❮ Mysql Update Query Mysql Database Export ❯

MySQL WHERE Clause

We know that we can use the SQL SELECT statement to read data from a MySQL table.

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

Syntax

The following is the general syntax for using the WHERE clause with 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.....

You can use one or multiple tables in the query statement, separated by commas ,, and use the WHERE statement to set the query conditions.
You can specify any condition in the WHERE clause.
You can use AND or OR to specify one or multiple conditions.
The WHERE clause can also be applied to SQL DELETE or UPDATE commands.
The WHERE clause is similar to the if condition in programming languages, reading specified data based on field values in the MySQL table.

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

The examples in the table assume A is 10 and B is 20.

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

If we want to read specified data from a MySQL data table, the WHERE clause is very useful.

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

If the given condition does not match any record in the table, the query will not return any data.

Reading Data from Command Prompt

We will use the WHERE clause in the SQL SELECT statement to read data from the MySQL data table tutorialpro_tbl:

Example

The following example will read all records from the tutorialpro_tbl table where the tutorialpro_author field is 'Sanjay':

SQL SELECT WHERE Clause

SELECT * from tutorialpro_tbl WHERE tutorialpro_author='tutorialpro.org';

Output:

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

Example with BINARY Keyword

mysql> SELECT * from tutorialpro_tbl WHERE BINARY tutorialpro_author='tutorialpro.org';
Empty set (0.01 sec)

mysql> SELECT * from tutorialpro_tbl WHERE BINARY tutorialpro_author='tutorialpro.org';
+-----------+---------------+---------------+-----------------+
| tutorialpro_id | tutorialpro_title  | tutorialpro_author | submission_date |
+-----------+---------------+---------------+-----------------+
| 3         | JAVA Tutorial   | tutorialpro.org    | 2016-05-06      |
| 4         | Learn Python | tutorialpro.org    | 2016-03-06      |
+-----------+---------------+---------------+-----------------+
2 rows in set (0.01 sec)

The example uses the BINARY keyword, which is case-sensitive, so the query condition tutorialpro_author='tutorialpro.org' does not return any data.

Reading Data with PHP Script

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

This function is used to execute the SQL command, and then output all queried data using the PHP function mysqli_fetch_array().

Example

The following example will return records from the tutorialpro_tbl table where the tutorialpro_author field is 'tutorialpro.org':

MySQL WHERE Clause Test:

<?php
$dbhost = 'localhost';  // mysql server host address
$dbuser = 'root';       // mysql username
$dbpass = '123456';     // mysql user password
$conn = mysqli_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
    die('Connection failed: ' . mysqli_error($conn));
}
// Set encoding to prevent Chinese乱码
mysqli_query($conn , "set names utf8");

// Read data where tutorialpro_author is tutorialpro.org
$sql = 'SELECT tutorialpro_id, tutorialpro_title, 
        tutorialpro_author, submission_date
        FROM tutorialpro_tbl
        WHERE tutorialpro_author="tutorialpro.org"';

mysqli_select_db( $conn, 'tutorialpro' );
$retval = mysqli_query( $conn, $sql );
if(! $retval )
{
    die('Could not read data: ' . mysqli_error($conn));
}
echo '<h2>tutorialpro.org 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['tutorialpro_id']}</td> ".
         "<td>{$row['tutorialpro_title']} </td> ".
         "<td>{$row['tutorialpro_author']} </td> ".
         "<td>{$row['submission_date']} </td> ".
         "</tr>";
}
echo '</table>';
// Free memory
mysqli_free_result($retval);
mysqli_close($conn);
?>

Output is as follows:

❮ Mysql Update Query Mysql Database Export ❯