Php pdo dynamic insert query

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



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

asked Aug 18, 2012 at 15:33


Forget about bindParam, just use execute and pass it the values of $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})");

answered Aug 18, 2012 at 15:55

Php pdo dynamic insert query


64.9k17 gold badges120 silver badges142 bronze badges


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

Php pdo dynamic insert query

Mihai IorgaMihai Iorga

38.6k15 gold badges107 silver badges106 bronze badges

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

Try that

answered Aug 18, 2012 at 15:42


1,80212 silver badges15 bronze badges

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

$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()) {

} else {
    echo "Insertd";

answered May 30, 2017 at 9:34

Umair AyubUmair Ayub

16.8k14 gold badges66 silver badges142 bronze badges

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


answered Feb 26, 2021 at 16:17

Php pdo dynamic insert query

Asif AliAsif Ali

681 silver badge6 bronze badges


How to insert query in php PDO?

In order to run an INSERT query with PDO just follow the steps below:.
create a correct SQL INSERT statement..
replace all actual values with placeholders..
prepare the resulting query..
execute the statement, sending all the actual values in the form of array..

How can check insert query was successful in PHP PDO?

If verification method needed, add any sequential column..
To tell the success, no verification is needed. ... .
To handle the unexpected error, keep with the same - no immediate handling code is needed..

How fetch data from database in PHP and display PDO?

Fetch data from a result set by calling one of the following fetch methods: To return a single row from a result set as an array or object, call the PDOStatement::fetch method. To return all of the rows from the result set as an array of arrays or objects, call the PDOStatement::fetchAll method.

What is PDO in database?

PDO is an acronym for PHP Data Objects. PDO is a lean, consistent way to access databases. This means developers can write portable code much easier. PDO is not an abstraction layer like PearDB. PDO is a more like a data access layer which uses a unified API (Application Programming Interface).