PHP generate dynamic PDO insert

Viewed 90 times

The following code should insert each key-value pair in an array into a mathing column-value in a table. The script returns no errors but the the inserted row contains only the last value in the array

E.g.

array('one'=>1,'two'=>2,'three'=>3);

insert the row successfully in a table with columns one, two and three but insert the value 3 in all.

    $columns = array();
    $bind = '';
    foreach($array as $key => $value){

        $columns[] = $key;

    }

    $columnString = implode($columns,',');
    $valueString = implode($columns,',:');
    $valueString = ':' . $valueString;

    $core = core::getInstance();
    $STH = $core->dbh->prepare("INSERT INTO table (" . $columnString . ") VALUES 
    (" . $valueString . ")");

    foreach($array as $key => $value){

        $STH->bindParam(':' . $key,$value);
    }

Answers

Forget about bindParam, just use execute and pass it the values of $array:

$STH->execute($array);

Alternatively, you could scratch the named parameters altogether to simplify your code a little:

$columnString = implode(',', array_keys($array));
$valueString = implode(',', array_fill(0, count($array), '?'));

$STH = $core->dbh->prepare("INSERT INTO table ({$columnString}) VALUES ({$valueString})");
$STH->execute(array_values($array));

Friday, October 7, 2022

lor.luzzi97-2abdee4f9b7e

The following works for me:

<?php

$pdo = new PDO("mysql:host=localhost;dbname=test", "root", "pass");
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);

$stmt = $pdo->prepare("INSERT INTO `null_test` (`can_be_null`) VALUES (:null)");
$stmt->bindValue(":null", null, PDO::PARAM_NULL);

$stmt->execute();

Pass in PHP's null, with type of PDO::PARAM_NULL. Also, make sure your prepare emulation is set to false. That might help.

Sunday, August 7, 2022

mohammad_reza_khahani

lastInsertId() is a method of the PDO class, not the PDOStatement class.

This should work:

$groupID = $dbo->lastInsertId();

Friday, November 18, 2022

jon_rimmer

The idea is to prevent running transactions for each insert, because it will be very slow indeed. So just start and commit the transaction, say for every 10k records.

$dbh->beginTransaction();
$counter = 0;
foreach($array as $item) {
    $query->execute(array_values($item));
    if ($counter++ % 10000 == 0) {
        $dbh->commit();
        $dbh->beginTransaction();
    }
}
$dbh->commit();

Another solution, you can move an array in a csv file and then just import it.

Tuesday, September 13, 2022

grzenio

You don't quote placeholders. That turns them into strings, not placeholders:

... VALUES(:id_facebook, :nome, :email)
                         ^----^-^-----^--- note the lack of quotes

is all that's required

The whole point of placeholders is to remove any need for quoting/escaping. The DB engine takes care of all that for you.

Monday, August 29, 2022

newbo.o

Only authorized users can answer the search term. Please sign in first, or register a free account.

Not the answer you're looking for? Browse other questions tagged :

php

mysql

pdo

PHP generate dynamic PDO insert

Answers

php,mysql,pdo