How to use php array with sql IN operator?

63 Views

How to use php array with sql IN operator?

SQL | IN operator with PHP Array

In this article we will use PHP Array with SQL IN operator. SQL IN operator allows us to specify multiple values in where clause. So in this article we will see how do we directly use PHP Array with SQL In operator.

This is a basic syntax of SQL IN operator

SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1, value2, ...);

Numeric Array

So, let's create an array $arr.

 <?php

// numeric array
$arr = array(2,4,5,7,9);

print_r($arr);

// output
// Array ( [0] => 2 [1] => 4 [2] => 5 [3] => 7 [4] => 9 )

?>

For using this array in sql statement with IN operation we need to use PHP implode function with comma "," delimiter.

implode() function:

The implode() function returns a string from elements of an array using any delimiter.

 

Here's the SQL statement using php array and SQL IN Operator

 <?php

$sql = "SELECT * FROM table WHERE comp_id IN (".implode(',',$arr).")";

echo $sql;

// The output is a valid SQL statement.
// SELECT * FROM table WHERE comp_id IN (2,4,5,7,9)

?>

 


String Array

Now let's check with an array having string data.

 <?php

// string array
$arr = array('Apple','Orange','Cherry','Strawberry','Mango');

print_r($arr);

// output
// Array ( [0] => Apple [1] => Orange [2] => Cherry [3] => Strawberry [4] => Mango )

?>

For array having string data we need to cover the string with inverted comma something like: 'apple'.

 <?php

echo $sql = "SELECT * FROM table WHERE comp_id IN ('".implode("','",$arr)."')";

// The output will be a valid SQL statement
// SELECT * FROM table WHERE comp_id IN ('Apple','Orange','Cherry','Strawberry','Mango')

?>

So by this way we can directly use PHP Array in SQL IN operation.