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
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:
- Evaluate the
FROMclause to identify the target table. - Proceed to the
WHEREclause to evaluate the criteria for data selection. - Check the
SELECTstatement 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
