PHP MySQL - WHERE clause

Let’s discuss how to apply the 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.

Schema:

 

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.

 

Records:

There are 6 records in the treatments table.

 

Now we will see about the WHERE clause.

 

WHERE in MySQL is used with SELECT which is used to filter the record inside a table by specifying a condition. So It takes a condition and returns the records  that match the condition.

It can be possible to generate a condition using the relational operators like < (less than),>(greater than), <=(less than or equal to), >=(greater than or equal to), ==(equal to), !=(not equal to).

 

SQL WHERE - Syntax

 

SELECT columns… from TABLE_NAME WHERE conditions….

As we know that columns refers to the column names that exist in the given table (TABLE_NAME).

 

PHP MySQL - Object oriented Connection

 

We will see how to connect PHP with a MySQL database in XAMPP Server using PHP Script using Object oriented Format.

 

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 WHERE clause.
$my_query = SELECT columns… from TABLE_NAME WHERE conditions….

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-

<?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 greater than 3 using WHERE clause.
$my_query = "SELECT Treatment_ID, Treatment_Name FROM treatments WHERE Treatment_ID > 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:

Records with Treatment_ID-4 and 6 were returned since they were greater than 3.

 

Example 2-

Return the records from the Treatment_ID and Treatment_Name columns from treatments table WHERE  values in the Treatment_Name  column are equal to ‘cancer’.

O

<?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 - cancer.
$my_query = "SELECT Treatment_ID, 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:

There are two records with Treatment_Name - cancer.

 

Example 3-

Return the records from the Treatment_Doctor and Doctor_Experience columns from treatments table WHERE  values in the Doctor_Experience  column are less 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 the Treatment_Doctor, Doctor_Experience records with Doctor_Experience less than 10 years.
$my_query = "SELECT Treatment_Doctor, Doctor_Experience FROM treatments WHERE Doctor_Experience < 10";


$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:

There are totally 5 records out of 6 that are less than 10 in the Doctor_Experience column.

 

Conclusion

So by the end of this article, we saw how to perform PHP-MySQL query with WHERE clause in XAMPP Server by running PHP Script. We discussed three different examples to filter the records from the table by providing the conditions within WHERE Clause. First we have to create a connection to the XAMPP by specifying the correct credentials. Then we can write SQL Query and fetch the filtered data and finally we have to close the connection using the connection object.