Creating a single insert statement for multiple array values

Question

I have a query about how to implement my use case.I have a set of arrays that I want to insert in the DB,but now I am confused how can I do it efficiently.I am using PHP and MySQL.Below is the use case:

$shareUrl = array();
$theInsertUrl = array();
$authKeyArray = array();

Above are the arrays I get after processing some information.Now I have to insert it into the DB,but instead of doing it one by one,I thought have a single insert would be better solution(Please correct if not).
For multiple insert the values part of my SQL query must have

 ($shareUrl[0],$theInsertUrl[0],$authKeyArray[0]),($shareUrl[1],$theInsertUrl[1],$authKeyArray[1]),...

I thought of writing a for loop and create a multidimensional array like this

  for($i=0;$i<count_of_array;$i++){
        $multiArray[$i]['shareUrl'] = $shareUrl[0];
        $multiArray[$i]['theInsertUrl'] = $theInsertUrl[0];
        $multiArray[$i]['authKeyArray'] = $authKeyArray[0];
  }

But still it will be tedious to use this in the SQL query's values part as it accepts a format like this ('val1','val2'),('val1','val2'). I need suggestions on how can I go about implementing it? Is the above approach correct or is there a better solution OR should I go it with single insert statements?

One advice is: Don't try to optimize too early. Anyway the single insert of multiple rows indeed is faster - when you have a good quantity of rows. If you have just tens of rows to insert daily, you don't really NEED to optimize that, if we are talking about thousands or a few thousands you will notice the optimization. Basicly you save the request time between each query and the primary key generation time. To help you some more info would be needed, are you using pure mysql_ functions, mysqli or PDO? Some numbers would also help, like how big the array is. — aleation
– aleation, Commented Jan 17, 2014 at 8:59
I am using mysqli functions! Actually I am doing it for a file sharing app.So there can be many inserts simultaneously!Initially though the number won't be significant,it will grow quickly as I progress! — KillABug
– KillABug, Commented Jan 17, 2014 at 9:12
Sorry i'm not very handy with mysqli, jumped from mysql to PDO, I'm not sure if mysqli has named parameters binding, as far as I know you can use question marks ? in order, but not :value1 like in PDO. So i'm not sure on the implementation, better let someone more experienced in mysqli to answer you. BTW you should use the prepared statements in mysqli, or you are not really being injection safe — aleation
– aleation, Commented Jan 17, 2014 at 9:26

Pedro Bernardo · Accepted Answer · 2014-01-17 12:53:26Z

1

just put everything as a string in the array and then implode it

$data= array();
for ($i = 0; $i < $size; $i++) {
    $data[] = "('".$mysqli->real_escape_string($array1[$i])."','".$mysqli->real_escape_string($array2[$i])."')";
}
if (sizeof($data) > 0) {
    $query = "INSERT INTO table (value1,value2) VALUES ".implode(",",$data).";");
    $mysqli->real_query($query)
}

edited Jan 17, 2014 at 12:53

answered Jan 17, 2014 at 9:05

Pedro Bernardo

9732 gold badges8 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

aleation Over a year ago

You shouldn't actually advise people with mysql_query() functions, as they are deprecated, are not sql injection safe, and specially if you don't even escape the data...

KillABug Over a year ago

I am using mysqli!! :)

Pedro Bernardo Over a year ago

then try $mysqli->real_query($query);, just as @aleation said, escape the values in the array if they are not numbers.

KillABug Over a year ago

@PedroBernardo But this still fails as each value has to be in quotes! ('val1','val2'),('val3','val4').This gives a string like (val1,val2),(val1,val2).

Pedro Bernardo Over a year ago

then you just need to escape it and put the quotes. I've edited the answer

Collectives™ on Stack Overflow

Creating a single insert statement for multiple array values

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest