PDO query using dynamic parameter (arrays)-CodePudding

I am working out a function that is meant to query to the database using PDO. I am working with arrays to execute. I am getting error HY093. Below is my code

//my function 
function test_function($statement,$data,$connect)
{
    $gg = implode(',',$data);
    $sth = $connect->prepare($statement);
    $sth ->execute(array($gg));
    $r_result = $sth->fetch();
    $show_result = $r_result['0'];
    return $show_result;
}

$datas = array("':ids' => 1"," ':stats' => 1");
$showsh = test_function("SELECT COUNT(*) FROM table WHERE col1 > :ids AND col2 = 
    :stats",$datas,$con);
echo $showsh;

Any guidance will be helpful.

CodePudding user response：

Your first error is in the creation of the array. You're creating an array with 2 strings, instead of an array with 2 key/value pairs. It should be this:

$datas = array(':ids' => 1,':stats' => 1);

Next up is inside of the function. You're turning the $data variable into a string, then passing that inside of an array into your query. Forget all that, and just pass $data into your execute.

$sth = $connect->prepare($statement);
$sth ->execute($data);

CodePudding user response：

Refactor $datas to [":ids" => 1, ":stats" => 1]
Then edit the function:

function test_function($statement,$data,$connect)
{
    $sth = $connect->prepare($statement);
    $sth ->execute($data);
    $r_result = $sth->fetch();
    $show_result = $r_result['0'];
    return $show_result;
}

If you must not change the $datas format, you must refactor it within the code. Something like:

$correctData = [];
foreach ($datas as $item) {
    $elements = explode("=>", $item);
    $key = preg_replace("/\s\'/", "", $elements[0]);
    $element = preg_replace("/\s\'/", "", $elements[1]);
    $correctData[] = [$key => $element];
}
$showsh = test_function("SELECT COUNT(*) FROM table WHERE col1 > :ids AND col2 = 
    :stats",$correctData,$con);

Edited: preg_replace("(/\s)(\')/", "",... to preg_replace("/\s\'/", "",...

CodePudding user response：

Thank you Aynber for your answer. I am posting an answer using the ? instead of the :. In this answer I am not using the associative array.

function test_function($statement,$data,$connect)
{
    $sth = $connect->prepare($statement);
    $sth ->execute($data);
    $r_result = $sth->fetch();
    $show_result = $r_result['0'];
    return $show_result;
}
$datas = array(1,1);
$showsh = test_function("SELECT COUNT(*) FROM table WHERE col1 >? AND col2 = ?",$datas,$con);
echo $showsh;