Forum Moderators: open
But it doesn't update my records if run from the PHP script. I can't seem to figure out where the bug is:
if ($status == "update")
{$sql="
UPDATE top10 SET
djid = '$djid', rank = '$rank1', artist = '$h_artist1', song = '$h_song1', mix = '$h_mix1', label = '$h_label1', comment = '$h_comment1'
WHERE id = '$item_id1';
UPDATE top10 SET
djid = '$djid', rank = '$rank2', artist = '$h_artist2', song = '$h_song2', mix = '$h_mix2', label = '$h_label2', comment = '$h_comment2'
WHERE id = '$item_id2';
UPDATE top10 SET
djid = '$djid', rank = '$rank3', artist = '$h_artist3', song = '$h_song3', mix = '$h_mix3', label = '$h_label3', comment = '$h_comment3'
WHERE id = '$item_id3';
UPDATE top10 SET
djid = '$djid', rank = '$rank4', artist = '$h_artist4', song = '$h_song4', mix = '$h_mix4', label = '$h_label4', comment = '$h_comment4'
WHERE id = '$item_id4';
UPDATE top10 SET
djid = '$djid', rank = '$rank5', artist = '$h_artist5', song = '$h_song5', mix = '$h_mix5', label = '$h_label5', comment = '$h_comment5'
WHERE id = '$item_id5';
UPDATE top10 SET
djid = '$djid', rank = '$rank6', artist = '$h_artist6', song = '$h_song6', mix = '$h_mix6', label = '$h_label6', comment = '$h_comment6'
WHERE id = '$item_id6';
UPDATE top10 SET
djid = '$djid', rank = '$rank7', artist = '$h_artist7', song = '$h_song7', mix = '$h_mix7', label = '$h_label7', comment = '$h_comment7'
WHERE id = '$item_id7';
UPDATE top10 SET
djid = '$djid', rank = '$rank8', artist = '$h_artist8', song = '$h_song8', mix = '$h_mix8', label = '$h_label8', comment = '$h_comment8'
WHERE id = '$item_id8';
UPDATE top10 SET
djid = '$djid', rank = '$rank9', artist = '$h_artist9', song = '$h_song9', mix = '$h_mix9', label = '$h_label9', comment = '$h_comment9'
WHERE id = '$item_id9';
UPDATE top10 SET
djid = '$djid', rank = '$rank10', artist = '$h_artist10', song = '$h_song10', mix = '$h_mix10', label = '$h_label10', comment = '$h_comment10'
WHERE id = '$item_id10'
";
}
else
{
$sql=" INSERT INTO top10
(djid, rank, artist, song, mix, label, comment)
VALUES
('$djid', '$rank1', '$h_artist1', '$h_song1', '$h_mix1', '$h_label1', '$h_comment1'),
('$djid', '$rank2', '$h_artist2', '$h_song2', '$h_mix2', '$h_label2', '$h_comment2'),
('$djid', '$rank3', '$h_artist3', '$h_song3', '$h_mix3', '$h_label3', '$h_comment3'),
('$djid', '$rank4', '$h_artist4', '$h_song4', '$h_mix4', '$h_label4', '$h_comment4'),
('$djid', '$rank5', '$h_artist5', '$h_song5', '$h_mix5', '$h_label5', '$h_comment5'),
('$djid', '$rank6', '$h_artist6', '$h_song6', '$h_mix6', '$h_label6', '$h_comment6'),
('$djid', '$rank7', '$h_artist7', '$h_song7', '$h_mix7', '$h_label7', '$h_comment7'),
('$djid', '$rank8', '$h_artist8', '$h_song8', '$h_mix8', '$h_label8', '$h_comment8'),
('$djid', '$rank9', '$h_artist9', '$h_song9', '$h_mix9', '$h_label9', '$h_comment9'),
('$djid', '$rank10', '$h_artist10', '$h_song10', '$h_mix10', '$h_label10', '$h_comment10')
";
}
$result = mysql_query($sql); echo mysql_errno() . ": " . mysql_error() . "\n";
From what I've read, it's not possible to runseveral UPDATE queries like this.
What would be the workaround?
From what I've read, it's not possible to runseveral UPDATE queries like this.
I assume the table only contains 10 records, yes? So why bother with the update?
In the case of an update, just delete everything, then run the insert:
if ($status == "update")
{
$sql='DELETE FROM top10'
$result = mysql_query($sql); echo mysql_errno() . ": " . mysql_error() . "\n";
}
<Insert Code>
If you don't like this approach, then you'll have to run each query individually - you should try to use array variables in your code though - you could then write a simple loop and construct the SQL each iteration from the array values.
Edit:
I see that table proabably contains more than 10 records, but the delete first solution would still work - just make sure you delete the correct susbset of data - eg:
if ($status == "update")
{
$sql='DELETE FROM top10 WHERE djid=$djid etc'
$result = mysql_query($sql); echo mysql_errno() . ": " . mysql_error() . "\n";
}
[edited by: FalseDawn at 5:04 pm (utc) on May 26, 2007]
I implemented your recommanded code like this but the delete function doesn't work:
if ($status == "update")
{
$sql1="DELETE FROM top10 WHERE djid ='$djid'";
}
$sql=" INSERT INTO top10
(djid, rank, artist, song, mix, label, comment)
VALUES
('$djid', '$rank1', '$h_artist1', '$h_song1', '$h_mix1', '$h_label1', '$h_comment1'),
('$djid', '$rank2', '$h_artist2', '$h_song2', '$h_mix2', '$h_label2', '$h_comment2'),
('$djid', '$rank3', '$h_artist3', '$h_song3', '$h_mix3', '$h_label3', '$h_comment3'),
('$djid', '$rank4', '$h_artist4', '$h_song4', '$h_mix4', '$h_label4', '$h_comment4'),
('$djid', '$rank5', '$h_artist5', '$h_song5', '$h_mix5', '$h_label5', '$h_comment5'),
('$djid', '$rank6', '$h_artist6', '$h_song6', '$h_mix6', '$h_label6', '$h_comment6'),
('$djid', '$rank7', '$h_artist7', '$h_song7', '$h_mix7', '$h_label7', '$h_comment7'),
('$djid', '$rank8', '$h_artist8', '$h_song8', '$h_mix8', '$h_label8', '$h_comment8'),
('$djid', '$rank9', '$h_artist9', '$h_song9', '$h_mix9', '$h_label9', '$h_comment9'),
('$djid', '$rank10', '$h_artist10', '$h_song10', '$h_mix10', '$h_label10', '$h_comment10')
";
$result = mysql_query($sql); echo mysql_errno() . ": " . mysql_error() . "\n";
Check the documentaion for query delimiters but it would be something like this.
$sql="
UPDATE top10 SET
djid = '$djid', rank = '$rank1', artist = '$h_artist1', song = '$h_song1', mix = '$h_mix1', label = '$h_label1', comment = '$h_comment1'
WHERE id = '$item_id1';
sql_delimiter
UPDATE top10 SET
djid = '$djid', rank = '$rank2', artist = '$h_artist2', song = '$h_song2', mix = '$h_mix2', label = '$h_label2', comment = '$h_comment2'
WHERE id = '$item_id2';
sql_delimiter
UPDATE top10 SET
djid = '$djid', rank = '$rank3', artist = '$h_artist3', song = '$h_song3', mix = '$h_mix3', label = '$h_label3', comment = '$h_comment3'
WHERE id = '$item_id3';
And so on............
It is worth a try... you may not want them all done in one statement.
