PDO IN clause bindparam for where condition mysql

PDO IN clause bindparam not available, with minor trick we able to prepare mysql IN query bindparam. Using simple loop we can bindparam for PDO IN query.

Let’s look all aspect of bind parameter for mysql IN sql step by step.

MySQL IN clause

Use IN clause to replace many OR conditions.

MySQL IN condition used to reduce the need to use multiple OR conditions. IN condition can be used in a SELECT, INSERT, UPDATE, or DELETE statement.

PDO IN clause bindparam for where condition mysql
PDO IN clause bindparam for where condition mysql

Syntax for IN condition:

expression IN (value1, value2, value2, .... value_n);

Detail for syntax parts:
expression
The value to check in list

Value in syntax
These are the values to check against expression. If any of these values matches expression, then the IN condition will evaluate to true. This is a quick method to test if any one of the values matches expression.

 

PDOStatement::bindParam

Binds a parameter to the specified variable name.

Binds a PHP variable to a corresponding named or question mark placeholder in the SQL statement that was used to prepare the statement. Unlike PDOStatement::bindValue(), the variable is bound as a reference and will only be evaluated at the time that PDOStatement::execute() is called.

Most parameters are input parameters, that is, parameters that are used in a read-only fashion to build up the query. Some drivers support the invocation of stored procedures that return data as output parameters, and some also as input/output parameters that both send in data and are updated to receive it.

Now as per MySQL IN clause information IN clause to replace many OR conditions. but as PDO not provide IN clause bindparam, we need to handle it different way.

 

Example case for use PDO IN clause bindparam

Suppose we have table with below detail.

+------+------+
|  id  | name |
+------+------+
|   1  | John |
|   2  | Ram  |
|   3  | Jack |
|   4  | Jill |
+------+------+

Now we want name values which have id 2 OR 3.

We can get name using below normal sql using IN condition.

SELECT name FROM table_name WHERE id IN(2,3);

 

Solution for PDO IN clause bindparam

First step prepare array of values in which you want to check table field value.

$values = [2, 3];

Second step loop on $values array and prepare sql string part and bindparam with key and value. Here i prepare function for get sql query part and bindparam array.

function getQueryBindParamFromArray($fieldName, $paramArr) {
    $result = ['sqlPart' => '', 'bindParam' => []];
    foreach ($paramArr as $key => $value) {
        $result['sqlPart'] .= ' '.$fieldName.' = :'.$fieldName.'_'.$key.' OR';
        $result['bindParam'][$fieldName.'_'.$key] = $value;
    }
    
    $result['sqlPart'] = trim($result['sqlPart'], ' OR');
    
    return $result;
}

Now use above function in prepare PDO IN clause bindparam process as like below example.

$values = [1, 2];
$arr = getQueryBindParamFromArray('id', $values);

$sql = 'SELECT * FROM table_name WHERE '.$arr['sqlPart'];
$stmt = $conn->prepare($sql);
$stmt->execute($arr['bindParam']);
$result = $stmt->fetchAll(PDO::FETCH_ASSOC);

print_r($result);

Hope you like and found article useful, also don’t forget to share with friends.