Multiple Rows Updates With PHP

Update Multiple Rows With PHP

The purpose of this tutorial is to output rows of data into input fields. Then, you can update any number of rows at the same time.

The following can be used to update multiple rows using a for loop. Here is how it works. All form inputs make arrays for each value. Thus, ids, firstnames, emails, etc become separate arrays when the submit button is clicked. To organize each array and singular member data, the id array is counted. Then, the incrementing i++ value is added to each member to make them distinct. Since each member will have all separate values from the for loop, mass updates can take place for each and every member.

<?php 
include ('connect.inc');
$db=public_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"); 
 
// count the array of ids
$count = count($id);

// shows array of ids when submitted
print_r($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. 

$i=0;

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

// append the value of $i to each member
$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);	

} 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>

October 20, 2012 by kent