Antonio Milo Antonio Milo - 1 year ago 99
MySQL Question

Replace prepared statement placeholders with array elements in PHP

I would like to replace the placeholders used in a prepared statement with the elements of an array.

Let's say i have this sql string:

SELECT * FROM table WHERE col1 = ? AND col2 = ?

And an array like this:

$array = array(0=>'value1', 1=>'value2')

My output should be:

SELECT * FROM table WHERE col1 = value1 AND col2 = value2

I read that is possible to use an array as parameter for the PHP function str_replace, so my attempt so far is:

function debugQuery($sql,$param){
return $ret = str_replace('?', $param, $sql);

This function instead of my desired output return:

SELECT * FROM table WHERE col1 = Array AND col2 = Array

I think i'm missing something...

Answer Source

Your code is (You will can remove mysql_real_escape_string, but this secure from sql-inj):

$sql = "SELECT * FROM table WHERE col1 = ? AND col2 = ?";
$array = array(0 => "TEST", 1 => "TEST2");
foreach ($array as $value) 
    $sql = preg_replace("#\?#", "'" . mysql_real_escape_string($value) . "'", $sql, 1);
echo $sql;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download