PHP MySQL WHERE Clause

In this tutorial you will learn how to select the records from a MySQL database table based on specific conditions using PHP.

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 query through passing it to the PHP mysqli_query() function to get the filtered data.

Consider we've a persons table inside the demo database that has following records:

+----+------------+-----------+----------------------+
| id | first_name | last_name | email                |
+----+------------+-----------+----------------------+
|  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

Procedural Object Oriented PDO
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>id</th>";
  •                 echo "<th>first_name</th>";
  •                 echo "<th>last_name</th>";
  •                 echo "<th>email</th>";
  •             echo "</tr>";
  •         while($row = mysqli_fetch_array($result)){
  •             echo "<tr>";
  •                 echo "<td>" . $row['id'] . "</td>";
  •                 echo "<td>" . $row['first_name'] . "</td>";
  •                 echo "<td>" . $row['last_name'] . "</td>";
  •                 echo "<td>" . $row['email'] . "</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);
  • ?>
  • <?php
  • /* Attempt MySQL server connection. Assuming you are running MySQL
  • server with default setting (user 'root' with no password) */
  • $mysqli = new mysqli("localhost", "root", "", "demo");
  •  
  • // Check connection
  • if($mysqli === 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($sql)){
  •     if($result->num_rows > 0){
  •         echo "<table>";
  •             echo "<tr>";
  •                 echo "<th>id</th>";
  •                 echo "<th>first_name</th>";
  •                 echo "<th>last_name</th>";
  •                 echo "<th>email</th>";
  •             echo "</tr>";
  •         while($row = $result->fetch_array()){
  •             echo "<tr>";
  •                 echo "<td>" . $row['id'] . "</td>";
  •                 echo "<td>" . $row['first_name'] . "</td>";
  •                 echo "<td>" . $row['last_name'] . "</td>";
  •                 echo "<td>" . $row['email'] . "</td>";
  •             echo "</tr>";
  •         }
  •         echo "</table>";
  •         // Free result set
  •         $result->free();
  •     } else{
  •         echo "No records matching your query were found.";
  •     }
  • } else{
  •     echo "ERROR: Could not able to execute $sql. " . $mysqli->error;
  • }
  •  
  • // Close connection
  • $mysqli->close();
  • ?>
  • <?php
  • /* Attempt MySQL server connection. Assuming you are running MySQL
  • server with default setting (user 'root' with no password) */
  • try{
  •     $pdo = new PDO("mysql:host=localhost;dbname=demo", "root", "");
  •     // Set the PDO error mode to exception
  •     $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
  • } catch(PDOException $e){
  •     die("ERROR: Could not connect. " . $e->getMessage());
  • }
  •  
  • // Attempt select query execution
  • try{
  •     $sql = "SELECT * FROM persons WHERE first_name='john'";  
  •     $result = $pdo->query($sql);
  •     if($result->rowCount() > 0){
  •         echo "<table>";
  •             echo "<tr>";
  •                 echo "<th>id</th>";
  •                 echo "<th>first_name</th>";
  •                 echo "<th>last_name</th>";
  •                 echo "<th>email</th>";
  •             echo "</tr>";
  •         while($row = $result->fetch()){
  •             echo "<tr>";
  •                 echo "<td>" . $row['id'] . "</td>";
  •                 echo "<td>" . $row['first_name'] . "</td>";
  •                 echo "<td>" . $row['last_name'] . "</td>";
  •                 echo "<td>" . $row['email'] . "</td>";
  •             echo "</tr>";
  •         }
  •         echo "</table>";
  •         // Free result set
  •         unset($result);
  •     } else{
  •         echo "No records matching your query were found.";
  •     }
  • } catch(PDOException $e){
  •     die("ERROR: Could not able to execute $sql. " . $e->getMessage());
  • }
  •  
  • // Close connection
  • unset($pdo);
  • ?>

After filtration the result set will look something like this:

+----+------------+-----------+---------------------+
| id | first_name | last_name | email               |
+----+------------+-----------+---------------------+
|  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.