MySQLi - WHERE Clause

The MySQL WHERE clause is used to specify conditions in a query. It can be used to specify conditions while fetching data from a table, joining two tables, updating records in a table, inserting records in a table or deleting records from a table.

The syntax for using WHERE Clause is given below:

SELECT column1, column2, ...
FROM table_name
WHERE conditions;

To specify condition in a query, MySQL comparison or logical operators like <, >, =, LIKE, IN, NOT, NULL etc. are used.

Along with this, to connect to the MySQL server, mysqli_connect() function can be used. After establishing the connection, mysqli_query() function can be used to perform a query on the database.

The num_rows() function can be used to check if there are more than zero rows returned. Then, the fetch_assoc() function can be used to fetch the result set as an associative array. Later on the free_result() function can be used to free the memory associated with the result.

Select and Filter Data - Object-oriented style

Consider a database containing a table called Employee with the following records:

EmpID	Name	City	Age	Salary
1	John	London	25	3000
2	Marry	New York	24	2750
3	Jo	Paris	27	2800
4	Kim	Amsterdam	30	3100
5	Ramesh	New Delhi	28	3000
6	Huang	Beijing	28	2800

The example below demonstrates how to use the WHERE clause to select and filter data from this table using object-oriented style.

<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDatabase";

//establishing connection 
$mysqli = new mysqli($servername, $username, $password, $dbname);

//checking connection
if ($mysqli->connect_errno) {
  echo "Failed to connect to MySQL: ". $mysqli->connect_error;
  exit();
}

//getting query result from the database
$sql = "SELECT Name, Age, Salary 
FROM Employee 
WHERE Salary > 2800";
$result = $mysqli->query($sql);

//fetching associative array
while ($row = $result->fetch_assoc()) {
  printf("%s, %d, %d\n", $row["Name"], $row["Age"], $row["Salary"]);
}

//free result set
$result->free_result();

//closing the connection
$mysqli->close();
?>

The output of the above code will be:

John, 25, 3000
Kim, 30, 3100
Ramesh, 28, 3000

Select and Filter Data - Procedural style

To obtain the same result using procedural style, the following script can be used.

<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDatabase";

//establishing connection 
$mysqli = mysqli_connect($servername, $username, $password, $dbname);

//checking connection
if (mysqli_connect_errno()) {
  echo "Failed to connect to MySQL: ". mysqli_connect_error();
  exit();
}

//getting query result from the database
$sql = "SELECT Name, Age, Salary 
FROM Employee 
WHERE Salary > 2800";
$result = mysqli_query($mysqli, $sql);

//fetching associative array
while ($row = mysqli_fetch_assoc($result)) {
  printf("%s, %d, %d\n", $row["Name"], $row["Age"], $row["Salary"]);
}

//free result set
mysqli_free_result($result);

//closing the connection
mysqli_close($mysqli);
?>

The output of the above code will be:

John, 25, 3000
Kim, 30, 3100
Ramesh, 28, 3000

Complete MySQLi Reference

For a complete reference of all properties, methods and functions of PHP MySQLi extension, see MySQLi Reference.