Inserting multiple row records using mysqli bind_param?

Question

I have a code here that works using mysql query.

$N = count($fullname);
for($i=0; $i < $N; $i++)
    mysql_query("INSERT INTO famcomp(fullname,fage,frel,fcivil,fedu,foccup,finco,app_id) VALUES ('$fullname[$i]','$fage[$i]','$frel[$i]','$fcivil[$i]','$fedu[$i]','$foccup[$i]','$finco[$i]','$id')");

how to convert this into mysqli bind_param here's what I did: (I have connection.php (mysqli))

$N = count($fullname);
for($i=0; $i < $N; $i++)
    $stmt = $conn->prepare("INSERT INTO famcomp(fullname,fage,frel,fcivil,fedu,foccup,finco,app_id) VALUES (?,?,?,?,?,?,?,?)");
    $stmt ->bind_param("ssssssss",$fullname[$i],$fage[$i],$frel[$i],$fcivil[$i],$fedu[$i],$foccup[$i],$finco[$i],$id);
}

but still not getting the data.

"s" stands for character/string datatypes. in case of integer, use"i". ex: "sssissi". @kim — Subin Thomas, Oct 03 '15 at 08:29
yup I get it. the result im geting string+int. so is it possible to do it in bind_param? — kim de castro, Oct 03 '15 at 08:29
yes. if your id is integer, use bind_param("sssssssi",..........); — Subin Thomas, Oct 03 '15 at 08:31
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/91228/discussion-between-subin-thomas-and-kim-de-castro). — Subin Thomas, Oct 03 '15 at 08:52

score 1 · Answer 1 · answered Oct 03 '15 at 08:31

With this code:

$N = count($fullname);
for($i=0; $i < $N; $i++)
    $stmt = $conn->prepare("INSERT INTO famcomp(fullname,fage,frel,fcivil,fedu,foccup,finco,app_id) VALUES (?,?,?,?,?,?,?,?)");
    $stmt ->bind_param("ssssssss",$fullname[$i],$fage[$i],$frel[$i],$fcivil[$i],$fedu[$i],$foccup[$i],$finco[$i],$id);
}

you just bind_params and that's all. Statement should be executed. It can be executed several times even with one call for prepare:

$N = count($fullname);
$stmt = $conn->prepare("INSERT INTO famcomp(fullname,fage,frel,fcivil,fedu,foccup,finco,app_id) VALUES (?,?,?,?,?,?,?,?)");
for($i=0; $i < $N; $i++)
    $stmt ->bind_param("ssssssss",$fullname[$i],$fage[$i],$frel[$i],$fcivil[$i],$fedu[$i],$foccup[$i],$finco[$i],$id);
    $stmt->execute();
}

still not working. no error but it doesnt get the data. – kim de castro Oct 03 '15 at 08:41 — kim de castro, Oct 03 '15 at 08:41

Your Common Sense · Answer 2 · 2015-10-03T08:35:36.443

Mysqli prepared statements are quite tricky to use, thus, I'd recommend to use PDO instead. With PDO your code would work all right:

$stmt = $conn->prepare("INSERT INTO famcomp(fullname,fage,frel,fcivil,fedu,foccup,finco,app_id) VALUES (?,?,?,?,?,?,?,?)");
$N = count($fullname);
for($i=0; $i < $N; $i++) {
    $stmt->execute(array($fullname[$i],$fage[$i],$frel[$i],$fcivil[$i],$fedu[$i],$foccup[$i],$finco[$i],$id));
}

While for mysqli you have to bind variables first, and then just change subset of variables before execute. And of course you have to call execute() as well.

$stmt = $conn->prepare("INSERT INTO famcomp(fullname,fage,frel,fcivil,fedu,foccup,finco,app_id) VALUES (?,?,?,?,?,?,?,?)");
$stmt ->bind_param("ssssssss",$fullname_,$fage_,$frel_,$fcivil_,$fedu_,$foccup_,$finco_,$id);

$N = count($fullname);
for($i=0; $i < $N; $i++)
    $fullname_ = $fullname[$i];
    // and so on
    $stmt->execute();
}

Note that when using prepared statements you have to prepare and bind only once.

score -1 · Accepted Answer · answered Oct 03 '15 at 09:58

If you guys looking for solution. here it is: thanks for those who shared their codes and helping me to give ideas to solved this.

$stmt = $conn->prepare("INSERT INTO famcomp (fullname,fage,frel,fcivil,fedu,foccup,finco,app_id) values (?,?,?,?,?,?,?,?)");

for ($i=0; $i<count($fullname); $i++) {
    $fullname1 = $fullname[$i];
    $fage1 = $fage[$i];
    $frel1 = $frel[$i];
    $fcivil1 = $fcivil[$i];
    $fedu1 = $fedu[$i];
    $foccup1 = $foccup[$i];
    $finco1 = $finco[$i];
    $id1 = $id;
    $stmt->bind_param('sssssssi', $fullname1, $fage1, $frel1, $fcivil1, $fedu1, $foccup1, $finco1, $id1);

    $stmt->execute();
}
echo "Done";

$stmt->close();

Inserting multiple row records using mysqli bind_param?

3 Answers3