Updating multible rows with php

Question

The below PHP script should update multiple rows on a MySQL database, but the update values in the size input field is empty after submit.

First time in table all values are shown correctly, but after submission the size field is empty, it seems that the values are not transferred.

Does anyone has any idea?

<?php
$host="localhost"; // Host name
$username="dbu"; // Mysql username
$password="mypw"; // Mysql password
$db_name="mydb"; // Database name
$tbl_name="files"; // Table name

// Connect to server and select databse.
mysql_connect("$host", "$username", "$password")or die("cannot connect");
mysql_select_db("$db_name")or die("cannot select DB");

$sql="SELECT * FROM $tbl_name";
$result=mysql_query($sql);

// Count table rows
$count=mysql_num_rows($result);
?>

<table width="500" border="0" cellspacing="1" cellpadding="0">
<form name="form1" method="post" action="">
<tr>
<td>
<table width="500" border="0" cellspacing="1" cellpadding="0" class="table">

<tr>
<td align="left"><strong>Id</strong></td>
<td align="left"><strong>Name</strong></td>
<td align="left"><strong>Size</strong></td>
<td align="left"><strong>Type</strong></td>
</tr>

<?php
while($rows=mysql_fetch_array($result)){
?>

<tr>
<td align="center">
<?php $id[]=$rows['id']; ?><?php echo $rows['id']; ?>
</td>
<td align="center">
<input name="name[]" type="text" id="name" value="<?php echo $rows['name']; ?>">
</td>
<td align="center">
<input name="size[]" type="text" id="size" value="<?php echo $rows['size']; ?>">
</td>
<td align="center">
<input name="type[]" type="text" id="type" value="<?php echo $rows['type']; ?>">
</td>
</tr>

<?php
}
?>

<tr>
<td colspan="4" align="center"><input type="submit" name="Submit" value="Submit"></td>
</tr>
</table>
</td>
</tr>
</form>
</table>

<?php

// Check if button name "Submit" is active, do this
//if($Submit){
if(isset($_POST['Submit'])){
for($i=0;$i<$count;$i++){
$sql1="UPDATE $tbl_name SET size='$size[$i]' WHERE id='$id[$i]'";
$result1=mysql_query($sql1);
}
}

if($result1){
header("location:index_table.php");
}
mysql_close();
?>

Where are you setting $size that you use in ...SET size='$size[$i]'...? You need to use your posted values - ...SET size='$_POST['size'][$i]'... Side note - make sure to sanitize your data to prevent sql injection — Sean, Apr 22 '15 at 03:41

score 0 · Answer 1 · edited May 23 '17 at 11:51

Submit also the ID. You can hide it as this is not needed (usually) for the front-user to see.

<input name="id[]" type="hidden" id="name" value="<?php echo echo $rows['id']; ?>">

And then use count() function of PHP to determine the number of rows submitted
Use a for loop based on the number of rows submitted
Your checking if the query has run successfully is outside the isset(), I changed it and put it inside
Use mysql_real_escape_string to prevent some of SQL injections

Your PHP code:

if(isset($_POST['Submit'])){

  $count = count($_POST["id"]);

  for($i=0;$i<$count;$i++){

    $size = mysql_real_escape_string($_POST["size"][$i]);
    $id = mysql_real_escape_string($_POST["id"][$i]);

    $sql1="UPDATE $tbl_name SET size='$size' WHERE id='$id'";
    $result1=mysql_query($sql1);

  } /* END OF FOR LOOP */

  if($result1){
    header("location:index_table.php");
  }

  mysql_close();

} /* END OF ISSET */

Recommendation:

You should be using mysqli_* prepared statement instead of deprecated mysql_* to prevent SQL injection

score 0 · Answer 2 · answered Apr 22 '15 at 03:56

<table width="500" border="0" cellspacing="1" cellpadding="0">
    <form name="form1" method="post" action="">
        <tr>
            <td>
                <table width="500" border="0" cellspacing="1" cellpadding="0" class="table">
                    <tr>
                        <td align="left"><strong>Id</strong></td>
                        <td align="left"><strong>Name</strong></td>
                        <td align="left"><strong>Size</strong></td>
                        <td align="left"><strong>Type</strong></td>
                    </tr>
                    <?php
                    while($rows=mysql_fetch_array($result)) {
                    ?>
                        <tr>
                            <td align="center">
                                <?php $id[]=$rows['id']; ?><?php echo $rows['id']; ?>
                                <input name="id[]" type="hidden" id="name" value="<?php echo $rows['id']; ?>">
                            </td>
                            <td align="center">
                                <input name="name[]" type="text" id="name" value="<?php echo $rows['name']; ?>">
                            </td>
                            <td align="center">
                                <input name="size[]" type="text" id="size" value="<?php echo $rows['size']; ?>">
                            </td>
                            <td align="center">
                                <input name="type[]" type="text" id="type" value="<?php echo $rows['type']; ?>">
                            </td>
                        </tr>
                    <?php
                    }
                    ?>
                    <tr>
                        <td colspan="4" align="center"><input type="submit" name="Submit" value="Submit"></td>
                    </tr>
                </table>
            </td>
        </tr>
    </form>
</table>


<?php

// Check if button name "Submit" is active, do this
//if($Submit){
if(isset($_POST['Submit'])){

    for($i=0;$i<$count;$i++){

        $sql1="UPDATE $tbl_name SET size='{$_POST['size'][$i]}' WHERE id='{$_POST['id'][$i]}'";

        $result1 = mysql_query($sql1);
    }
}

if(isset($result1)) {
    header("location:index_table.php");
}

mysql_close();
?>

Updating multible rows with php

2 Answers2

Recommendation: