PHP MYSQL Update Multiple Rows

PHP / MYSQL Updating Multiple Rows

Update is one of the main features for CRUD (Create, Read, Update and Delete). Normally, updating a database is performed on single entries or rows; such as users changing a profile or updating an address. However, using the for loop, you can update multiple records at once.

For example, you could have a list of players on a football team and you need to adjust their weights for a particular reason. The script below shows how all entries of a table can be output with editable input fields for each player. Upon submit, any adjustments to any row happens almost instantly.

<?php
include ('myconnect.inc');
$db=db_connect();
if(!empty($_POST['submit'])) {### Here we get post variables for each person$id                = $_POST['id'];
$firstname         = $_POST['firstname'];
$lastname        = $_POST['lastname'];
$email             = $_POST['email'];
$height          = $_POST['height'];
$age              = $_POST['age'];
$weight             = $_POST['weight'];
$mydate         = date("Y-m-d");

$i=0;
$count = count($id);

#The loop below takes in account of all the rows in the table. Then an update is applied to each row; whether it is changed or not. If the row is unchanged, it updates based on the original values.
for ($i=0; $i < $count; $i++) {

$id_from_loop             = mysqli_real_escape_string($db, $id[$i]);
$lastname_from_loop     = mysqli_real_escape_string($db, $lastname[$i]);
$firstname_from_loop     = mysqli_real_escape_string($db, $firstname[$i]);
$email_from_loop        = mysqli_real_escape_string($db, $email[$i]);
$height_from_loop        = mysqli_real_escape_string($db, $height[$i]);
$age_from_loop             = mysqli_real_escape_string($db, $age[$i]);
$weight_from_loop         = mysqli_real_escape_string($db, $weight[$i]);

$command = "SELECT * FROM table_sort WHERE id='$id_from_loop' ";
$result = mysqli_query($db, $command);

if (mysqli_num_rows($result) > 0) {

$command = "UPDATE table_sort SET firstname='$firstname_from_loop', lastname='$lastname_from_loop', email='$email_from_loop', height='$height_from_loop', age='$age_from_loop', weight='$weight_from_loop' WHERE id='$id_from_loop' ";
$result = mysqli_query($db, $command) or die(mysqli_error($db));

} else {
echo "There are no records!";
}

}
?>
<div>Directory has been updated!</div>
<?php
}
?>
<form method="post" action="<?php echo $_SERVER['PHP_SELF']; ?>" name="directory">
<div><p><input type="submit" name="submit" value="Update" /></p></div>
<div style="clear:both;"></div>
<?php
?>
<div>
<table style="width:100%;">
<tr>
<th>id</th><th>Last Name</th><th>First Name</th><th>Email</th><th>Height</th><th>Age</th><th>Weight</th><th>Date</th>
</tr>
<?php
################ Here we create the list of all entries from the database table and the form names use [] to create arrays of post variables for each entry.

$command    = "SELECT * FROM table_sort ORDER BY id ASC ";
$result = mysqli_query($db, $command);
while ($row = mysqli_fetch_assoc($result)) {
//Remove a person if you want
//if($row['lastname'] == 'Zhang') { continue; }
$myid            = $row['id'];
$first            = $row['firstname'];
$last             = $row['lastname'];
$email_address  = $row['email'];
$height          = $row['height'];
$age              = $row['age'];
$weight          = $row['weight'];
$mydate          = $row['date'];

echo '<tr>';
echo '<input type="hidden" name="id[]" value="'.$myid.'" />';
echo '<td>'.$myid.'</td>';
echo '<td><input type="text" name="lastname[]" style="width: 95%; padding: 3px; margin: 3px;" value="'.$last.'"/></td>';
echo '<td><input type="text" name="firstname[]" style="width: 95%; padding: 3px; margin: 3px;" value="'.$first.'"/></td>';
echo '<td><input type="text" name="email[]" style="width: 95%; padding: 3px; margin: 3px;" value="'.$email_address.'"/></td>';
echo '<td><input type="text" name="height[]" style="width: 95%; padding: 3px; margin: 3px;" value="'.$height.'"/></td>';
echo '<td><input type="text" name="age[]" style="width: 95%; padding: 3px; margin: 3px;" value="'.$age.'"/></td>';
echo '<td><input type="text" name="weight[]" style="width: 95%; padding: 3px; margin: 3px;" value="'.$weight.'"/></td>';
echo '<td><input type="text" name="date[]" style="width: 95%; padding: 3px; margin: 3px;" value="'.$mydate.'"/></td>';
echo '</tr>';
}
?>
</table>
</div>
<p><input type="submit" name="submit" value="Update" /></p>
</form>