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
– David, Commented Jun 9, 2018 at 13:39
Possible duplicate of running multiple queries through a single php mysqli_query function — Jens
– Jens, Commented Jun 9, 2018 at 13:39
Thank you for your answers. I will pay attention about prepared statements — Ali Al-ali
– Ali Al-ali, Commented Jun 9, 2018 at 15:43

Mureinik · Accepted Answer · 2018-06-09 15:46:42Z

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 9, 2018 at 15:46

answered Jun 9, 2018 at 13:43

Mureinik

316k54 gold badges399 silver badges405 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Ali Al-ali Over a year ago

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 (?, ?)");

Mureinik Over a year ago

@AliAl-ali arg, yeah, that was a typo on my side. Edited and fixed, thanks for noticing!

Al-amin Islam Nerob · Accepted Answer · 2018-06-09 13:52:26Z

0

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";
}

**

answered Jun 9, 2018 at 13:52

Al-amin Islam Nerob

13 bronze badges

Collectives™ on Stack Overflow

Error: how to insert values into mysql using loop?

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related