View Full Version : Updating multiple rows at once

03-16-2008, 01:23 AM
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))
<form action="pl.php?p=top10_updated.php" method="post" name="top10_submit">
<input name="djid" type="hidden" value="<?php echo $djid ?>">
$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">
<tr style="text-decoration: underline"><td>Rank</td><td>Artist</td><td>Song</td><td>Mix</td><td>Label</td><tr>
while($row_top10 = mysql_fetch_array($result_top10))
<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>

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

Thanks for any help.

03-16-2008, 01:44 AM
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:

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

mysql_query('UPDATE `table` SET `field` = \'value\' WHERE `id` IN(' . implode(',', $top10_ids) . ')');
Add error checking, etc.