Updating multiple rows at once

[ro1960]

I need to update several rows from the same table at once but get stuck on the code to perform this duty. I have tried many examples found here and in other forums to no avail.

Here's the deal, I have a top10 table. Each row is a rank/artist/song combo associated to a particular DJ. Here's the table structure:

id tinyint(11) primary
djid smallint(11)
rank tinyint(11)
artist varchar(128)
song varchar(128)
mix varchar(128)
label varchar(50)
comment text
date_modified timestamp


Here's the edit form code:

$query_dj = "
SELECT name FROM djs WHERE id = '$djid'";
$result_dj = mysql_query($query_dj)
or die ("Couldn't execute query.");		

while($row_dj = mysql_fetch_array($result_dj))
{
extract($row_dj);
?>
<form action="pl.php?p=top10_updated.php" method="post" name="top10_submit">
<input name="djid" type="hidden" value="<?php echo $djid ?>">
<?php
}
?>
<?php
$query_top10 = "
SELECT * FROM top10 WHERE djid = '$djid' ORDER BY date_modified DESC, rank LIMIT 10";
$result_top10 = mysql_query($query_top10)
or die ("Couldn't execute query.");		
$num_rows = mysql_num_rows($result_top10);
// echo $num_rows;
?>

<div class="textbox_white" style="margin-bottom:10px">
<table>
<tr style="text-decoration: underline"><td>Rank</td><td>Artist</td><td>Song</td><td>Mix</td><td>Label</td><tr>
<?php
while($row_top10 = mysql_fetch_array($result_top10))
{
extract($row_top10);
?>
<tr>
<td><input name="id<?php echo $rank; ?>" type="hidden" value="<?php echo $id; ?>"><input name="rank<?php echo $rank; ?>" type="text" value="<?php echo $rank; ?>" size="2"></td>
<td><input name="artist<?php echo $rank; ?>" value="<?php echo $artist; ?>" type="text" size="20" maxlength="128"></td>
<td><input name="song<?php echo $rank; ?>" value="<?php echo $song; ?>" type="text" size="20" maxlength="128"></td>
<td><input name="mix<?php echo $rank; ?>" value="<?php echo $mix; ?>" type="text" size="20" maxlength="128"></td>
<td><input name="label<?php echo $rank; ?>" value="<?php echo $label; ?>" type="text" size="20" maxlength="128"></td>
</tr>
<?php
}
?>

So now how do I write an UPDATE query to update all these 10 rows at once?

Thanks for any help.

[Inigoesdr]

At what point do you need to update it? If it's after the loop you can add the row id's to an array, and use the IN() construct in MySQL:

PHP Code:

$top10_ids = array();
while($row_top10 = mysql_fetch_array($result_top10))
{
    $top10_ids[] = $row_top10['id'];
    // rest of your loop
}

if(!empty($top10_ids))
{
    mysql_query('UPDATE `table` SET `field` = \'value\' WHERE `id` IN(' . implode(',', $top10_ids) . ')');
} 


Add error checking, etc.