PHP MySQL - AND operator

Let’s discuss how to include the AND 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 OR operator.

 

AND in MySQL is used to filter the records by returning values based on the conditions specified in WHERE Clause.

If all the specified conditions are true, then the rows matching with the conditions were returned. Otherwise empty is returned.

 

SQL AND - Syntax

SELECT columns… from TABLE_NAME WHERE first_condition AND second_condition AND .......

Here, first_condition and second_condition are the conditions.

 

PHP MySQL - AND 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  AND 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 AND operator.
SELECT columns… from TABLE_NAME WHERE first_condition AND second_condition AND .......;

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 all the columns where the values in the Treatment_ID column greater than 6 AND values in the Doctor_Experience column greater than 10.

<?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 all columns where Treatment_ID > 6 AND Doctor_Experience > 10.
$my_query = "SELECT * FROM treatments WHERE Treatment_ID > 6 AND Doctor_Experience > 10 ";


$return = $connecting_data->query($my_query);

if ($return->num_rows > 0) {

  while($record_data = $return->fetch_assoc()) {

    echo "Treatment ID: " . $record_data["Treatment_ID"]. " , Treatment Name: " . $record_data["Treatment_Name"]. " , Doctor: ". $record_data["Treatment_Doctor"]. ", Experience: " . $record_data["Doctor_Experience"]."<br>";
  }
} else {
  echo "No Data";
}

$connecting_data->close();
?>

Output:

 

So we can see that rows rows are matching with the conditions. So empty is returned.

 

Example 2:

Return the records from all the columns where the values in the Treatment_Name column equals 'cancer' AND Doctor_Experience is greater than 3.

<?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 all columns where Treatment_Name ='cancer' AND Doctor_Experience > 3.
$my_query = "SELECT * FROM treatments WHERE Treatment_Name ='cancer' AND Doctor_Experience > 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"]. " , Treatment Name: " . $record_data["Treatment_Name"]. " , Doctor: ". $record_data["Treatment_Doctor"]. ", Experience: " . $record_data["Doctor_Experience"]."<br>";
  }
} else {
  echo "No Data";
}

$connecting_data->close();
?>

Output:

We can see that there is only one record that macthes both the condition.

 

Example 3:

Return the records from all the columns where the values in the Treatment_Name column equals 'cancer' AND Doctor_Experience is greater than 3.

<?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 all columns where Treatment_Name ='cancer' AND Doctor_Experience > 3 AND Treatment_ID=1.
$my_query = "SELECT * FROM treatments WHERE Treatment_Name ='cancer' AND Doctor_Experience > 3 AND Treatment_ID=1";


$return = $connecting_data->query($my_query);

if ($return->num_rows > 0) {
 
  while($record_data = $return->fetch_assoc()) {

    echo "Treatment ID: " . $record_data["Treatment_ID"]. " , Treatment Name: " . $record_data["Treatment_Name"]. " , Doctor: ". $record_data["Treatment_Doctor"]. ", Experience: " . $record_data["Doctor_Experience"]."<br>";
  }
} else {
  echo "No Data";
}

$connecting_data->close();
?>

Output:

We can see that there is only one record that macthes three conditions.

 

Conclusion

So by the end of this article, we saw how to perform PHP-MySQL query with AND operator inside WHERE clause in XAMPP Server by running PHP Script. We discussed three different examples to filter the records from the table by providing different conditions  within WHERE Clause using the AND operator.