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 stackoverflow.com/questions/11664684/… , it should help u — Siraj Khan
– Siraj Khan, Commented Dec 23, 2013 at 7:23
@Manish Patel No just want to store those values inside the array in the table. — Rob
– Rob, Commented Dec 23, 2013 at 7:26

chadkouse · Accepted Answer · 2013-12-23 07:24:07Z

4

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.

answered Dec 23, 2013 at 7:24

chadkouse

1463 bronze badges

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

1 Comment

Rob Over a year ago

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 stackoverflow.com/questions/11664684/…

Deepika Janiyani · Accepted Answer · 2013-12-23 07:27:37Z

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, 2013 at 7:27

answered Dec 23, 2013 at 7:21

Deepika Janiyani

1,4679 silver badges17 bronze badges

2 Comments

Rob Over a year ago

Ok so there's no way to do an update without a foreach. So they will be single queries each time?

Deepika Janiyani Over a year ago

with update its not possible to do in a single query

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related