Update mysql table with one query the values from an automated generated form

Question

I have the following form automatically generated in a WHILE using $i++.

<form method="POST" id="form" >
<input type="text" name="id1" value="1"  />
<input type="text" name="q1" value="5"  />
<input type="text" name="a1" value="p" />

<input type="text" name="id2" value="4"  />
<input type="text" name="q2" value="3"  />
<input type="text" name="a2" value="t" />

<input type="text" name="id3" value="8"  />
<input type="text" name="q3" value="7" />
<input type="text" name="a3" value="z" />

.....................

</form>

I insert the values into mysql using:

$id1=$_POST['id1'];
$q1=$_POST['q1'];
$a1=$_POST['a1'];

$query = "UPDATE table SET answer = '$a1' WHERE question = '$q1' and id= '$id1'";
mysqli_query($con,$query) or die(mysqli_error());

$id2=$_POST['id2'];
$q2=$_POST['q2'];
$a2=$_POST['a2'];

$query = "UPDATE table SET answer = '$a2' WHERE question = '$q2' and id= '$id2'";
mysqli_query($con,$query) or die(mysqli_error());

$id3=$_POST['id3'];
$q3=$_POST['q3'];
$a3=$_POST['a3'];

$query = "UPDATE table SET answer = '$a3' WHERE question = '$q3' and id= '$id3'";
mysqli_query($con,$query) or die(mysqli_error());

..........................

It's working just fine but how could I insert the values with only one query and WHILE or foreach? I tried to do like this but something is wrong:

$stmt = $this->mysqli->prepare("UPDATE table SET answer=? WHERE question=? and id =?");
    foreach ($_POST['id'] as $i => $n){
            $stmt->bind_param("sss", $n, $_POST['question'][$i], $_POST['answer'][$i], $id);
            $stmt->execute();
        }

**Warning:** You are wide open to [SQL Injections](https://stackoverflow.com/a/60496/1839439) and should use parameterized **prepared statements** instead of manually building your queries. They are provided by [PDO](https://php.net/manual/pdo.prepared-statements.php) or by [MySQLi](https://php.net/manual/mysqli.quickstart.prepared-statements.php). Never trust any kind of input! Even when your queries are executed only by trusted users, [you are still in risk of corrupting your data](http://bobby-tables.com/). [Escaping is not enough!](https://stackoverflow.com/q/5741187) — Dharman, Aug 30 '20 at 14:05

score 1 · Accepted Answer · answered Aug 30 '20 at 13:29

You can give same name to mutliple inputs and use [] so that it be access using 0,1..etc. So your form will look like below :

<form method="POST" id="form" >
<input type="text" name="id[]" value="1"  />
<input type="text" name="question[]" value="5"  />
<input type="text" name="answer[]" value="p" />

<input type="text" name="id[]" value="4"  />
<input type="text" name="question[]" value="3"  />
<input type="text" name="answer[]" value="t" />

.....................

</form>

And then access same in your php page like below :

 $id= isset($_POST['id']) ? $_POST['id'] : "" ;//get post datas
 $question= isset($_POST['question']) ? $_POST['question'] : "" ;
 $answer= isset($_POST['answer']) ? $_POST['answer'] : "" ;
 $stmt = $this->mysqli->prepare("UPDATE table SET answer=? WHERE question=? and id =?");
  //loop through ids 
   foreach ($id as $key=>$value){
   //answer[0],answer[1]..
         $stmt->bind_param("sss", $answer[$key], $question[$key], $value);
         $stmt->execute();
  }

The problem is that I am using AJAX to update the table and it is not working. Thank you. — Florin, Aug 30 '20 at 15:08
How your form gets submitted? what doesn't work here..? Can you elaborate ? — Swati, Aug 30 '20 at 18:38

Update mysql table with one query the values from an automated generated form

1 Answers1