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.

[DrDoc]

You basically have two options:

1) ten separate UPDATE statements (preferred)
2) a single UPDATE statement, but with CASE or IF soup to determine which values need to go on which row

[ro1960]

Thanks for your response DrDoc. I tried before to do 10 separate updates but it didn't work. Here's the code I used:

$sql=" 
UPDATE top10 SET djid='$djid', rank='$h_rank1', artist='$h_artist1', song='$h_song1', mix='$h_mix1', label='$h_label1', comment='$h_comment1' WHERE id = '$id1';
UPDATE top10 SET djid='$djid', rank='$h_rank2', artist='$h_artist2', song='$h_song2', mix='$h_mix2', label='$h_label2', comment='$h_comment2' WHERE id = '$id2';
UPDATE top10 SET djid='$djid', rank='$h_rank3', artist='$h_artist3', song='$h_song3', mix='$h_mix3', label='$h_label3', comment='$h_comment3' WHERE id = '$id3';
UPDATE top10 SET djid='$djid', rank='$h_rank4', artist='$h_artist4', song='$h_song4', mix='$h_mix4', label='$h_label4', comment='$h_comment4' WHERE id = '$id4';
UPDATE top10 SET djid='$djid', rank='$h_rank5', artist='$h_artist5', song='$h_song5', mix='$h_mix5', label='$h_label5', comment='$h_comment5' WHERE id = '$id5';
UPDATE top10 SET djid='$djid', rank='$h_rank6', artist='$h_artist6', song='$h_song6', mix='$h_mix6', label='$h_label6', comment='$h_comment6' WHERE id = '$id6';
UPDATE top10 SET djid='$djid', rank='$h_rank7', artist='$h_artist7', song='$h_song7', mix='$h_mix7', label='$h_label7', comment='$h_comment7' WHERE id = '$id7';
UPDATE top10 SET djid='$djid', rank='$h_rank8', artist='$h_artist8', song='$h_song8', mix='$h_mix8', label='$h_label8', comment='$h_comment8' WHERE id = '$id8';
UPDATE top10 SET djid='$djid', rank='$h_rank9', artist='$h_artist9', song='$h_song9', mix='$h_mix9', label='$h_label9', comment='$h_comment9' WHERE id = '$id9';
UPDATE top10 SET djid='$djid', rank='$h_rank10', artist='$h_artist10', song='$h_song10', mix='$h_mix10', label='$h_label10', comment='$h_comment10' WHERE id = '$id10';
";
$result = mysql_query($sql); echo mysql_errno() . ": " . mysql_error() . "\n";

So I was thinking of some kind of loop but I'm really not familiar with that.

As for your second suggestion I am totally clueless. Do you care to elaborate?

Thank you.

[Habtom]

You need to execute each separately

$sql="UPDATE top10 SET . . .
$result = mysql_query($sql);

$sql2="UPDATE top10 SET . . .
$result2 = mysql_query($sql2);

[ro1960]

Thanks Habton. This is incredibly simple. I am surprised I ddin't see this suggested anywhere else.

[DrDoc]

MySQL 5 and later lets you execute multiple statements in one query. But in 4.x and earlier, yes, like Habtom suggested, they must be executed separately.

[ro1960]

But I'm running on version 5.0.22. What would be the code like to run multiple statements in a single query?

[DrDoc]

My bad for confusing you ...
PHP itself doesn't support multiple queries through

mysql_query()

[ro1960]

Ok, that happens, no worries. Thanks again for the help.

[coopster]

If you want to run multiple queries you can use the MySQL Improved Extension [php.net] function, mysqli_multi_query [php.net].