PHP generate dynamic PDO insert

user_641843 0
Viewed 7048

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);
    }
pdo mysql php
5 Answers

@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);
user_82976890
Related