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

Question

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.

Please see this link http://stackoverflow.com/questions/11664684/how-to-bulk-update-mysql-data-with-one-query , it should help u — Siraj Khan, Dec 23 '13 at 07:23
@Manish Patel No just want to store those values inside the array in the table. — Rob, Dec 23 '13 at 07:26

score 4 · Accepted Answer · answered Dec 23 '13 at 07:24

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.

Cheers thanks, just to add to the answer, the update can be done with the comment made by Siraj Khan under the initial question. He refers us to http://stackoverflow.com/questions/11664684/how-to-bulk-update-mysql-data-with-one-query — Rob, Dec 23 '13 at 07:30

Deepika Janiyani · Answer 2 · 2013-12-23T07:27:37.990

0

For update you can do as follows

    $a = (1,2,3);
    $b = ('john','Rob','Stuffs');
    $c = ('doe','roe','soe');

    $i=0;
    foreach($b as $fname){
        if( !empty($b[$i]))
        {
            $sql = "update ppl set firstname = '".$b[$i]."', lastname = '".$c[$i]."' where id = $a[$i]";
        }
    $i++;
    }

and for insert you can try

    $i=0;
    $var = '';
    foreach($b as $fname){
        if( !empty($b[$i]))
        {
            $var .= "(".$a[$i].",'".$c[$i]."','".$b[$i]."') ";
        }
        $i++;
    }
    if(!empty($var)){
        $sql = "insert into ppl(id,firstname,lastname) values ".$var;
    }

edited Dec 23 '13 at 07:27

answered Dec 23 '13 at 07:21

Deepika Janiyani

1,487
9
17

Ok so there's no way to do an update without a foreach. So they will be single queries each time? – Rob Dec 23 '13 at 07:23
with update its not possible to do in a single query – Deepika Janiyani Dec 23 '13 at 07:25

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

2 Answers2