Updating multiple rows in MySQL

Question

I'm trying to update multiple rows in one table in MySQL database by doing this. And its not working.

$query = "UPDATE cart SET cart_qty='300' WHERE cart_id = '21';
          UPDATE cart SET cart_qty='200' WHERE cart_id = '23';
          UPDATE cart SET cart_qty='100' WHERE cart_id = '24';";
mysql_query($query,$link);// $link is specified above

Anyone know what is wrong with this.

Answer 1

From the PHP documentation:

mysql_query() sends a unique query (multiple queries are not supported)

The ; separates SQL statements, so you need to separate the queries if you want to continue using the mysql_query function...

Answer 2

mysql_query can't use multiple queries.

The easiest thing is to just run them separately. I believe you can do multi query but I haven't tried it.

$updateArray = array(21=>300,23=>200,24=>100);
foreach($updateArray as $id=>$value)
{
    $query = "UPDATE cart SET cart_qty='$value' WHERE cart_id = '$id'";
    mysql_query($query,$link);// $link is specified above
}

This will accept a combination of IDs and their corresponding cart value. Looping though, it builds the query and executes it. The array can then come from a variety of sources (results from another query, form inputs or, as in this case, hard-coded values)

Update:

If you really need to execute all in one, heres the PHP info on multi query:

mysqli::multi_query

Answer 3

You'll need to send them as separate queries. Why not add the queries as strings to an array, then iterate through that array sending each query separtely?

Also check this thread for another idea

Answer 4

This isn't the best method.. But if you need to do multiple queries you could use something like...

function multiQuery($sql) 
{
  $query_arr  =  explode(';', $sql);

  foreach ($query_arr as $query)
  {
    mysql_query($query);
  }
}

another example of a helper query

function build_sql_update($table, $data, $where)
{

  $sql  =  '';

  foreach($data as $field => $item)
  {
    $sql .=  "`$table`.`$field` = '".mysql_real_escape_string($item)."',";
  }

  // remove trailing ,
  $sql  =  rtrim($sql, ',');

  return 'UPDATE `' . $table .'` SET '.$sql . ' WHERE ' .$where;
}

echo build_sql_update('cart', array('cart_qty' => 1), 'cart_id=21');