Hello and welcome to our community! Is this your first visit?
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 2 of 2
  1. #1
    New Coder
    Join Date
    Dec 2005
    Thanked 0 Times in 0 Posts

    Updating multiple rows at once

    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.

  2. #2
    Senior Coder Inigoesdr's Avatar
    Join Date
    Mar 2007
    Florida, USA
    Thanked 406 Times in 398 Posts
    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();
    $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.


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts