HackerOne users: Testing against this community violates our program's Terms of Service and will result in your bounty being denied.
Options

Best way to insert multiple records into table

I want to insert multiple rows into one table at one. Instead of doing a foreach loop and calling sql->insert a hundred times, I would prefer to insert values like that:
sql->insert(table,
array(
array(a=>1, b=>2, c=>3),
array(a=>1, b=>2, c=>3),
array(a=>1, b=>2, c=>3),
array(a=>1, b=>2, c=>3),
array(a=>1, b=>2, c=>3),
...

From looking at the sqldrivers insert function, I'd say that I cannot do it like the example above.

And since my values are calculated, I cannot fill them in with a the $select parameter of the function.

Now I'll try to do some array_map magic and use a manual insert sql, but isn't there any better/more framework using solution?

Answers

  • Options
    hgtonighthgtonight ∞ · New MVP

    If you are inserting hundreds of records, do it via SQL, imo.

    I have used a for loop before, though.

    I am also interested in this.

    Search first

    Check out the Documentation! We are always looking for new content and pull requests.

    Click on insightful, awesome, and funny reactions to thank community volunteers for their valuable posts.

  • Options
    R_JR_J Ex-Fanboy Munich Admin

    I was quite surprised to find out that it could be done with an array of array:

    $sql = Gdn::sql();
    $sql->options('ignore', true);
        decho(
            $sql->getinsert(
                'Table',
                array(
                    array(
                        'Word' => 'a',
                        'letter' => 'one'
                    ),
                    array(
                        'Word' => 'b',
                        'letter' => 'two'
                    )
                )
            )
        );
    }
    

    That gives:

    DEBUG: insert Table
    (`Word`, `letter`) 
    values 
    ('a', 'one'), 
    ('b', 'two')
    

    Now I only have to find out why that didn't end up in an insert ignore statement! Anyone has an idea?

  • Options
    R_JR_J Ex-Fanboy Munich Admin

    OMG! It's case sensitive! $sql->options('Ignore', true); works as expected =)

Sign In or Register to comment.