Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 163
  • Last Modified:

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

0
maximus81
Asked:
maximus81
  • 2
1 Solution
 
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

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now