PHP generates PDO dynamic insertion

Question

The following code should insert each key-value pair in the array into the value of the mathematical column in the table. the script does not return errors, but the inserted row contains only the last value in the array

eg.

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

insert row successfully into table with columns one, two and three, but insert value 3.

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

Answer 1

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

 $STH->execute($array); 

In addition, you can scratch named parameters in general to simplify the code a bit:

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

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

Answer 3

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

Try

Answer 4

 $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"; }