Consider a scenario where you want to retrieve records of all the patients whose age is 30, 35, or 40. To do so, you can use three WHERE statements with OR operator between them. However there is another more concise and effective approach to achieve these results. You can use IN operator to select records of patients where patient_age is equal to certain values. The syntax of IN operator is as follows:
SELECT column1, column2, column3 FROM table WHERE column1 IN (value1, value2, value3 ....)
Using IN Operator in Object Oriented MySQLi
To select filtered data using IN operator via MySQLi, pass the query for selecting filtered data to the query function of the mysqli object. The following example demonstrates this concept.
Using IN Operator in Object Oriented Mysqli connect_error) { die("Connection not established: " . $connection->connect_error); } $query = "Use Hospital;"; $connection->query($query); // Using IN Operator $query = "SELECT patient_fname, patient_age ". "FROM Patient ". "WHERE patient_age IN (30, 33, 40)"; $output = $connection->query($query); if ($output->num_rows > 0) { // output data of each row while($row = $output->fetch_assoc()) { echo "First Name: " . $row["patient_fname"]." Age: " . $row["patient_age"]."
"; } } else { echo "0 results"; } $connection->close(); ?>
The above code will retrieve values for patient_fname and patient_age column of all the patients whose age is equal to 30, 33 or 40.
Using IN operator in Procedural MySQLi
Selecting filtered table data via IN operator in procedural MySQLi is very simple. Just replace mysqli object with mysqli_connect function. Have a look at the following example.
Using IN Operator in Mysqli connect_error) { die("Connection not established: " . $connection->connect_error); } $query = "Use Hospital;"; $connection->query($query); // Using IN Operator $query = "SELECT patient_fname, patient_age ". "FROM Patient ". "WHERE patient_age IN (23, 35, 42)"; $output = $connection->query($query); if ($output->num_rows > 0) { // output data of each row while($row = $output->fetch_assoc()) { echo "First Name: " . $row["patient_fname"]." Age: " . $row["patient_age"]."
"; } } else { echo "0 results"; } $connection->close(); ?>
The above code will retrieve values for patient_fname and patient_age column of all the patients whose age is equal to 23, 35 or 42.