MySQL WHERE Clause Tutorial

We have seen the SQL SELECT command to fetch data from a MySQL table. We can use a conditional clause called the WHERE Clause to filter out the results. Using MySQL WHERE clause, we can specify selection criteria to select the required records from a table.

Syntax

The following code block demonstrates how to retrieve data from a MySQL table using the SELECT command and the WHERE clause in a generic SQL statement.

SELECT field1, field2,...fieldN table_name1, table_name2...
[WHERE condition1 [AND [OR]] condition2.....

The WHERE clause is an optional part of the SELECT command, and it allows you to specify one or more tables separated by commas to include multiple conditions.
The WHERE clause allows for the specification of any condition.
The AND and OR operators allow you to specify multiple conditions.
When using the DELETE or UPDATE SQL command, the WHERE clause can also be used to specify a condition.

The WHERE clause in MySQL is analogous to the if statement in other programming languages. The given value is compared to the value of the corresponding field in the MySQL table, using this clause. If the value supplied from outside the database is the same as one of the field values in the relevant MySQL table, the matching row is returned.

Below is a complete list of operators that can be used in conjunction with the WHERE clause:

MySQL WHERE Clause Operators

If we assume that A contains 5 and B contains 10, then:

Operator	Description	Example
=	The condition is determined to be true if and only if the values of the two operands are equal.	(A = B) is not true.
!=	Checks if the two operands are equal; if they are not, the condition is met.	(A != B) is true.
>	Invoked to determine whether the left operand's value is higher than the right operand's, making the condition true if so.	(A > B) is not true.
<	Conditionally evaluates to true if left operand is less than right operand.	(A < B) is true.
>=	Compares the left operand's value to the right operand's value to see if it's greater than or equal to the right one; if so, the condition is met.	(A >= B) is not true.
<=	Identifies whether the left operand's value is less than or equal to the right operand's value, making the condition true if so.	(A <= B) is true.

When using MySQL Join, the WHERE clause is invaluable for retrieving only the desired rows from a table. There's a separate chapter for the joins.

In order to speed up a search, it is common practise to use the Primary Key as the search criteria.

If no rows in the table satisfy the condition, the query will return no results.

MySQL WHERE Clause Examples

Obtaining Information from the Command Prompt

This will retrieve the desired information from the MySQL table via the SELECT command's WHERE clause:

Example

The following example will return all the records from the course_tbl table for which the author name is Shibu.

root@host# mysql -u root -p password;
Enter password:*******
mysql> use COURSES;
Database changed
mysql> SELECT * from course_tbl WHERE course_author = 'Shibu';
+-------------+----------------+-----------------+-----------------+
| course_id | course_title | course_author | submission_date |
+-------------+----------------+-----------------+-----------------+
|      3      | JAVA Tutorial  |      Shibu     |    2022-02-11   |      
+-------------+----------------+-----------------+-----------------+
1 rows in set (0.01 sec)

mysql>

Except when using the LIKE operator, case is ignored when comparing strings. This is how to make your search case-sensitive when using the BINARY keyword:

root@host# mysql -u root -p password;
Enter password:*******
mysql> use COURSES;
Database changed
mysql> SELECT * from course_tbl \
   WHERE BINARY course_author = 'shibu';
Empty set (0.02 sec)

mysql>

Using PHP to Get Information

To retrieve data from a MySQL table according to a where clause, PHP makes use of the mysqli query() or mysql query() function. This method expects two values as input and returns true if successful, or false otherwise.

Syntax

$mysqli->query($sql,$resultmode)

Sr.No.	Parameter & Description
1	$sqlRequired - SQL query to select records in a MySQL table using Where Clause.
2	$resultmodeOptional - Either the constant MYSQLI_USE_RESULT or MYSQLI_STORE_RESULT depending on the desired behavior. By default, MYSQLI_STORE_RESULT is used.

Example

Here's a sample of how to use a where clause to select a specific record from a table:

To use this example, simply rename the file below to mysql-example.php:

<html>
   <head>
      <title>Using MySQL Where Clause</title>
   </head>
   <body>
      <?php
         $dbhost = 'localhost';
         $dbuser = 'root';
         $dbpass = 'root@123';
         $dbname = 'COURSES';
         $mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);
         
         if($mysqli->connect_errno ) {
            printf("Connect failed: %s<br />", $mysqli->connect_error);
            exit();
         }
         printf('Connected successfully.<br />');
   
         $sql = 'SELECT course_id, course_title, course_author, submission_date 
            FROM course_tbl where course_author = "Vignesh"';
		 
         $result = $mysqli->query($sql);
           
         if ($result->num_rows > 0) {
            while($row = $result->fetch_assoc()) {
               printf("Id: %s, Title: %s, Author: %s, Date: %d <br />", 
                  $row["tutorial_id"], 
                  $row["tutorial_title"], 
                  $row["tutorial_author"],
                  $row["submission_date"]);               
            }
         } else {
            printf('No record found.<br />');
         }
         mysqli_free_result($result);
         $mysqli->close();
      ?>
   </body>
</html>

Output

Check the output by loading the mysql-example.php file from the Apache web server. In this case, we have populated the table with multiple rows before executing the select script.

Connected successfully.
Id: 1, Title: MySQL Tutorial, Author: Vignesh, Date: 2022
Id: 2, Title: CSS Tutorial, Author: Vignesh, Date: 2022
Id: 3, Title: PERL Tutorial, Author: Vignesh, Date: 2022

In this tutorial, you have learned how to use the MySQL WHERE clause in the SELECT statement to retrieve the data conditionally.