Antonio Milo Antonio Milo - 9 days ago 4
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

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

<?php
$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;
Comments