MySQL WHERE Clause

We know that we can read data from MySQL tables using the SQL SELECT statement.

To select data conditionally from a table, you can add the WHERE clause to the SELECT statement.

Syntax

The following is the general syntax for using the WHERE clause in the SQL SELECT statement to read data from a data table:

SELECT field1, field2,...fieldN FROM table_name1, table_name2...
[WHERE condition1　[AND [OR]] condition2...

In a query statement, you can use one or more tables, separated by commas, and use the WHERE clause to set the query conditions.
You can specify any condition in the WHERE clause.
You can specify one or more conditions using AND or OR.
The WHERE clause can also be used with SQL's DELETE or UPDATE commands.
The WHERE clause is similar to an if condition in programming languages, used to read specified data based on the field values in a MySQL table.

The　following　is　a　list　of　operators　that　can　be　used　in　the　WHERE　clause.

The　following　table　assumes　A　as 10, B　is 20

Operator	Description	Example
=	Equal,　it　detects　if　the　two　values　are　equal,　and　returns　true　if　they　are　equal	(A　=　B)　returns　false.
, !=	Not　equal,　it　detects　if　the　two　values　are　equal,　and　returns　true　if　they　are　not　equal	(A　!=　B)　returns　true.
>	Greater　than　operator,　it　detects　if　the　left　value　is　greater　than　the　right　value,　and　returns　true　if　the　left　value　is　greater　than　the　right　value	(A　>　B)　returns　false.
<	Less　than　operator,　it　detects　if　the　left　value　is　less　than　the　right　value,　and　returns　true　if　the　left　value　is　less　than　the　right　value	(A　<　B)　returns　true.
>=	Greater　than　or　equal　operator,　it　detects　if　the　left　value　is　greater　than　or　equal　to　the　right　value,　and　returns　true　if　the　left　value　is　greater　than　or　equal　to　the　right　value	(A　>=　B)　returns　false.
<=	Less　than　or　equal　operator,　it　detects　if　the　left　value　is　less　than　or　equal　to　the　right　value,　and　returns　true　if　the　left　value　is　less　than　or　equal　to　the　right　value	(A　<=　B)　returns　true.

WHERE　clauses　are　very　useful　if　we　want　to　read　specific　data　from　MySQL　data　tables.

Using　the　primary　key　as　a　condition　in　the　WHERE　clause　is　very　fast.

If　there　are　no　matching　records　in　the　table　for　the　given　condition,　the　query　will　not　return　any　data.

Read　data　from　the　command　prompt

We　will　use　the　WHERE　clause　in　the　SQL　SELECT　statement　to　read　data　from　MySQL　table　w3data　in　codebox_tbl：

Example

The　following　example　will　read　the　w3in　codebox_tbl　table3All　records　with　the　codebox_author　field　value　as　Sanjay：

SQL　SELECT　WHERE　clause

SELECT　*　from　w3codebox_tbl　WHERE　w3codebox_author='Basic Tutorial Website';

Output　result：

MySQL's　WHERE　clause　is　not　case-sensitive　for　string　comparison. You　can　use　the　BINARY　keyword　to　set　the　string　comparison　in　the　WHERE　clause　to　be　case-sensitive.

As　follows　is　an　example:

BINARY　keyword

mysql>　SELECT　*　from　w3codebox_tbl　WHERE　BINARY　w3codebox_author='oldtoolbag.com';
Empty　set　(0.01　sec)
　
mysql>　SELECT　*　from　w3codebox_tbl　WHERE　BINARY　w3codebox_author='oldtoolbag.com';
+----------+-------------+--------------+-----------------+
|　w3codebox_id　|　w3codebox_title　|　w3codebox_author | submission_date |
+----------+-------------+--------------+-----------------+
|　　　　　　　　1　| Learn PHP           | oldtoolbag.com       |　2018-04-14　　　　　　|
|　　　　　　　　2　| Learn MySQL        | oldtoolbag.com       |　2018-04-14　　　　　　|
|　　　　　　　　3　| JAVA Tutorial       | oldtoolbag.com       |　2018-04-14　　　　　　|
+----------+-------------+--------------+-----------------+
3　rows in set (0.00 sec)

The example uses BINARY Keywords are case-sensitive, so w3codebox_author='oldtoolbag.com' The query condition has no data.

Reading data using PHP script

You can use the PHP function mysqli_query() and the same SQL SELECT command with a WHERE clause to retrieve data.

This function is used to execute SQL commands and then output all query data through the PHP function mysqli_fetch_array().

Online example

The following example will retrieve data from w3codebox_tbl table returns using w3codebox_author field value is w3Records of codebox.COM:

MySQL WHERE clause test:

<?php
$dbhost = 'localhost';　　//　MySQL server host address
$dbuser = 'root';　　　　　　　　　　　　//　MySQL username
$dbpass = '123456';　　　　　　　　　　//　MySQL username and password
$conn = mysqli_connect($dbhost, $dbuser, $dbpass);
if (!$conn)
{
　　　　die('Connection failed: ' . mysqli_error($conn));
}
//　Set encoding to prevent Chinese characters from becoming garbled
mysqli_query($conn, "set names utf8);
　
//　Reading w3codebox_author is oldtoolbag.com's data
$sql = 'SELECT w3codebox_id, w3codebox_title,　
　　　　　　　　w3codebox_author, submission_date
　　　　　　　　FROM w3codebox_tbl
　　　　　　　　WHERE w3codebox_author="oldtoolbag.com"';
　
mysqli_select_db($conn, 'w3codebox');
$retval = mysqli_query($conn, $sql);
if(! $retval)
{
　　　　die('Unable to read data: ' . mysqli_error($conn));
}
echo '<h2>Basic Tutorial Network MySQL WHERE Clause Test<h2>';
echo '<table border="1><tr><td> Tutorial ID</td><td>Title</td><td>Author</td><td>Submission Date</td></tr>';
while($row = mysqli_fetch_array($retval, MYSQLI_ASSOC))
{
　　　　echo "<tr><td> {$row['w3codebox_id']}</td> ".
　　　　　　　　　"<td>{$row['w3codebox_title']} </td> ".
　　　　　　　　　"<td>{$row['w3codebox_author']} </td> ".
　　　　　　　　　"<td>{$row['submission_date']} </td> ".
　　　　　　　　　"</tr>";
}
echo '</table>';
//　Free Memory
mysqli_free_result($retval);
mysqli_close($conn);
?>

The output is as follows:

MySQL UPDATE Statement MySQL Query Data

MySql Tutorial