tam tam - 4 months ago 12
MySQL Question

How to update multiple rows in a table to mysql




First I tried to retrieve multiple rows of data from MySQL and display them in a table

Then I tried to use foreach loop to update multiple rows into the MySQL but in vain, anyone has any solution would be greatly appreciated, thanks





include('connect-db.php');

$result = mysql_query("SELECT vaccinedetail.id,vaccinedetail.vaccineid,vaccinedetail.vaccinename1,vaccinedetail.vaccinename2,vaccinedetail.vaccinename3, vaccinedetail.totalnoofinjection,vaccinedetail.nthinjection,vaccinedetail.skip
FROM vaccinedetail
WHERE vaccinedetail.vaccineid = '" . $_POST['vaccineid'] . "'")
or die(mysql_error());?>

<?php while($row = mysql_fetch_array( $result )){?>

<tr>
<td><input type="text" name = "id[]" value="<?php echo $row['id'] ?>"</td>

<td><input type="text" name = "vaccineid[]" value="<?php echo $row['vaccineid'] ?>"</td>
<td><input type="text" name = "vaccinename1[]" value="<?php echo $row['vaccinename1'] ?>"</td>
<td><input type="text" name = "vaccinename2[]" value="<?php echo $row['vaccinename2'] ?>"</td>
<td><input type="text" name = "vaccinename3[]" value="<?php echo $row['vaccinename3'] ?>"</td>
<td><input type="text" name = "totalnoofinjection[]" value="<?php echo $row['totalnoofinjection'] ?>"</td>
<td><input type="text" name = "nthinjection[]" value="<?php echo $row['nthinjection'] ?>"</td>
<td><input type="text" name = "skip[]" value="<?php echo $row['skip'] ?>"</td>

</tr>

<?php }?>

</table>
<input type="submit" name="submit" value="Save change">
</form>


</div>

</body>
</html>

<?php

include('connect-db.php');

if (isset($_POST["submit"])){
foreach ($_POST['vaccineid'] as $index => $vaccineid) {
$data1 = mysql_real_escape_string($vaccineid);
$data2 = mysql_real_escape_string($_POST['vaccinename1'][$index]);
$data3 = mysql_real_escape_string($_POST['vaccinename2'][$index]);
$data4 = mysql_real_escape_string($_POST['vaccinename3'][$index]);
$data5 = mysql_real_escape_string($_POST['totalnoofinjection'][$index]);
$data6 = mysql_real_escape_string($_POST['nthinjection'][$index]);
$data7 = mysql_real_escape_string($_POST['skip'][$index]);

mysql_query("UPDATE vaccinedetail SET vaccineid ='$data1', vaccinename1 = '$data2',vaccinename2 = '$data3',vaccinename3 = '$data4',
totalnoofinjection = '$data5', nthinjection ='$data6', skip ='$data7'") or die(mysql_error());
header("Location: start.php");
}
}
?>

<html>

<head>
<title>Display Vaccine type</title>
</head>

<body>
<?php
session_start();?>
<form action="" method="POST">
<!-- Create the table and its heading-->

<table border='1' cellpadding='10'>
<tr>
<th>ID</th>
<th>Vaccine ID</th>
<th>疫苗名稱 (繁體)</th>
<th>疫苗名称 (简体)</th>
<th>Vaccine Name (Eng)</th>
<th>Total no of injection</th>
<th>Nth Injection</th>
<th>Next Injection Skip</th>
<th></th>
<th></th>
</tr>




Answer Source

Well, there are a number of issues here

  • You're not updating at an index. This means every query will result in updating every single row. To fix this, use a WHERE clause. That will limit the number of rows.
foreach( $_POST['vaccineid'] as $index => $vaccineid ) {
    $id = mysql_real_escape_string($_POST['vaccineid'][$index]);
    /* Escape string queries */
    mysql_query(
    "UPDATE 
      vaccinedetail SET vaccineid ='$data1', 
      vaccinename1 = '$data2',
      vaccinename2 = '$data3',
      vaccinename3 = '$data4',
      totalnoofinjection = '$data5', 
      nthinjection ='$data6', 
      skip ='$data7' 
    WHERE id = $id") // <-- notice that I added a WHERE clause
        or die(mysql_error()); 
    header("Location: start.php");
}

In addition:

  • You're updating your data after you've already displayed your data. Move if (isset($_POST["submit"])){ to right after include('connect-db.php');.
    • You're not putting an exit statement after the call to header (this will lead to output despite the fact that you don't want it to do that.)
    • You have header called after you've already sent content back to the browser (header must be called before any content is sent in a ?> <?php section).
    • You're using mysql_ library. That's deprecated. Use mysqli_.