PHP PDO 查询，参数位于数组中

Question

我在设置 PDO 查询时遇到问题。我的 PDO 查询如下所示:

$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(?,?,?,?) ";

执行此查询的函数:

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

如果我设置静态参数，例如:

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

我得到正确的值作为查询结果。但如果我尝试在 PHP 中添加动态值，例如:

 $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';

并将其放入 sql_pdo_funct 函数中，我收到错误消息:

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

函数调用:

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

为什么会出现这个错误？我做错了什么以及如何做到这一点？

感谢您的帮助和建议。

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

注意:如果您想将参数作为字符串分隔传递，您应该引用您的参数。

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