Updating multiple rows in MySQL

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.

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...

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

You can do it this way:

UPDATE table
  SET col1 = CASE id
    WHEN id1 THEN id1_v1,
    WHEN id2 THEN id2_v1
  END
  col2 = CASE id
    WHEN id1 THEN id1_v2,
    WHEN id2 THEN id2_v2
  END
  WHERE id IN (id1, id2)

This example shows updating two different columns in two different rows so you can expand this to more rows and columns by cludging together a query like this. There might be some scaling issues that makes the case statement unsuitable for a very large number of rows.

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

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');