MySQL where clause
We know that to read the data using SQL SELECT statement from the MySQL table.
To conditionally select data from a table, you can add a WHERE clause to the SELECT statement.
grammar
The following is a SQL SELECT statement with a WHERE clause to read data from the data table general syntax:
SELECT field1, field2,...fieldN FROM table_name1, table_name2... [WHERE condition1 [AND [OR]] condition2.....
- You can query using one or more tables, use a comma between tables (,) segmentation, and use a WHERE clause to set the search criteria.
- You can specify any condition in the WHERE clause.
- You can use AND or OR to specify one or more conditions.
- WHERE clause can also be applied to the SQL DELETE or UPDATE command.
- WHERE clause is similar to programming languages if the conditions, based on field values MySQL table to read specific data.
The following is a list of operators that can be used in the WHERE clause.
A is assumed in the following table as Examples 20 to 10 B
| Operators | description | Examples |
|---|---|---|
| = | Equate to detect whether two values are equal, it returns true if they are equal | (A = B) returns false. |
| <>,! = | Is not equal to detect whether two values are equal, if not equal returns true | (A! = B) returns true. |
| > | Greater-than sign, left detection value is greater than the value of the right to the left if the value is greater than the value of the right of return true | (A> B) returns false. |
| < | No less than the value detected on the left is less than the value of the right to the left if the value is less than the true value of the right of return | (A <B) returns true. |
| > = | Greater than or equal number to detect the left is greater than or equal to the value on the right if the value is greater than or equal to the left of the right of return true | (A> = B) returns false. |
| <= | Less than or equal numbers, check whether the value is less than the left or to the right of equal value, if the value is less than or equal to the left of the right of return true | (A <= B) returns true. |
If we want to MySQL data table reads the specified data, WHERE clause is very useful.
As a condition of using the primary key to the WHERE clause of the query is very fast.
If the conditions are not given any matching records in the table, the query does not return any data.
Reads the data from the command prompt
We will use the WHERE clause in the SQL SELECT statement to read data in MySQL data tables w3big_tbl:
Examples
The following example will read all records w3big_tbl table w3big_author Sanjay field values of:
root@host# mysql -u root -p password; Enter password:******* mysql> use w3big; Database changed mysql> SELECT * from w3big_tbl WHERE w3big_author='Sanjay'; +-------------+----------------+-----------------+-----------------+ | w3big_id | w3big_title | w3big_author | submission_date | +-------------+----------------+-----------------+-----------------+ | 3 | JAVA Tutorial | Sanjay | 2007-05-21 | +-------------+----------------+-----------------+-----------------+ 1 rows in set (0.01 sec) mysql>
MySQL string comparison of the WHERE clause is not case-sensitive. You can use the BINARY keyword to set the WHERE clause string comparisons are case-sensitive.
The following examples
root@host# mysql -u root -p password;
Enter password:*******
mysql> use w3big;
Database changed
mysql> SELECT * from w3big_tbl \
WHERE BINARY w3big_author='sanjay';
Empty set (0.02 sec)
mysql>
Use PHP script to read data
You can use the PHP function mysql_query () and the same SQL SELECT command to bring the WHERE clause to retrieve the data.
This function is used to execute SQL commands, then () to output data for all queries through PHP function mysql_fetch_array.
Examples
The following examples will w3big_tbl table Returns w3big_author field value Sanjay records:
<?php
$dbhost = 'localhost:3036';
$dbuser = 'root';
$dbpass = 'rootpassword';
$conn = mysql_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
die('Could not connect: ' . mysql_error());
}
$sql = 'SELECT w3big_id, w3big_title,
w3big_author, submission_date
FROM w3big_tbl
WHERE w3big_author="Sanjay"';
mysql_select_db('w3big');
$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['w3big_id']} <br> ".
"Title: {$row['w3big_title']} <br> ".
"Author: {$row['w3big_author']} <br> ".
"Submission Date : {$row['submission_date']} <br> ".
"--------------------------------<br>";
}
echo "Fetched data successfully\n";
mysql_close($conn);
?>