PHP generate dynamic PDO insert
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));
MySQL PDO INSERT dynamically generated query
You can do it like this:
'INSERT INTO `table1` SET `id`= :id, ' . join(', ', $parts)
If you have an autoincrement on id:
'INSERT INTO `table1` SET ' . join(', ', $parts)
PDO Insert into database values from dynamic html table
Your query is setup as if it were an UPDATE
query. An INSERT
query is different, using this format:
INSERT INTO `table` (`col1`, `col2`, ...)
VALUES ('val1', 'val2', ...)
An INSERT
requires that you specify the columns you'll be inserting into followed by an equal number of VALUES
.
UPDATE: Thanks to the good catch by @Mihai, "untiprice insted of unitprice, the placeholder and the bindparam value are different"
PDO: Create dynamic insert syntax error
There is a missing, )
, to close the column assignment, before the implode.
$sql = "INSERT INTO stackoverflowplaceholder (".implode("','",$stacks)." VALUES (";
will be
$sql = "INSERT INTO stackoverflowplaceholder (".implode("','",$stacks)." ) VALUES (";
php pdo dynamic html form insert array
By default, php put anonymous arrays of inputs into separated items, that's the reason for what you post 2 inputs with id and name and php interprets it as an array of 4 items, thus your foreach will loop 4 times instead of 2, and you will never be able to catch id and name in the same loop.
The best solution is to put and number for each input group, like:
items[0][id]
items[0][name]
items[1][id]
items[1][name]
See this post for further info: How to POST data as an indexed array of arrays (without specifying indexes)
But, there's an other way to achive what you want without defining the index numbers, it only requires that you invert you arrays in inputs names like this:
from: items[][id]
to: items[id][]
and from: items[][name]
to: items[name][]
and your php should be:
if(isset($_POST['submit']))
{
$items = (isset($_POST['items']) && is_array($_POST['items'])) ? $_POST['items'] : array();
$array_ids = $items['id'];
$array_names = $items['name'];
$array_items = array_combine($array_ids, $array_names);
$insertStmt = $db->prepare("INSERT INTO products (prod_id, type) VALUES (:id, :name)");
foreach ($array_items as $id => $name) {
$insertStmt->bindValue(':id', $id);
$insertStmt->bindValue(':name', $name);
$insertStmt->execute();
}
}
that way you create two arrays, one containing only the ids and one containing only the names, and then you combine them to one array that puts id on key and name on value. With that, you can do your foreach and retrieve the id and name on one loop.
dynamic prepared insert statement
public function create() {
$db = Database::getInstance();
$mysqli = $db->getConnection();
$attributes = $this->sanitized_attributes();
$tableName = static::$table_name;
$columnNames = array();
$placeHolders = array();
$values = array();
foreach($attributes as $key=>$val)
{
// skip identity field
if ($key == static::$identity)
continue;
$columnNames[] = '`' . $key. '`';
$placeHolders[] = '?';
$values[] = $val;
}
$sql = "Insert into `{$tableName}` (" . join(',', $columnNames) . ") VALUES (" . join(',', $placeHolders) . ")";
$statement = $mysqli->stmt_init();
if (!$statement->prepare($sql)) {
die("Error message: " . $mysqli->error);
return;
}
$bindString = array();
$bindValues = array();
// build bind mapping (ssdib) as an array
foreach($values as $value) {
$valueType = gettype($value);
if ($valueType == 'string') {
$bindString[] = 's';
} else if ($valueType == 'integer') {
$bindString[] = 'i';
} else if ($valueType == 'double') {
$bindString[] = 'd';
} else {
$bindString[] = 'b';
}
$bindValues[] = $value;
}
// prepend the bind mapping (ssdib) to the beginning of the array
array_unshift($bindValues, join('', $bindString));
// convert the array to an array of references
$bindReferences = array();
foreach($bindValues as $k => $v) {
$bindReferences[$k] = &$bindValues[$k];
}
// call the bind_param function passing the array of referenced values
call_user_func_array(array($statement, "bind_param"), $bindReferences);
$statement->execute();
$statement->close();
return true;
}
I want to make special note that I did not find the solution myself. I had a long time developer find this solution and wanted to post it for those that might want to know.
Related Topics
Browscap Ini Directive Not Set
Can Anyone Give Me an Example for PHP's Curlfile Class
Process Very Big CSV File Without Timeout and Memory Error
Get All Records from MySQL Database That Are Within Google Maps .Getbounds
PHP Fatal Error: Call to a Member Function Format() on Boolean
PHP How to Remove Last Part of a Path
Which Is Faster? Constants, Variables or Variable Arrays
Laravel SQLstate[Hy000] [2002] Connection Refused
File_Get_Contents Returns Empty String
What Does It Mean to Run PHP in Quiet Mode
How to Extend the Zend Navigation Menu View Helper
Removing Password from Rsa Private Key
Get Refunded Orders and Refunded Order Items Details in Woocommerce 3
Forcing Fputcsv to Use Enclosure for *All* Fields