PHP MySQL WHERE Clause

A WHERE clause filters the records according to the condition in the clause.

Filtering the Records

The WHERE clause is used to extract only those records that fulfill a specified condition.

The basic syntax of the WHERE clause can be given with:

SELECT column_name(s) FROM table_name WHERE column_name operator value

Let's make a SQL query using the WHERE clause in SELECT statement, after that we'll execute this SQL query through passing it to the mysqli_query() function to get the filtered data.

Consider the following "persons" table inside the "demo" database:

+-----------+------------+-----------+----------------------+
| person_id | first_name | last_name | email_address        |
+-----------+------------+-----------+----------------------+
|         1 | Peter      | Parker    | peterparker@mail.com |
|         2 | John       | Rambo     | johnrambo@mail.com   |
|         3 | Clark      | Kent      | clarkkent@mail.com   |
|         4 | John       | Carter    | johncarter@mail.com  |
|         5 | Harry      | Potter    | harrypotter@mail.com |
+-----------+------------+-----------+----------------------+

The following PHP code selects all the rows from the persons table where first_name='john':

Example

Download
  • <?php
  • /* Attempt MySQL server connection. Assuming you are running MySQL
  • server with default setting (user 'root' with no password) */
  • $link = mysqli_connect("localhost", "root", "", "demo");
  •  
  • // Check connection
  • if($link === false){
  •     die("ERROR: Could not connect. " . mysqli_connect_error());
  • }
  •  
  • // Attempt select query execution
  • $sql = "SELECT * FROM persons WHERE first_name='john'";
  • if($result = mysqli_query($link, $sql)){
  •     if(mysqli_num_rows($result) > 0){
  •         echo "<table>";
  •             echo "<tr>";
  •                 echo "<th>person_id</th>";
  •                 echo "<th>first_name</th>";
  •                 echo "<th>last_name</th>";
  •                 echo "<th>email_address</th>";
  •             echo "</tr>";
  •         while($row = mysqli_fetch_array($result)){
  •             echo "<tr>";
  •                 echo "<td>" . $row['person_id'] . "</td>";
  •                 echo "<td>" . $row['first_name'] . "</td>";
  •                 echo "<td>" . $row['last_name'] . "</td>";
  •                 echo "<td>" . $row['email_address'] . "</td>";
  •             echo "</tr>";
  •         }
  •         echo "</table>";
  •         // Close result set
  •         mysqli_free_result($result);
  •     } else{
  •         echo "No records matching your query were found.";
  •     }
  • } else{
  •     echo "ERROR: Could not able to execute $sql. " . mysqli_error($link);
  • }
  •  
  • // Close connection
  • mysqli_close($link);
  • ?>

After filtration the result set will look something like this:

+-----------+------------+-----------+---------------------+
| person_id | first_name | last_name | email_address       |
+-----------+------------+-----------+---------------------+
|         2 | John       | Rambo     | johnrambo@mail.com  |
|         4 | John       | Carter    | johncarter@mail.com |
+-----------+------------+-----------+---------------------+
 
Close

Your Feedback:

 

We would love to hear from you! Please say something.