Update Multiple MYSQL Rows With Foreach Loop

PHP / MYSQL Update Multiple Rows With a Foreach Loop

This exercise will show how to output all rows of table data and do a mass update at the same time using a foreach loop.The following can be used to update multiple rows

using a foreach 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. However, what we want to do is match all keys from each array to make new arrays based on keys. For example, the first key in the id array is the id for the first element in the firstnames array. The array of ids is is run through a foreach loop. We take the key and id. Then, the $members_info array is created which uses each member’s id and its matching key to match the keys for each other array. Here is a simple explanation. Since the first member in the array will have a key for its id to be [0], we grab each other value from the other arrays where the key is [0]. The members_info array does this for each and every entry.

After all the members_info arrays are built, each member is separated into a string called $member_info. Then, we simply select this member’s id and update the table for each and every member. If new output is inserted, it gets updated. If no info changes, the data remains intact.

<?php 
include ('connect.inc');
$db=public_db_connect();   
if(!empty($_POST['submit'])) {

### Here we get post variables for each person

$ids			= $_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"); 

foreach ($ids as $key => $id){
//echo $key."-".$id."<br/>";
$members_info[] = $id.",".$firstname[$key].",".$lastname[$key].",".$email[$key].",".$height[$key].",".$age[$key].",".$weight[$key].",".$mydate;

if(!in_array($key, $firstname)){
continue;
}

}

//print_r($members_info);

#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. 

foreach ($members_info as $member_info) {
$all = explode(",",$member_info);

$id 			= $all[0];
$firstname    	= $all[1];
$lastname   	= $all[2];
$email  		= $all[3];
$height	     	= $all[4];
$age 			= $all[5];
$weight 		= $all[6];

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

if (mysqli_num_rows($result) > 0) {

$command = "UPDATE table_sort SET firstname='$firstname', lastname='".addslashes($lastname)."', email='$email', height='$height', age='$age', weight='$weight' WHERE id='$id' ";
$result = mysqli_query($db, $command);	

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

} 
print_r($all_post_variables_array);
?>
	<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 WHERE id >0 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 			= stripslashes($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