individual update for every row in database using

Question

while using input type as number, i want to update the database but every time the script is run, the values from the last row are updated to the whole table. only those values must be changed corresponding to which the change is made.

$conn=mysql_connect('localhost','root','');
$db=mysql_select_db('shaggy');

$q = "SELECT * FROM `automata`";
$re=mysql_query($q)or die(mysql_error());
$num=mysql_num_rows($re);

the sql connectivity from where the number of rows is extracted

$i=0;
while($num>$i)
{
$bupin=mysql_result($re,$i,'bupin');
$name=mysql_result($re,$i,'name');
$q1=mysql_result($re,$i,'q1');
$q2=mysql_result($re,$i,'q2');
$mst1=mysql_result($re,$i,'mst1');
$mst2=mysql_result($re,$i,'mst2');
$est=mysql_result($re,$i,'est');

          echo "<tr>
        <td> <b>$bupin</b> </td>
        <td><b> $name </b></td>

        <td><input type='number' min='00' max='30' value='$q1' name='f1' ></td>

        <td><input type='number' min='00' max='30' value='$q2' name='f2' ></td>

        <td><input type='number' min='00' max='50' value='$mst1' name='f3' ></td>

        <td><input type='number' min='00' max='50' value='$mst2' name='f4' ></td>

        <td><input type='number' min='00' max='100' value='$est' name='f5' ></td>
          </tr>";

$i++;
}

with the help of this script all the values from the database table are being displayed and the option for number is also available that the user wants to change. but on changing the values and submitting it, all the table is being assigned with the values that were updated in the last row and is not updated with their individual values. The code for updation is as follows:

$conn=mysql_connect('localhost','root','');
$db=mysql_select_db('shaggy');

$q = "SELECT * FROM `automata`";
$re=mysql_query($q)or die(mysql_error());
$num=mysql_num_rows($re);

$i=0;
while($num>$i)
{
     $a=$_POST['f1'];
    $b=$_POST['f2'];
$c=$_POST['f3'];
$d=$_POST['f4'];
$e=$_POST['f5'];
$nbupin=mysql_result($re,$i,'bupin');
$nname=mysql_result($re,$i,'name');
mysql_query("UPDATE automata SET q1=$a, q2=$b, mst1=$c, mst2=$d, est=$e WHERE bupin='$nbupin'");
$i++;
}
echo "Success";

Since you are building your ``s in a loop, the last ones will always overwrite all the other values. You should do these as an array, with a paired key, and then use that key when you do the loop on the post side. — Sean, Dec 19 '15 at 04:12
where is primary key ? how do you know which row to update ? BTW, `mysql` is deprecated, start using `mysqli` or `PDO`. Also I suggest use array for post, with index as the primary key. — Jigar, Dec 19 '15 at 04:21

score 3 · Accepted Answer · edited May 23 '17 at 12:23

The easiest way to do this is to make your <input>s into arrays, with the $bupin as the key.

$i=0;
while($num>$i)
{
    $bupin=mysql_result($re,$i,'bupin');
    $name=mysql_result($re,$i,'name');
    $q1=mysql_result($re,$i,'q1');
    $q2=mysql_result($re,$i,'q2');
    $mst1=mysql_result($re,$i,'mst1');
    $mst2=mysql_result($re,$i,'mst2');
    $est=mysql_result($re,$i,'est');

          echo "<tr>
        <td> <b>$bupin</b> </td>
        <td><b> $name </b></td>

        <td><input type='number' min='00' max='30' value='$q1' name='f1[$bupin]' ></td>

        <td><input type='number' min='00' max='30' value='$q2' name='f2[$bupin]' ></td>

        <td><input type='number' min='00' max='50' value='$mst1' name='f3[$bupin]' ></td>

        <td><input type='number' min='00' max='50' value='$mst2' name='f4[$bupin]' ></td>

        <td><input type='number' min='00' max='100' value='$est' name='f5[$bupin]' ></td>
          </tr>";

$i++;
}

Now, on form submit, you can use that $bupin in a loop to update your table.

$conn=mysql_connect('localhost','root','');
$db=mysql_select_db('shaggy');

foreach($_POST['f1'] as $bupin => $value)
{
    $a=$_POST['f1'][$bupin];
    $b=$_POST['f2'][$bupin];
    $c=$_POST['f3'][$bupin];
    $d=$_POST['f4'][$bupin];
    $e=$_POST['f5'][$bupin];

    mysql_query("UPDATE automata SET q1=$a, q2=$b, mst1=$c, mst2=$d, est=$e WHERE bupin='$bupin'");
}
echo "Success";

Note, you should also update your mysql_ code to either mysqli_ or PDO, and also learn about sql injection - see How can I prevent SQL-injection in PHP?

individual update for every row in database using

1 Answers1