Update multiple row using PHP and MYSQL for my table

Question

I want to create a php code which can update all the rows at once when i click the button. Already try few method that i look from the other post but still can't figure it out. Please help me................

<table cellspacing="0" cellpadding="1" border=1px; action="" method="post">
<?php
        $result=mysqli_query($conn,"SELECT * from subject ");
        if($result->num_rows>0)
        {
            ?>
       <tr>
          <th>Subject Code</th>
          <th>Subject</th>
          <th>Fees</th>
          <th>Status</th>


        </tr>
        <?php
        while($row = mysqli_fetch_assoc($result))
        {
         if ($row["subject_status"]==="Available")
         {$status="Unavailable";}
          else $status="Available";
        ?>

         <tr class="alttr1">
          <td><?php echo $row["subject_code"]; ?></td>
          <td><?php echo $row["subject_name"]; ?></td>
          <td>RM<input type="number" name="price[]" value="<?php echo $row["subject_price"]; ?>" ></td>
          <td><select name="status[]" ><option value="<?php echo $row["subject_status"]; ?>" ><?php echo $row["subject_status"]; ?></option><option value="<?php echo $status; ?>" ><?php echo $status; ?></option></select></td>


        </tr>
        <?php 
        }
        ?>

      </table>
 <input type="submit" name="updatebtn" value="Save Changes">
<?php
if (isset($_POST["updatebtn"]))
{
    $price = $_POST["price"];
    $status = $_POST["status"];

    foreach($_POST["price"] as $price)
    {
    mysqli_query($conn,"UPDATE subject SET subject_price='$price' ");
    }

    foreach($_POST["status"] as $status)
    {
    mysqli_query($conn,"UPDATE subject SET subject_status='$status' ");
    }
    header("location: subjectmanage.php");

}

[Little Bobby](http://bobby-tables.com/) says ***[your script is at risk for SQL Injection Attacks.](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php)*** Learn about [prepared](http://en.wikipedia.org/wiki/Prepared_statement) statements for [MySQLi](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php). Even [escaping the string](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) is not safe! [Don't believe it?](http://stackoverflow.com/q/38297105/1011527) — Jay Blanchard, Jan 11 '17 at 17:11
Add error reporting to the top of your file(s) right after your opening ` — Jay Blanchard, Jan 11 '17 at 17:11
You're updating all the rows to the same value, is that really what you want? — Barmar, Jan 11 '17 at 17:13
If you want to update specific rows, you need a `WHERE` clause to tell it which row to update. — Barmar, Jan 11 '17 at 17:13
the problem is i loop out all the row to the table and i don't know how to indicate the subject_id for each of the row @Barmar — Kar Wai, Jan 11 '17 at 17:20
@KarWai When you're displaying the result set initially make a hidden field that contains the subject_id then post that to your processing page. — jon.r, Jan 11 '17 at 17:27

score 0 · Accepted Answer · answered Jan 11 '17 at 18:44

Add a hidden field containing the subject code to the form.

     <tr class="alttr1">
      <td><?php echo $row["subject_code"]; ?><input type="hidden" name="code[]" value="<?php echo $row["subject_code"]; ?>"></td>
      <td><?php echo $row["subject_name"]; ?></td>
      <td>RM<input type="number" name="price[]" value="<?php echo $row["subject_price"]; ?>" ></td>
      <td><select name="status[]" ><option value="<?php echo $row["subject_status"]; ?>" ><?php echo $row["subject_status"]; ?></option><option value="<?php echo $status; ?>" ><?php echo $status; ?></option></select></td>
    </tr>

Then the update code can look like this:

<?php
if (isset($_POST["updatebtn"]))
{
    $codes = $_POST["code"];
    $prices = $_POST["price"];
    $statuses = $_POST["status"];

    foreach($codes as $i => $code)
    {
        $code = mysql_real_escape_string($code);
        $price = mysql_real_escape_string($prices[$i]);
        $status = mysql_real_escape_string($statuses[$i]);
        mysqli_query($conn,"UPDATE subject SET subject_price='$price', status = '$status' WHERE subject_code = '$code'");
    }
}

Update multiple row using PHP and MYSQL for my table

1 Answers1