I am updating / refining database code, and I was wondering what I should expect from using prepared statements.
Take this code example:
(naked with me, I know this is ugly - I wrote it)
$values = '';
for ($i = 0; $i < $count; $i++) {
$name = mysql_real_escape_string ($list[$i][1]);
$voc = mysql_real_escape_string ($list[$i][3]);
$lev = $list[$it][2];
$lev = is_numeric ($lev)? $lev : 0;
$values .= ($values == '')? "('$name', '$voc', $lev)" : ", ('$name', '$voc', $lev)";
}
if ($values != '') {
$core->query ("INSERT INTO onlineCList (name, voc, lev) VALUES $values;");
}
Now, despite the obvious gain in readability (sanity) and the fact that it is max_packet_sizeno longer a problem, should I expect any changes in performance when I recode it to use prepared statements? I am connecting remotely to a MySQL server, and I am worried that sending multiple small packages will be significantly slower than sending one large package. If so, can MySQLi / mysqlnd cache these packages?
Another example:
$names = '';
while ($row = mysql_fetch_array ($result, MYSQL_ASSOC)) {
$name = mysql_real_escape_string($row['name']);
$names .= ($names == '') ? "'$name'" : ", '$name'";
}
if ($names != '') {
$core->query ("UPDATE onlineActivity SET online = NULL WHERE name IN ($names) AND online = 1;");
}
, , , ? MySQL, IN (.. WHERE name = $name AND ..)?
, .