Error: how to insert values into mysql using loop?

Question

I have this code allow user to enter age and gender of children and then insert them to DB. when I echo the variables. I can get all values from user because user can enter more than one child. However, when I want to insert into DB, it will insert only the data of last children

e.g.: if user inter two children one with age: 3 gender M and second age 5 gender F, the second values only will be inserted

$age=$_POST['age'];
$gender=$_POST['gender'];

for($i=0;$i<count($gender);$i++)
{
    if($age[$i]!="" && $gender[$i]!="")
    {
        echo $age[$i];
        echo $gender[$i];
        $query = "INSERT INTO `children`(`age` , `gender`)VALUES('$age[$i]' , '$gender[$i]')";      
    }   
 }

 $result = mysqli_query($connection, $query);
 //echo $result;
 echo "<pre>";
 if (!$result)
 {
     die("Query Faile".  mysqli_errno($connection));   
 }

You create the query many times, but you only *execute it* once, after the loop has completed. What do you *expect* `$quiry` to be when you get to the line `mysqli_query($connection, $quiry)`? Why? — David, Jun 09 '18 at 13:39
Possible duplicate of [running multiple queries through a single php mysqli\_query function](https://stackoverflow.com/questions/36449117/running-multiple-queries-through-a-single-php-mysqli-query-function) — Jens, Jun 09 '18 at 13:39
Thank you for your answers. I will pay attention about prepared statements — Ali Al-ali, Jun 09 '18 at 15:43

Mureinik · Accepted Answer · 2018-06-09T15:46:42.307

3

You execute the query once, after the for loop is done. Move the execution into the loop. This works well with a prepared statement, which will also improve your program's security:

$stmt =
    mysqli_prepare($connection, "INSERT INTO `children` (`age`, `gender`) VALUES (?, ?)");

for($i = 0; $i < count($gender); $i++)
{
    if($age[$i]!="" && $gender[$i]!="")
    {
        mysqli_stmt_bind_param($stmt, "ds", $age[$i], $gender[i]);
        mysqli_stmt_execute($stmt);
    }   
}

edited Jun 09 '18 at 15:46

answered Jun 09 '18 at 13:43

Mureinik

297,002
52
306
350

thank you for your help It works good but it need to add parenthesis at the end of the statement $stmt = mysqli_prepare($connection, "INSERT INTO `children` (`age`, `gender`) VALUES (?, ?)"); – Ali Al-ali Jun 09 '18 at 15:45
@AliAl-ali arg, yeah, that was a typo on my side. Edited and fixed, thanks for noticing! – Mureinik Jun 09 '18 at 15:47

score 0 · Answer 2 · answered Jun 09 '18 at 13:52

Change your code like this

**

$stmt =
    mysqli_prepare($connection, "INSERT INTO `children` (`age`, `gender`) VALUES (?, ?");
$res = false;
$count = 0;
for($i = 0; $i < count($gender); $i++)
{
    if($age[$i] != "" && $gender[$i] != "")
    {
        mysqli_stmt_bind_param($stmt, "ds", $age[$i], $gender[i]);
        if(mysqli_stmt_execute($stmt)){
          ++$count;
          $res= true;
        }
    }   
}
if(!$res){
  echo "Failed after ".$count." Data added";
}else{
  echo "Job Done";
}

**

Error: how to insert values into mysql using loop?

2 Answers2