Php insert/update multple row, using array and not a foreach

mysql, php

Solution

if (count($a) <= 0)
   return; // nothing to do

$sql = "INSERT INTO table (id, firstname, lastname) VALUES";
while (count($a) > 0)
{
   $id = array_shift($a);
   $fname = array_shift($b);
   $lname = array_shift($c);

   $sql .= sprintf("('%s', '%s', '%s'),", mysql_real_escape_string($id), mysql_real_escape_string($fname), mysql_real_escape_string($lname));
}
$sql = substr($sql, 0, -1); //remove last comma

$sql .= " ON DUPLICATE KEY UPDATE firstname=VALUES(fname), lastname=VALUES(lname)";

//run your sql

this will allow you to run all of them at once.

Problem

I’m wondering if this is possible, I’ve search and haven’t found anything so about to give up. I’m looking to do the following for example, note i do not want to use a foreach as that converts it into single queries. ``` $a = (1,2,3); $b = ('john','Rob','Stuffs'); $c = ('doe','roe','soe'); $sql = "update ppl set firstname = $b, lastname = $c where id = $a"; ``` The same can be said for an insert. ``` $sql = "insert into ppl (firstname,lastname) value ($b,$c)"; ``` The main reason I'm looking to do this is to optimise the db a bit. There are a lot of single queries that (if this method is possible) could be converted into 1 single query. Thanks in advance.

Original source

Related problems