Hello and welcome to our community! Is this your first visit?
Register
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
    Posts
    80
    Thanks
    0
    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:

    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.

  • #2
    Super Moderator Inigoesdr's Avatar
    Join Date
    Mar 2007
    Location
    Florida, USA
    Posts
    3,638
    Thanks
    2
    Thanked 404 Times in 396 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();
    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.


  •  

    Posting Permissions

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