PHP MySQL Where

Filtering the Data

The WHERE clause is used to remove only the documents that meet a set of criteria.

The following is the basic syntax for the WHERE clause:

  SELECT column_name(s) FROM table_name WHERE column_name operator value

Let's start by creating a SQL query with the WHERE clause in the SELECT statement, then running it through the PHP mysqli query() function to get the filtered results.

The PHP code below selects all rows from the people table where first name='john':

<?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();
?>