dongqieli4164
2019-08-09 14:11
浏览 101
已采纳

使用php在PDO查询中使用Multiple in()条件

I'm using multiple in() condition in WHERE clause of PDO Select query. As a values of in() condition, I am trying to use array, by converting the string into array using explode statement. Like this.,

        $conditions="";

        if($rows['notification_counselor']!='0')
        {
            $counselor=str_repeat('?,', count(explode(",",$rows['notification_counselor'])) - 1) . '?';
            $conditions.=" AND (st.counselorname IN($counselor))";
        }

        if($rows['notification_source']!='0')
        {
            $source=str_repeat('?,', count(explode(",",$rows['notification_source'])) - 1) . '?';
            $conditions.=" AND (st.source IN($source))";
        }

        if($rows['notification_type']!='0')
        {
            $type=str_repeat('?,', count(explode(",",$rows['notification_type'])) - 1) . '?';
            $conditions.=" AND (st.type IN($type))";
        }

        if($rows['notification_program']!='0')
        {   
            $program=str_repeat('?,', count(explode(",",$rows['notification_program'])) - 1) . '?';
            $conditions.=" AND (st.program IN($program))";
        }

In this, $rows['notification_counselor'] is string, so I'm using explode function to change it to array.

But I'm getting this result only:

SELECT st.email FROM tbl_studentrecord st LEFT JOIN tbl_callrecord cr ON st.student_id_pk=cr.student_id_fk WHERE (DATE(st.createddate) >= :fromdate AND DATE(st.createddate) <= :todate) AND (st.counselorname IN(?,?,?))

with this error:

Warning: PDOStatement::execute(): SQLSTATE[HY093]: Invalid parameter number: mixed named and positional parameters

Rest of code is like as follows,

$stmt = $crsmspdo->prepare("SELECT st.email FROM tbl_studentrecord st LEFT JOIN tbl_callrecord cr ON st.student_id_pk=cr.student_id_fk WHERE (DATE(st.createddate) >= :fromdate AND DATE(st.createddate) <= :todate) $conditions GROUP BY cr.student_id_fk");
        $find_data=array('fromdate' =>$rows['notification_fdate'],'todate' =>$rows['notification_tdate']);
        $stmt->execute($find_data);

But, I expect actual results as like this,

SELECT st.email FROM tbl_studentrecord st LEFT JOIN tbl_callrecord cr ON st.student_id_pk=cr.student_id_fk WHERE (DATE(st.createddate) >= :fromdate AND DATE(st.createddate) <= :todate) AND (st.counselorname IN(5,6,7))

I don't know where I making a mistake. Kindly help me to troubleshoot my code error or give any alternate ideas to achieve this..,

Thanks in advance!

  • 写回答
  • 好问题 提建议
  • 关注问题
  • 收藏
  • 邀请回答

1条回答 默认 最新

  • dongsuiying7773 2019-08-09 14:54
    已采纳

    The warning actually told you what's wrong. The SQL string you generated has both the positional parameters (i.e. ?) and named parameters (e.g. :todate, :fromdate). You need to either use one of them in the SQL, not both.

    One way to fix this is to convert your all or ? usage into more sophisticated named parameters.

    For example, this part of your code:

    if($rows['notification_counselor']!='0')
    {
        $counselor=str_repeat('?,', count(explode(",",$rows['notification_counselor'])) - 1) . '?';
        $conditions.=" AND (st.counselorname IN($counselor))";
    }
    

    Can be rewritten like this:

    
    if ($rows['notification_counselor'] != '0') {
        $values = explode(',', $rows['notification_counselor']);
        $placeholders = [];
        foreach ($values as $key => $value) {
            $placeholders[] = ':notification_counselor_' . $key;
            $find_data[':notification_counselor_' . $key] = $value;
        }
        $conditions .= ' AND (st.counselorname IN(' . implode(', ', $placeholders) . '))';
    }
    
    
    已采纳该答案
    评论
    解决 无用
    打赏 举报

相关推荐 更多相似问题