Is it bad to run a stored procedures N times in a PHP for loop?

Question

I have stored procedure that takes an id and a string and stores the pair in a cross reference table. If my PHP receives 6 sets if id and string pairs, the PHP for-loop will execute this stored procedure six times.

If I have millions of users running this stored procedure, Is this an unnecessarily large load on my servers?

Should I instead concatenate the six strings into one delimited string and pass that larger string as one value to the stored procedure which will then split the string and enter each separate row into my table?

Answer 1

Sounds like you might want something like this:

$pairs = array(
    42=>"Hello",
    93=>"World",
    // ... insert more ID => string pairs
);
// now convert to a query:
$sql = "INSERT INTO `tablename` (`id`,`string`) VALUES ";
$rows = array();
foreach($pairs as $id=>$string) {
    $rows[] = "(".intval($id).",'".appropriate_escaping_function($string)."')";
}
$sql .= implode(", ",$rows);
execute_mysql_query_function($sql);

This requires only a single query to be run, regardless of how many key/value pairs you have.