Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Try Vanilla Forums Cloud product

Best way to insert multiple records into table

R_JR_J Cheerleader & TroubleshooterMunich Moderator

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?

Comments

  • hgtonighthgtonight ∞ · New Moderator

    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.

  • R_JR_J Cheerleader & Troubleshooter Munich Moderator

    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?

  • R_JR_J Cheerleader & Troubleshooter Munich Moderator

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

Sign In or Register to comment.