使用php在PDO查询中使用Multiple in（）条件

Question

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!

Answer 1

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) . '))';
}