Open In App

How to bind an array to an IN() condition in PHP ?

Improve
Improve
Like Article
Like
Save
Share
Report

Given a database containing some data and the task is to bind a PHP array to it by using MySQL IN() operator. Here we will look at a program that does the needful by taking a PHP array within the query and interpreting it.

SQL IN() Operator: The SQL IN() operator allows you to pass multiple arguments in the where clause. It is often used as the shorthand for multiple OR statements.

Syntax:

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

PHP implode() function: We will be using PHP predefined function implode() to deal with the array. The implode() function joins the array elements with a string. It requires two parameters to carry out the same. First is the string that will be used to join the array elements that can also be referred to as a glue string. Since it will be used to glue the elements together. Second is obviously the array on which the operation is required to be performed.

Syntax:

string implode(separator, array)

Array Binding: As per our need, we simply need to bind the PHP array to IN() clause and to obtain this functionality, we first need to convert the given array to the form acceptable by the IN() clause, which is a job carried out by PHP implode() function. Also, keep in mind that by passing our array to the function, we are just converting the array in the form acceptable by IN() but it still is the same array.

The following program will help you understand the concept practically and more carefully:

Used DataBase: Here, we will use a database (database name: Geeks) to bind an array to an IN() condition in PHP.

Table name: GFG The table name GFG contains the following information.

ID NAME
1 MEGAN
2 GINA
3 DARVY
4 DEBBY
5 MICHEL
6 RACHEL
7 EVA
8 SAM
9 ZACK
10 TIM

Program 1: The following code takes PHP array of IDs from the GFG table and passes it to IN() operator to retrieve names, corresponding to given IDs.




<?php  
  
// Connecting to the server
$server = 'localhost';
$username = 'root';
$password = '';
$dbname = 'Geeks';
   
// Declare and initialize an
// array that need to be
// passed to IN() operator
$arr = array(1, 2, 3, 4, 5);
   
// Joining array elements using
// implode() function
$ids = implode(', ', $arr);
   
$conn = new mysqli($server
    $username, $password, $dbname);
   
// SQL query to pass the array 
$sql = ('SELECT NAME FROM GFG 
        WHERE ID IN ('.$ids.')' );
   
$result = $conn->query($sql);
   
if($result->num_rows > 0) {
    while($row = $result->fetch_assoc()) {
        echo $row['NAME'] . "<br>"; }
else {
    echo "0 results";
}
   
$conn->close();
   
?>


Output:

MEGAN
GINA
DARVY
DEBBY
MICHEL

Program 2: The following code uses the same information table but retrieves ID corresponding to the names given in the array.




<?php
   
// Connecting to server
$server = 'localhost';
$username = 'root';
$password = '';
$dbname = 'Geeks';
   
// Declare and initialize an
// array that need to be
// passed to IN() operator
$arr = array("MEGAN", "TIM");
   
// Joining array elements using
// implode() function
$names = implode('\', \'', $arr);
   
// Since array elements to be
// searched, here are string
// making it look like a string
// so that sql can easily
// interpret it
$fin = "'" . $names . "'";
   
$conn = new mysqli($server
    $username, $password, $dbname);
   
// SQL query to pass the array 
$sql=('SELECT ID FROM GFG
        WHERE NAME IN ('.$fin.')' );
   
$result = $conn->query($sql);
   
if($result->num_rows > 0)  {
    while($row = $result->fetch_assoc()){
        echo $row['ID'] . "<br>"
    }
} else {
    echo "0 results";
}
   
$conn->close();
   
?>


Output:

1
10


Last Updated : 20 May, 2020
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads