I would like to update multiple records in a MySQL table using a single query. Basically, this is a tasks table, which has assignments for different people on different dates. When these assignments are changed and submitted via the Online form there is a lot of POST data that gets submitted (pretty much all the pending assignments). I've written an algorithm that sorts through all this information and gets what I want out of it, but I'm stuck on writing the query to update the MySQL table:
// Find the modified records and save their information
$update = 0;
for ( $n = 0; $n < $total_records; $n++ )
{
if ( $_POST['update'.$n] == true )
{
$updates_arr[$update] = array( intval($_POST['user_id'.$n]), intval($_POST['task'.$n]), $_POST['date'.$n] );
$update++;
}
}
if ( $mysql_db = OpenDatabase() )
{
$query = "UPDATE tasks_tbl";
if ( $updates_arr[0] )
{
$query .= " SET task = ".$updates_arr[0][1]." WHERE user_id = ".$updates_arr[0][0]." AND date = ".$updates_arr[0][2];
}
for ( $n = 1; $n < $updates; $n++ )
{
$query .= ", SET task = ".$updates_arr[$n][1]." WHERE user_id = ".$updates_arr[$n][0]." AND date = ".$updates_arr[$n][2];
}
$result = mysql_query( $query, $mysql_db );
if ( $result )
{
$page .= "<p>Success!</p>
";
}
else
{
$page .= "<p>Error: ".mysql_error()."</p>
";
}
}
This is the query that is generated:
UPDATE tasks_tbl
SET task = 1
WHERE user_id = 16
AND date = 2010-05-05,
SET task = 1
WHERE user_id = 17
AND date = 2222-02-22
Any suggestions would be appreciated. Thanks.