Need help with updating multiple rows in MySQL using PHP

I have a while statement that is pulling 4 records from my MySQL database and I have an update button on each line which posts to a case name. The issue is when I press the button on each line its updating the last record. What do i need to change to update each record. Code is below.
if (isset($_POST['form'])){
switch ($_POST['form']) {

case 'Update':

$asset = $_POST['asset'];
$name = $_POST['name'];
$department = $_POST['department'];
$dateout = $_POST['dateout'];
$timeout = $_POST['timeout'];
$edatein = $_POST['edatein'];
$etimein = $_POST['etimein'];
$info = $_POST['info'];

	$con = mysql_connect("localhost","root","pass");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }
 
mysql_select_db('ats');
echo "UPDATE checkout SET 
name='".$name."',
department='".$department."',
dateout='".$dateout."',
timeout='".$timeout."',
edatein='".$edatein."',
etimein='".$etimein."',
info='".$info."'
WHERE asset = '".$asset."'";

mysql_query("UPDATE checkout SET 
name='".$name."',
department='".$department."',
dateout='".$dateout."',
timeout='".$timeout."',
edatein='".$edatein."',
etimein='".$etimein."',
info='".$info."'
WHERE asset = '".$asset."'") or die (mysql_error());


break;


}
}





<fieldset id="request_1">
<?php

	$con = mysql_connect("localhost","root","pass");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }
 
mysql_select_db('ats');

$query = mysql_query("SELECT * FROM atsassets WHERE asignedto = 'checkout' order by model") or die (mysql_error());
$numofrows = mysql_num_rows($query);



?>

			<legend>Checkout Laptops Available</legend>
			<table width="450" class="innertable1">

<?php
echo "<tr>";
echo "<td colspan='2'>";
echo $numofrows, " <b>Assets available</b>";
echo "</td>";
echo "</tr>";
echo "<tr>";
echo "<td>";
echo "<b>Asset Tag:</b>";
echo "</td>";
echo "<td>";
echo "<b>Serial Tag:</b>";
echo "</td>";
echo "<td>";
echo "<b>Model:</b>";
echo "</td>";
echo "<td>";
echo "<b>Operating System:</b>";
echo "</td>";
echo "</tr>";

while ($info = mysql_fetch_assoc($query))
{

$asset = $info['asset'];
$serial = $info['serial'];
$model = $info['model'];
$os = $info['os'];
$id = $info['id'];


echo "<tr>";
echo "<td>";
echo "<a href=easset.php?id=$id>$asset</a>";
echo "</td>";
echo "<td>";
echo "<a href=easset.php?id=$id>$serial</a>";
echo "</td>";
echo "<td>";
echo "<a href=easset.php?id=$id>$model</a>";
echo "</td>";
echo "<td>";
echo "<a href=easset.php?id=$id>$os</a>";
echo "</td>";
echo "<td>";
echo "</td>";
echo "</tr>";

}
?>


			</table>
            </fieldset>

            <br />
			<fieldset id="request_1">
<?php

	$con = mysql_connect("localhost","root","pass");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }
 
mysql_select_db('ats');

$query = mysql_query("SELECT * FROM checkout") or die (mysql_error());
$numofrows = mysql_num_rows($query);
?>
<legend>Checked Out Laptops</legend>
			<table width="700" class="innertable1">

<?php
echo "<tr>";
echo "<td colspan='2'>";
echo $numofrows, " <b>Assets available</b>";
echo "</td>";
echo "</tr>";
echo "<tr>";
echo "<td>";
echo "<b>Asset Tag:</b>";
echo "</td>";
echo "<td>";
echo "<b>Name:</b>";
echo "</td>";
echo "<td>";
echo "<b>Department:</b>";
echo "</td>";
echo "<td>";
echo "<b>Date Out:</b>";
echo "</td>";
echo "<td>";
echo "<b>Time Out:</b>";
echo "</td>";
echo "<td>";
echo "<b>Estimated Date In:</b>";
echo "</td>";
echo "<td>";
echo "<b>Estimated Time In:</b>";
echo "</td>";
echo "<td>";
echo "<b>Info:</b>";
echo "</td>";
echo "</tr>";

while ($info = mysql_fetch_assoc($query))
{

$asset = $info['asset'];
$name = $info['name'];
$department = $info['department'];
$dateout = $info['dateout'];
$timeout = $info['timeout'];
$edatein = $info['edatein'];
$etimein = $info['etimein'];
$info = $info['info'];

echo "<tr>";
?>
<td class="inputdata"><input size="10" class="text" type="text" name="asset" value="<?php echo $asset ?>"> 
</td>
<td class="inputdata"><input size="10" class="text" type="text" name="name" value="<?php echo $name ?>"> 
</td>
<td class="inputdata"><input size="15" class="text" type="text" name="department" value="<?php echo $department ?>"> 
</td>
<td class="inputdata"><input size="10" class="text" type="text" name="dateout" value="<?php echo $dateout ?>"> 
</td>
<td class="inputdata"><input size="10" class="text" type="text" name="timeout" value="<?php echo $timeout ?>"> 
</td>
<td class="inputdata"><input size="10" class="text" type="text" name="edatein" value="<?php echo $edatein ?>"> 
</td>
<td class="inputdata"><input size="10" class="text" type="text" name="etimein" value="<?php echo $etimein ?>"> 
</td>
<td class="inputdata"><input size="10" class="text" type="text" name="info" value="<?php echo $info ?>"> 
</td>
<?php
echo "<td>";
echo "<a href=checkout.php?asset=$asset&view=checkin><b>Check In</b></a>";
echo "</td>";
?><td>
<center><input class="submit" type="submit" name="form" value="Update"></center></td>
<?php
echo "</tr>";

}
?>


			</table>
            </fieldset>

Open in new window

maximus81Asked:
Who is Participating?
 
Marco GasiFreelancerCommented:
Give to yourtable fields a name with array notation:
 
<td class="inputdata"><input size="10" class="text" type="text" name="asset[]" value="<?php echo $asset ?>"> 
</td>
<td class="inputdata"><input size="10" class="text" type="text" name="name[]" value="<?php echo $name ?>"> 
</td>
<td class="inputdata"><input size="15" class="text" type="text" name="department[]" value="<?php echo $department ?>"> 
</td>
<td class="inputdata"><input size="10" class="text" type="text" name="dateout[]" value="<?php echo $dateout ?>"> 
</td>
<td class="inputdata"><input size="10" class="text" type="text" name="timeout[]" value="<?php echo $timeout ?>"> 
</td>
<td class="inputdata"><input size="10" class="text" type="text" name="edatein[]" value="<?php echo $edatein ?>"> 
</td>
<td class="inputdata"><input size="10" class="text" type="text" name="etimein[]" value="<?php echo $etimein ?>"> 
</td>
<td class="inputdata"><input size="10" class="text" type="text" name="info[]" value="<?php echo $info ?>"> 
</td>

Open in new window


Then, at the top of your code you have to iterate through elements submitted:
 
<?php
if (isset($_POST['form'])){
switch ($_POST['form']) {

case 'Update':

for ($i=0; $i < count($_POST['asset']);$i++){
$asset = $_POST['asset'][$i];
$name = $_POST['name'][$i];
$department = $_POST['department'][$i];
$dateout = $_POST['dateout'][$i];
$timeout = $_POST['timeout'][$i];
$edatein = $_POST['edatein'][$i];
$etimein = $_POST['etimein'][$i];
$info = $_POST['info'][$i];

	$con = mysql_connect("localhost","root","pass");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }
 
mysql_select_db('ats');
echo "UPDATE checkout SET 
name='".$name."',
department='".$department."',
dateout='".$dateout."',
timeout='".$timeout."',
edatein='".$edatein."',
etimein='".$etimein."',
info='".$info."'
WHERE asset = '".$asset."'";

mysql_query("UPDATE checkout SET 
name='".$name."',
department='".$department."',
dateout='".$dateout."',
timeout='".$timeout."',
edatein='".$edatein."',
etimein='".$etimein."',
info='".$info."'
WHERE asset = '".$asset."'") or die (mysql_error());

}
break;

Open in new window


That's all. With your code, php engines updated only the last because avery other value was deleted, or better: overwritten by the last value submitted. Now you treat values as arrays, so you can iterate through them and repeat update operation for each element effectively updated by user.

Hope this makes sense for you.

Cheers
0
 
maximus81Author Commented:
This does make sense and works perfect. Thank you very much for your help.
0
 
Marco GasiFreelancerCommented:
You're welcome. Glad to help you.
Good luck with your project.

Marco
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.