How to insert an array into a single MySQL Prepared statement w/ PHP and PDO
Solution 1
You could build the query programatically...:
$sql = 'INSERT INTO table (memberID, programID) VALUES ';
$insertQuery = array();
$insertData = array();
foreach ($data as $row) {
$insertQuery[] = '(?, ?)';
$insertData[] = $memberid;
$insertData[] = $row;
}
if (!empty($insertQuery)) {
$sql .= implode(', ', $insertQuery);
$stmt = $db->prepare($sql);
$stmt->execute($insertData);
}
Solution 2
2 solutions
// multiple queries
$stmt = $pdo->prepare('INSERT INTO table SET memberID=:memberID, programID=:programID, date_added=NOW()');
$data = array(155, 165, 175, 185);
foreach($data as $d) {
$stmt->execute(array(':memberID' => $memberid, ':programID' => $d));
}
And
// one query
$data = array(155, 165, 175, 185);
$values = array();
foreach($data as $d) {
$values[] = sprintf('(%d, %d, NOW())', $d, $memberid);
}
$sql = sprintf('INSERT INTO table (memberID, programID, date_added) VALUES %s', implode (', ', $values));
$pdo->exec($sql);
JM4
I studied engineering/management of technology but got tossed into the world of web development after working years in a project management capacity. Work with healthcare/insurance companies to develop custom database and reporting solutions along with online enrollment applications.
Updated on August 20, 2022Comments
-
JM4 over 1 year
During an online enrollment, a customer may select a number of programs which they choose to enroll for. These programs are three digit integers and are stored in an array.
For example:
I want to enroll in programid 155, 165, 175, and 185.
My array is set up as simple as:
$data = array(); $data[] = 155; $data[] = 165; $data[] = 175; $data[] = 185;
When it comes time to insert this information into the associated table, I also include additional elements from the other part of the enrollment:
For example, if I were doing a SINGLE program insert statement, it would look as follows:
$stmt = $db->prepare("INSERT INTO table SET memberID=?, programID=?, date_added=NOW()"); $stmt->execute(array($memberid, 155));
I would normally create a simple loop for the array above which would call multiple instances of the sql statement and execute such as:
for($j = 0; $j < (count($data)-1); $j++) { $stmt = $db->prepare("INSERT INTO table SET memberID=?, programID=?, date_added=NOW()"); $stmt->execute(array($memberid, $data[$j])); }
I do realize the code above is invalid ( $data[$j] ) but looking for the right way to do the call.
I have also been told before that building a single dynamic sql statement is overall better than multiple calls like above. My first pass would be something like:
$sql = array(); foreach( $data as $row ) { $sql[] = '("'.$memberid.'", "'.$row[$j].'", NOW()")'; } mysql_real_query('INSERT INTO table (memberid, programid) VALUES '.implode(',', $sql));
but with PDO I am not quite sure how this works, especially with placeholders (?).
Any suggestions?
-
JM4 over 13 yearsthanks for your post, although, isnt the first answer above making multiple queries and taxing the database more than the latter? (most have told me not to use as such). I will test the latter as it seems to probably fit my needs better. Of note: i have zero problem doing multiple queries, only have been told it is 'slower' (with a max of 8 records, one second means nothing to me).
-
JM4 over 13 yearsthanks for the suggestion. I am toying with it right now. The implode is throwing an error but I'll let you know what comes up.
-
JM4 over 13 yearsgreat. thanks for the help! Thinking through the date_added in the example above, creating a new $insertdata[] = NOW() element would not insert properly (as PDO would treat that as a varchar input and treat it literally instead of mysql date format. Normally I would simply generate the query: date_added = NOW() but with the values as above, can this be done? I do realize I could simply run php date(Y-m-d H:i:s) but wanted to see if NOW() were possible.
-
Xavier Barbosa over 13 yearsThe multiple queries are slower about ten milliseconds, but they are more readable and easier to debug.
-
Admin over 10 yearsYou make it so simple +1. Respect