Where Clause Example

PHP uses mysqli query() or mysql_query() function to select records in a MySQL table using where clause. This function takes two parameters and returns TRUE on success or FALSE on failure.

Syntax

$mysqli->query($sql,$resultmode)
Sr.No.Parameter & Description
1

$sql

Required – SQL query to select records in a MySQL table using Where Clause.

2

$resultmode

Optional – Either the constant MYSQLI_USE_RESULT or MYSQLI_STORE_RESULT depending on the desired behavior. By default, MYSQLI_STORE_RESULT is used.

Example

Try the following example to select a record using where clause in a table −

Copy and paste the following example as mysql_example.php −

<html>
   <head>
      <title>Using Where Clause</title>
   </head>
   <body>
      <?php
         $dbhost = 'localhost';
         $dbuser = 'root';
         $dbpass = '[email protected]';
         $dbname = 'TUTORIALS';
         $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 tutorial_id, tutorial_title, tutorial_author, submission_date FROM tutorials_tbl where tutorial_author = "Mahesh"';
         $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

Access the mysql_example.php deployed on apache web server and verify the output. Here we’ve entered multiple records in the table before running the select script.

Connected successfully.
Id: 1, Title: MySQL Tutorial, Author: Mahesh, Date: 2021
Id: 2, Title: HTML Tutorial, Author: Mahesh, Date: 2021
Id: 3, Title: PHP Tutorial, Author: Mahesh, Date: 2021
You can use one or more tables separated by a comma to include various conditions using a WHERE clause, but the WHERE clause is an optional part of the SELECT command. You can specify any condition using the WHERE clause. You can specify more than one condition using the AND or the OR operators.

is the standard statement defining which columns and which table we target. The WHERE keyword identifies the data selection criteria. There can be a single value, the range between values, the subquery, and more than one condition.

Automate your work with query criteria. See and visually edit them on the separate tabs of our visual MySQL query tool where you’re drawing your query. You don’t need to type the columns’ names or remember complex rules. Just select the required data from the lists and enjoy the results.

Using WHERE in MySQL

MySQL works in the following way when executing the SELECT statement containing the WHERE clause:

  1. Evaluate the FROM clause to identify the target table.
  2. Proceed to the WHERE clause to evaluate the criteria for data selection.
  3. Check the SELECT statement to define the columns to retrieve. 

Note: The SELECT statement often includes the ORDER BY clause that puts the results in ascending or descending order. If so, MySQL will evaluate it in the end.

The principle of work of MySQL WHERE is similar to the IF condition in programming languages. It compares the values we provide with those of the target MySQL table. When these values match, the RDBMS fetches those records to the users. 

When the condition for the WHERE clause is a text value, we put that text into single quotes. However, no quotes are necessary for the numeric values.

SELECT first_name, last_name, city 
FROM Readers
WHERE City = Seattle’;

SELECT * 
FROM Readers
WHERE CustomerID = 24;

Level-up your scripting abilities with the advanced MySQL formatter that allows beautifying your code to get it exactly as you like it.

Now, let us review the usage of the WHERE clause. It involves plenty of specific cases. The best way to explore them is to refer to a practical MySQL WHERE example for each scenario.

WHERE query single condition example

The simplest scenario of using this clause is MySQL WHERE having one single criterion for the data selection. Assume that some company wants to retrieve the list of its employees working in the Customer Care department as support managers:

SELECT 
    last_name, 
    first_name, 
    job_title