I have always passed an array of my values into execute as it seemed easier to read imo. I was recently working on a script and noticed it was using bindParam and later came to find out how this passes the variable reference (I never knew).
With that said, in a current project I can think of quite a few uses for bindParam instead of my current array usage.
With that said, is there any performance difference between bindValue/bindParam and using an array on execute... especially in the case of repeated loops? I find myself doing a lot of $stmt->execute( array_merge($binding_clause, $binding) ); where I have a few binds that won't change and some that will, in loops of course, where as far as I can tell using bindParam would be perfect for this.
Does adding the type (PDO::PARAM_STR, PDO::PARAM_INT) with the first have any performance over not doing so when using an array (I believe it is string by default with arrays)?
Example difference between the two (these are prepared statements) :
$binding = array(
'cw_account_id' => $_SESSION['user']['account_id'],
'cw_date_start' => $date_start,
'cw_date_end' => $date_end,
'cw_start' => $b_counter * 500
);
$stmt->execute($binding);
compared to
$stmt->bindValue(':cw_account_id', $_SESSION['user']['account_id'], PDO::PARAM_INT);
$stmt->bindValue(':cw_date_start', $date_start, PDO::PARAM_INT);
$stmt->bindValue(':cw_date_end', $date_end, PDO::PARAM_INT);
$stmt->bindValue(':cw_start', $b_counter * 500, PDO::PARAM_INT);
$stmt->execute();
Aside from the questions above, would each $stmt->bindValue() here be another trip to the db as compared to one when using the array method?
Aside from readability of code, and how bindParam references values, are there are performance positives/negatives between the two (small and large scale usage... repeated loops included)?
Aside from readability of code, and how bindParam references values, are there are performance positives/negatives between the two
No.
would each $stmt->bindValue() here be another trip to the db
No.