CODEWITHSUNDEEP
phpsqlarrayspdoinsert
nielsmartens
nielsmartens

Reputation: 39

PDO Inserts multiple rows in single query

im trying to put multiple php arrays into a insert statement and put it in a sql database. I dont have much expierence in programming, so i tried this. But you cant bindValue a php array so it gives null. How can i fix this.

$voornamen,$geslachten,$achternamen,$geboortedata are four seperate php arrays what contains data of multiple user names, date births, surnames etc.

php code to insert:

$query = $conn->prepare( "INSERT INTO ReserveerCursusCursisten 
    (Geslacht, Achternaam, Voorletters, Geboortedatum) VALUES (:geslachten, :achternamen, :voornamen, :geboortedata, 0, 0)");
for($i = 0; $i < count($achternamen); $i++)
{
    $stmt->bindValue(':geslachten', $geslachten);
    $stmt->bindValue(':achternamen', $achternamen);
    $stmt->bindValue(':voornamen', $voornamen);
    $stmt->bindValue(':geboortedata', $geboortedata);
    $stmt->execute();
}

if ($stmt->execute())
{
  echo "query is gelukt";
}
else
{
    echo "query is niet gelukt";
}

Upvotes: 0

Views: 96

Answers (2)

Zhorov
Zhorov

Reputation: 29943

Consider the following:

  • your arrays must have equal count of elements

  • variable $query is never used, change it to $stmt

  • you have four column names, but six values in SQL statement

  • pass array item to binValue(), not the array

Example:

<?php
$stmt = $conn->prepare(
    "INSERT INTO ReserveerCursusCursisten 
        (Geslacht, Achternaam, Voorletters, Geboortedatum) 
    VALUES 
        (:geslachten, :achternamen, :voornamen, :geboortedata)
");
for($i = 0; $i < count($achternamen); $i++)
{
    $stmt->bindValue(':geslachten', $geslachten[$i]);
    $stmt->bindValue(':achternamen', $achternamen[$i]);
    $stmt->bindValue(':voornamen', $voornamen[$i]);
    $stmt->bindValue(':geboortedata', $geboortedata[$i]);
    if ($stmt->execute() === false) {
        echo "query is niet gelukt";
    } else {
        echo "query is gelukt";
    }
}
?>

Notes:

You may skip bindValue() and pass parameters to execute(). In this case all parameters are treated as strings (PDO::PARAM_STR), while with using bindValue() you may define parameter data type (one of PDO::PARAM_* constants).

<?php
$stmt = $conn->prepare(
    "INSERT INTO ReserveerCursusCursisten 
        (Geslacht, Achternaam, Voorletters, Geboortedatum) 
    VALUES 
        (:geslachten, :achternamen, :voornamen, :geboortedata)
");
for ($i = 0; $i < count($achternamen); $i++)
{
    $params = array($geslachten[$i], $achternamen[$i], $voornamen[$i], $geboortedata[$i]);
    if ($stmt->execute($params) === false) {
        echo "query is niet gelukt";
    } else {
        echo "query is gelukt";
    }
}
?>

Part of PHP documentation about prepared statements:

Calling PDO::prepare() and PDOStatement::execute() for statements that will be issued multiple times with different parameter values optimizes the performance of your application by allowing the driver to negotiate client and/or server side caching of the query plan and meta information. Also, calling PDO::prepare() and PDOStatement::execute() helps to prevent SQL injection attacks by eliminating the need to manually quote and escape the parameters.

Upvotes: 2

Amarnasan
Amarnasan

Reputation: 15529

This is the way to build the query: First you build the whole SQL, then you bind the values to each column and row:

$query = "INSERT INTO ReserveerCursusCursisten 
(Geslacht, Achternaam, Voorletters, Geboortedatum) VALUES ";

$subqueries = [];

for($ii=0; $ii< count($geslachten);$ii++) {
    $subqueries []="(:ges$ii,:ach$ii,:voo$ii,:geb$ii)";
}

$query .= implode(",", $subqueries);

$stmt = $conn->prepare($query);

for($ii=0; $ii< count($geslachten);$ii++) {
    $stmt->bindValue(":ges$ii", $geslachten[$ii]);
    $stmt->bindValue(":ach$ii", $achternamen[$ii]);
    $stmt->bindValue(":voo$ii", $voornamen[$ii]);
    $stmt->bindValue(":geb$ii", $geboortedata[$ii]);
}
$stmt->execute();

Upvotes: 0

Related Questions