Let’s discuss how to include the NOT 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:
-
Treatment_ID with int type of length - 2
-
Treatment_Name with varchar type of length - 100
-
Treatment_Doctor with varchar type of length - 100
-
Doctor_Experience with int type of length - 2.
There are 6 records in the treatments table.
Now we will see about the NOT operator.
NOT in MySQL is used to filter the records by returning values based on the condition specified in WHERE Clause.
If the condition is true, the rows that doesnot match with the condition were returned.
SQL NOT - Syntax
SELECT columns… from TABLE_NAME WHERE NOT condition/s......
PHP MySQL - NOT 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 OR 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 NOT operator.
SELECT columns… from TABLE_NAME WHERE NOT condition/s......
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 Treatment_ID and Treatment_Name Columns with Treatment_ID not in 1 and 3 using IN operator specified with NOT Operator as a condition.
<?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_ID, Treatment_Name records with Treatment_ID not in 1 and 3.
$my_query = "SELECT Treatment_ID, Treatment_Name FROM treatments WHERE Treatment_ID NOT IN (1,3)";
$return = $connecting_data->query($my_query);
if ($return->num_rows > 0) {
while($record_data = $return->fetch_assoc()) {
echo "Treatment ID: " . $record_data["Treatment_ID"]. " and Treatment Name: " . $record_data["Treatment_Name"]. "<br>";
}
} else {
echo "No Data";
}
$connecting_data->close();
?>
Output:
So we can see that records with values in Treatment_Id columns were returned except 1 and 3.
Example 2:
Mention the SQL Query to return the Treatment_ID, Treatment_Name records with Treatment_Name Not equal to 'cancer'.
<?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_ID, Treatment_Name records with Treatment_Name Not equal to 'cancer'
$my_query = "SELECT Treatment_ID, Treatment_Name FROM treatments WHERE Treatment_Name NOT IN (SELECT Treatment_Name FROM treatments
WHERE Treatment_Name = 'cancer')";
$return = $connecting_data->query($my_query);
if ($return->num_rows > 0) {
while($record_data = $return->fetch_assoc()) {
echo "Treatment ID: " . $record_data["Treatment_ID"]. " and Treatment Name: " . $record_data["Treatment_Name"]. "<br>";
}
} else {
echo "No Data";
}
$connecting_data->close();
?>
Output:
So we can see that records with values in Treatment_Name not equal to 'cancer'.
Conclusion
So by the end of this article, we saw how to perform PHP-MySQL query with NOT operator inside WHERE clause in XAMPP Server by running PHP Script. We discussed two different examples to filter the records from the table by providing different conditions within WHERE Clause using the NOT 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 14,2024