CODEWITHSUNDEEP
phpmysqlpdo
Sergio
Sergio

Reputation: 11

PHP PDO query with parameters in array

I have a problem with setting up a PDO query. My PDO query looks like:

$query= "
SELECT COUNT(k.id) AS total 
FROM members k 
INNER JOIN members_subscription p ON p.id = k.id 
WHERE k.status=? 
AND k.gender IN (?,?) 
AND country= ? 
AND pic1 !="" 
AND galer !=? 
AND video !=? 
AND birthday < ? 
AND birthday > ? 
AND purposes in(?,?,?,?) ";

The function that executes this query:

$rows_pr = sql_pdo_funct($query, array($status.$gender_one.$gender_two.$location.$gal_prm.$video_prm.$year_old.$purposes ));

If I set static parameters like:

$rows_pr = sql_pdo_funct($query, array(7,2,5,1,0,0,1999-08-08,1992-08-08,1,2,3,4));

I get correct value as a query result. But if I'm trying to add dynamic values in PHP like:

 $status = '7';
 $gender = ',2,5';
 $location= ',1';
 $gal_prm= ',0';
 $video_prm= ',0';
 $year_old= ',1999-08-08,1992-08-08';
 $purposes_prm= ',1,2,3,4';

And put that in sql_pdo_funct function I get error message:

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[HY093]: Invalid parameter number' in...

The function call: 

$rows_pr = sql_pdo_funct($query,array($status.$gender.$location.$gal_prm.$video_prm.$year_old.$purposes ));

Why this error occurs? What am I doing wrong and how this can be done?

Thank you for any help and advice.

Upvotes: 0

Views: 904

Answers (3)

ArtisticPhoenix
ArtisticPhoenix

Reputation: 21681

Syntax error,

array($status.$gender_one.$gender_two.$location.$gal_prm.$video_prm.$year_old.$purposes )

Try using the , instead of the .

The . is for concatenation, adding strings together, the comma , is for separating array elements.

Easy mistake to make.

Once you see that the error makes total sense, as your essentially concatining all your data into one array item, and therefor you query is looking for 4 items yet you only sent one.

Invalid parameter number

I prefer the named placeholders, makes it easier to keep track of stuff. It quite easy to do, just change these ? to the names like :country for country and then the same in the input array [':country' => 1 ....] etc. It's easer to read then [1,24,5,2 ... bla bla

UPDATE

This strikes me as wrong $purposes_prm= ',1,2,3,4'; this is one item not 4

Yea this wont work

If I set static parameters like:

$rows_pr = sql_pdo_funct($query, array(7,2,5,1,0,0,1999-08-08,1992-08-08,1,2,3,4));

I get correct value as a query result. But if I'm trying to add dynamic values in PHP like:

$status = '7';
$gender = ',2,5';

Those commas and stuff, yea not gonna work like that. The first part is good the part after But if I'm trying that's because it's not even a valid array when added like that.

Upvotes: 1

Sumon Sarker
Sumon Sarker

Reputation: 2795

If you pass the params in function sql_pdo_funct() as like -

$rows_pr = sql_pdo_funct($query, array($status.$gender_one.$gender_two.$location.$gal_prm.$video_prm.$year_old.$purposes ));

Then you can't get from function sql_pdo_funct() like as -

$rows_pr = sql_pdo_funct($query, array(7,2,5,1,0,0,1999-08-08,1992-08-08,1,2,3,4));

You will get like (As a String). Because you concat the String

$rows_pr = sql_pdo_funct($query, array("7,2,5,1,0,0,1999-08-08,1992-08-08,1,2,3,4"));

Note : You should quoted your parameters, If you want to pass the params as String separated.

Update your params like as below. Because you have 12 ? query string in your SQL Query, You should pass 12 params

 $status   = '7';
 $gender   = '2',
 $gender2  = '5';
 $location = '1';
 $gal_prm  = '0';
 $video_prm= '0';
 $year_old = '1999-08-08';
 $year_old2= '1992-08-08';
 $purposes_prm  = '1';
 $purposes_prm1 = '2';
 $purposes_prm2 = '3';
 $purposes_prm3 = '4';

$rows_pr = sql_pdo_funct($query,
   array(
     $status,
     $gender_one,$gender_two,
     $location,
     $gal_prm,
     $video_prm,
     $year_old,
     $year_old2 #Added another birthday params, Because there is 2 birthday conditions
     $purposes_prm,$purposes_prm1,$purposes_prm2,$purposes_prm3 #Added more 3 params, Because there is total 4 params in `IN`
   )
);

Upvotes: 1

Kevin Yan
Kevin Yan

Reputation: 1236

Just replace the static values with corresponding variables. When you use static values in array it have eleven elements , then just simply substitute these static values to variables(don not concatenate these variables)

Upvotes: 0

Related Questions