MySQL where clause

MySQL where clause

We know that SQL SELECT statements are used to read data from mySQL tables.

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

Grammar

The following is a common syntax for SQL SELECT statements to read data from a data table using the WHERE clause:

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, which are split with a comma (,) and where statements are used to set query criteria.
• You can specify any condition in the WHERE clause.
• You can use AND or OR to specify one or more conditions.
• The WHERE clause can also be applied to SQL's DELETE or UPDATE commands.
• The WHERE clause is similar to the if condition in the program language, reading the specified data based on the field values in the MySQL table.

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

The example in the following table assumes that A is 10 B is 20

The WHERE clause is useful if we want to read the specified data in the MySQL data table again.

Conditional queries that use primary keys as WHERE clauses are very fast.

If the given condition does not have any matching records in the table, the query does not return any data.

Read the data from the command prompt

We will use theWHERE clause in the SQL SELECT statement to read the data in the MySQL w3cschool_tbl data table:

Instance

The following example reads all w3cschool_tbl in the w3cschool_author the field value of Sanjay in the table:

root@host# mysql -u root -p password;
Enter password:
mysql> use W3CSCHOOL;
Database changed
mysql> SELECT * from w3cschool_tbl WHERE w3cschool_author='Sanjay';
+-------------+----------------+-----------------+-----------------+
| w3cschool_id | w3cschool_title | w3cschool_author | submission_date |
+-------------+----------------+-----------------+-----------------+
|           3 | JAVA Tutorial  | Sanjay          | 2007-05-21      |
+-------------+----------------+-----------------+-----------------+
1 rows in set (0.01 sec)

mysql>

Unless you use LIKE to compare strings, the string comparison for MySQL's WHERE clause is case insensimination. You can use the BINARY keyword to set the case-sensitive string comparison for the WHERE clause.

The following example

root@host# mysql -u root -p password;
Enter password:
mysql> use W3CSCHOOL;
Database changed
mysql> SELECT  from w3cschool_tbl \
          WHERE BINARY w3cschool_author='sanjay';
Empty set (0.02 sec)

mysql>

Read the data using the PHP script

You can use the command of the phAP mysql_query () and the same SQL SELECT with THE clause to get the data.

This function is used to execute SQL commands and then output data for all queries mysql_fetch_array the PHP function, The File ().

Instance

The following instance returns a w3cschool_tbl that uses the w3cschool_author field value Sanjay from the table:

<?php
$dbhost = 'localhost:3036';
$dbuser = 'root';
$dbpass = 'rootpassword';
$conn = mysql_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
  die('Could not connect: ' . mysql_error());
}
$sql = 'SELECT w3cschool_id, w3cschool_title,
               w3cschool_author, submission_date
        FROM w3cschool_tbl
        WHERE w3cschool_author="Sanjay"';

mysql_select_db('W3CSCHOOL');
$retval = mysql_query( $sql, $conn );
if(! $retval )
{
  die('Could not get data: ' . mysql_error());
}
while($row = mysql_fetch_array($retval, MYSQL_ASSOC))
{
    echo "Tutorial ID :{$row['w3cschool_id']}




 ".
         "Title: {$row['w3cschool_title']} 
 ".
         "Author: {$row['w3cschool_author']} 
 ".
         "Submission Date : {$row['submission_date']} 
 ".
         "--------------------------------
";
} 
echo "Fetched data successfully\n";
mysql_close($conn);
?>