0

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.

Improve this question
4
  • Your function call is attempting to call $purposes instead of $purposes_prm. Can you try correcting that and see if the problem still occurs? Commented Aug 7, 2017 at 23:34
  • 2
    Why are you using dots to separate your variables? You are only sending 1 parameter, a concatenated string. And you need to bind each placeholder individually. Commented Aug 7, 2017 at 23:41
  • With that many parameters, it might be worth your while looking into using named parameters. With named parameters, a line of SQL changes to something like WHERE k.status = :status and the associated array element becomes array(':status' => 7, ...). Having named parameters makes it easy to visually identify which parameter value matches which SQL parameter, rather than pointing to the screen and counting. Commented Aug 8, 2017 at 1:28
  • @jeroen - I tried this way too $rows_pr = sql_pdo_funct($query,array($status,$gender,$location,$gal_prm,$video_prm,$year_old,$purposes )); but the result was the same " Invalid parameter number" Commented Aug 8, 2017 at 8:54

3 Answers 3

Reset to default
1

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`
   )
);
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

1

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.

Improve this answer

3 Comments

I tried with the commas but the result is the same " Invalid parameter number".
var_dump(array($status,$gender,$location,$gal_prm,$video_prm,$year_old,$purposes); array (size=7) 0 => string '7,' (length=2) 1 => string '1,2,4,5,6' (length=9) 2 => string ',1' (length=2) 3 => string ',0' (length=2) 4 => string ',0' (length=2) 5 => string ',1991-08-08,1982-08-08' (length=22) 6 => string ',1,2,3,4' (length=8)
have you tried using named place holders, I find them easier to keep track of things.
0

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)

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.