PHP generate dynamic PDO insert

Question

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

pdoStatement::bindParam takes a reference to a variable as its argument, pdoStatement::bindValue takes the value of a variable as its argument. The way you did it, you gave it multiple references to the same variable $value, which will of course all evaluate to the same value. — user1233508
– user1233508, Commented Aug 18, 2012 at 15:39

netcoder · Accepted Answer · 2012-08-18 17:57:57Z

11

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));

edited Aug 18, 2012 at 17:57

answered Aug 18, 2012 at 15:55

netcoder

68k19 gold badges129 silver badges142 bronze badges

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

Comments

Mihai Iorga · Accepted Answer · 2012-08-18 15:55:38Z

2

maybe something like this:

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

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

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

foreach($columns as $key => $value){
    $STH->bindValue(':' . $key, $value);
}

answered Aug 18, 2012 at 15:55

Mihai Iorga

39.8k17 gold badges109 silver badges109 bronze badges

Comments

Makita · Accepted Answer · 2012-08-18 15:42:11Z

0

foreach($array as $key => $value){
    $STH->bindParam(':' . $key,$array[$key]);
}

Try that

answered Aug 18, 2012 at 15:42

Makita

1,80212 silver badges15 bronze badges

Comments

Umair Ayub · Accepted Answer · 2017-05-30 09:34:07Z

$conn = new PDO('mysql:host=' . $HOST . ';dbname=' . $DATABASE, $USERNAME, $PASSWORD);
$conn->exec("set names utf8");
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING);

$ins_query = 'INSERT INTO `' . $table_name . '` ';

$columns = array();
$columns_bindings = array();
foreach ($dataArray as $column_name => $data) {
    $columns[] = $column_name;
    $columns_bindings[] = ':' . $column_name;
}

$ins_query = $ins_query . '(' . implode(', ', $columns) . ') VALUES (' . implode(', ', $columns_bindings) . ')';

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

foreach ($dataArray as $column_name => $data) {
    $stmt->bindValue(":" . $column_name, $data);
}
if (!$stmt->execute()) {
    print_r($stmt->errorInfo());

} else {
    echo "Insertd";
}

Asif Ali · Accepted Answer · 2021-02-26 17:07:52Z

0

@netcoder 's reply is also good if want to achieve your goal using positional placeholders. However, You can use sprintf function to prepare dynamic sql query using named placeholder.

$sql = sprintf("INSERT INTO table (%s) VALUES(%s)",
              implode(", ", array_keys($array)),
              ":" . implode(", :", array_keys($array)));

$pdo->prepare($sql);
$pdo->execute($array);

edited Feb 26, 2021 at 17:07

answered Feb 26, 2021 at 16:17

Asif Ali

681 silver badge6 bronze badges

Collectives™ on Stack Overflow

PHP generate dynamic PDO insert

5 Answers 5

Comments

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related