Let’s discuss how to include the IN operator in WHERE Clause with the SQL SELECT Query through PHP to connect with the MySQL database in the XAMPP Server.
Consider the table - treatments present in the database - hospital snapshot with some records.
Here, there are 4 columns with the following data types:
1. Treatment_ID with int type of length - 2
2. Treatment_Name with varchar type of length - 100
3. Treatment_Doctor with varchar type of length - 100
4. Doctor_Experience with int type of length - 2.
There are 6 records in the treatments table.
Now we will see about the IN operator.
IN in MySQL is used to filter the records by returning the rows provided in the list of values. It is used with WHERE Clause.
We can specify each value separated by comma like - (value1,value2,..............).
WHERE in MySQL is used with SELECT which is used to filter the record inside a table by specifying a condition. Here the condition is specified by using the IN Operator.
SQL IN - Syntax
SELECT columns… from TABLE_NAME WHERE column IN (values);
As we know that columns refers to the column names that exist in the given table (TABLE_NAME).
PHP MySQL - IN Object oriented Connection
We will see how to connect PHP with a MySQL database in XAMPP Server using PHP Script using Object oriented Format to implement the IN operator.
Approach
1.Specify the credentials to create a connection object.
The credentials include XAMPP Server localhost name, username, password and database name.
$connecting_data = new mysqli($server, $user, $pwd, $data);
2.Specify the SQL Query that includes the IN operator.
SELECT columns… from TABLE_NAME WHERE column IN (values);
3.Get the query into the connection object.
$return = $connecting_data->query($my_query);
4.Return the records from the result query.
if ($return->num_rows > 0) {
while($record_data = $return->fetch_assoc()) {
// Display the data
}
} else {
echo "No Data";
}
5.Close the connection object
$connecting_data->close();
Example 1-
Return the records from the Treatment_Doctor and Doctor_Experience columns from treatments table where values in the Doctor_Experience column are (2,8).
<?php
// Specify the server
$server = "localhost";
// Specify the user
$user = "root";
// Specify the password
$pwd = "";
// Specify the database
$data = "hospital";
// Let's create connection by using the above details
$connecting_data = new mysqli($server, $user, $pwd, $data);
// Mention the SQL Query to return the Treatment_Doctor, Doctor_Experience records with Doctor_Experience IN 2,8.
$my_query = "SELECT Treatment_Doctor, Doctor_Experience FROM treatments WHERE Doctor_Experience IN (2,8)";
$return = $connecting_data->query($my_query);
if ($return->num_rows > 0) {
while($record_data = $return->fetch_assoc()) {
echo "Doctor Name: " . $record_data["Treatment_Doctor"]. " and Doctor Experience: " . $record_data["Doctor_Experience"]. "<br>";
}
} else {
echo "No Data";
}
$connecting_data->close();
?>
Output:
Doctor Name: soujanya and Doctor Experience: 2
Doctor Name: varma and Doctor Experience: 8
Doctor Name: ramya sree and Doctor Experience: 8
So the above script returned the Treatment_Doctor, Doctor_Experience records with Doctor_Experience IN 2,8.
Example 2-
Return the records from the Treatment_Doctor and Treatment_ID columns from treatments table where values in the Treatment_ID column are (1,2,4,6).
<?php
// Specify the server
$server = "localhost";
// Specify the user
$user = "root";
// Specify the password
$pwd = "";
// Specify the database
$data = "hospital";
// Let's create connection by using the above details
$connecting_data = new mysqli($server, $user, $pwd, $data);
// Mention the SQL Query to return the Treatment_Doctor, Treatment_ID records with Treatment_ID IN 1,2,4 and 6.
$my_query = "SELECT Treatment_Doctor, Treatment_ID FROM treatments WHERE Treatment_ID IN (1,2,4,6)";
$return = $connecting_data->query($my_query);
if ($return->num_rows > 0) {
while($record_data = $return->fetch_assoc()) {
echo "Doctor Name: " . $record_data["Treatment_Doctor"]. " and Treatment ID: " . $record_data["Treatment_ID"]. "<br>";
}
} else {
echo "No Data";
}
$connecting_data->close();
?>
Output:
Doctor Name: sai kumar and Treatment ID: 1
Doctor Name: sravan kumar and Treatment ID: 2
Doctor Name: soujanya and Treatment ID: 1
Doctor Name: varma and Treatment ID: 4
Doctor Name: ramya sree and Treatment ID: 6
So the above script returned the Treatment_Doctor, Treatment_ID records with Treatment_ID IN 1,2,4 and 6.
Conclusion
So by the end of this article, we saw how to perform PHP-MySQL query with IN operator inside WHERE clause in XAMPP Server by running PHP Script. We discussed two examples to filter the records from the table by providing the values within WHERE Clause using the IN operator.
Would you like to see your article here on tutorialsinhand.
Join
Write4Us program by tutorialsinhand.com
About the Author
Gottumukkala Sravan Kumar 171FA07058
B.Tech (Hon's) - IT from Vignan's University.
Published 1400+ Technical Articles on Python, R, Swift, Java, C#, LISP, PHP - MySQL and Machine Learning
Page Views :
Published Date :
Jun 30,2023