PDO query using dynamic parameter (arrays)

Question

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.

Your $dates array contains 2 strings, not key => value pairs, because you've quoted both. You then turn it into a straight string, and pass it inside another array. This means none of your parameters are being bound. — aynber
– aynber, Commented Apr 21, 2022 at 18:00
First you $gg = implode(',',$data); and then $sth ->execute(array($gg));, at which point $gg will be an array with one comma-separated string (with the keys lost). Why implode? — Markus AO
– Markus AO, Commented Apr 21, 2022 at 18:05

aynber · Accepted Answer · 2022-04-21 18:22:12Z

2

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);

answered Apr 21, 2022 at 18:22

aynber

23.1k9 gold badges57 silver badges69 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Vincent Omondi Over a year ago

When edited as guided. I am getting the error: Warning: PDOStatement::execute(): SQLSTATE[HY093]:

aynber Over a year ago

Can you edit your post with your current code, as well as the full error message? If I remember right, HY093 is mismatched parameters, which means $data should have 2 key/value pairs matching :ids and :stats

Vincent Omondi Over a year ago

This has worked. Thank you.

SIR - 9I214 · Accepted Answer · 2022-04-21 18:40:08Z

0

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\'/", "",...

edited Apr 21, 2022 at 18:40

answered Apr 21, 2022 at 18:21

SIR - 9I214

491 silver badge10 bronze badges

3 Comments

Vincent Omondi Over a year ago

I have done as guided I am getting the following errors 1. Notice: Array to string conversion 2. Warning: PDOStatement::execute(): SQLSTATE[HY093]: Invalid parameter number: parameter was not defined in

SIR - 9I214 Over a year ago

I've just edited the comment. Anyway, can you please specify which line triggered the notice? And it is not clear why the PDO Exception was triggered as I see no parameter "number" in the code provided

Vincent Omondi Over a year ago

Line 4: Notice: Array to string conversion in Line 4: Warning: PDOStatement::execute(): SQLSTATE[HY093]:

Vincent Omondi · Accepted Answer · 2022-04-21 20:32:21Z

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;

Collectives™ on Stack Overflow

PDO query using dynamic parameter (arrays)

3 Answers 3

3 Comments

3 Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

3 Comments

Comments

Related