17

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
  • 6,740
  • 18
  • 77
  • 125
  • 2
    Don't run the prepare more than once. One it's prepared, you can execute it multiple times with different values. – Powerlord Jan 07 '11 at 19:29

3 Answers3

31

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);
}
ircmaxell
  • 163,128
  • 34
  • 264
  • 314
  • thanks 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 Jan 07 '11 at 19:45
  • great. 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. – JM4 Jan 07 '11 at 20:02
  • You make it so simple +1. Respect –  Feb 03 '14 at 22:12
9

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);
Xavier Barbosa
  • 3,919
  • 1
  • 20
  • 18
  • thanks 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 Jan 07 '11 at 19:44
  • The multiple queries are slower about ten milliseconds, but they are more readable and easier to debug. – Xavier Barbosa Jan 07 '11 at 22:29
1

What you are looking for is how to do a BULK insert, this is more SQL related than to PDO itself.

You only have to do the exact same thing than with *_query, build your bulk insert query and your param array side by side.

$placeholder = array();
$values = "?, ?, ?, ...";
$args = array();
foreach ($arrays as $array) {
  $placeholder[] = $value;
  $args[] = $array['col1'];
  $args[] = $array['col2'];
  $args[] = $array['col3'];
  ...
}    
$sql = "INSERT INTO table (col1, col2, ... ) VALUES ("
     . implode('), (', $placeholder)
     . ")"; 
$stmt = $db->prepare($sql);
$db->execute($sql, $args);

This is an ugly but working algorithm, I think.

Pierre
  • 146
  • 4