MySQL - WHERE Clause MYSQL
- MySQL Introduction
- MySQL - Installation
- MySQL - Administration
- MySQL - PHP Syntax
- MySQL - Connection
- MySQL - Create Database
- Drop MySQL Database
- Selecting MySQL Database
- MySQL - Data Types
- Create MySQL Tables
- Drop MySQL Tables
- MySQL - Insert Query
- MySQL - Select Query
- MySQL - WHERE Clause
- MySQL - UPDATE Query
- MySQL - DELETE Query
- MySQL - LIKE Clause
- MySQL - Sorting Results
- Using MySQl Joins
- MySQL - Regexps
- MySQL - Transactions
- MySQL - ALTER Command
- MySQL - INDEXES
- MySQL - Temporary Tables
- MySQL - Clone Tables
- MySQL - Database Info
- Using MySQL Sequences
- MySQL - Handling Duplicates
- MySQL - and SQL Injection
- MySQL - Database Export
MySQL - WHERE Clause
MySQL - WHERE Clause
We can use a conditional clause called the WHERE Clause to filter out the results. Using this WHERE clause, we can specify a selection criteria to select the required records from a table.
Syntax
The following code block has a generic SQL syntax of the SELECT command with the WHERE clause to fetch data from the MySQL table −
SELECT field1, field2,...fieldN table_name1, table_name2... [WHERE condition1 [AND [OR]] condition2.....
-
You can use one or more tables separated by a comma to include various conditions using a WHERE clause, but the WHERE clause is an optional part of the SELECT command.
-
You can specify any condition using the WHERE clause.
-
You can specify more than one condition using the AND or the OR operators.
-
A WHERE clause can be used along with DELETE or UPDATE SQL command also to specify a condition.
The WHERE clause works like an if condition in any programming language. This clause is used to compare the given value with the field value available in a MySQL table. If the given value from outside is equal to the available field value in the MySQL table, then it returns that row.
Here is the list of operators, which can be used with the WHERE clause.
Assume field A holds 10 and field B holds 20, then −
Operator | Description | Example |
---|---|---|
= | Checks if the values of the two operands are equal or not, if yes, then the condition becomes true. | (A = B) is not true. |
!= | Checks if the values of the two operands are equal or not, if the values are not equal then the condition becomes true. | (A != B) is true. |
> | Checks if the value of the left operand is greater than the value of the right operand, if yes, then the condition becomes true. | (A > B) is not true. |
< | Checks if the value of the left operand is less than the value of the right operand, if yes then the condition becomes true. | (A < B) is true. |
>= | Checks if the value of the left operand is greater than or equal to the value of the right operand, if yes, then the condition becomes true. | (A >= B) is not true. |
<= | Checks if the value of the left operand is less than or equal to the value of the right operand, if yes, then the condition becomes true. | (A <= B) is true. |
The WHERE clause is very useful when you want to fetch the selected rows from a table, especially when you use the MySQL Join. If the given condition does not match any record in the table, then the query would not return any row.
Fetching Data Using a PHP Script
You can use the same SQL SELECT command with the WHERE CLAUSE into the PHP function mysql_query(). This function is used to execute the SQL command and later another PHP function mysql_fetch_array() can be used to fetch all the selected data. This function returns a row as an associative array, a numeric array, or both. This function returns FALSE if there are no more rows.
Example
The following example will return all the records from the tutorials_tbl table for which the author name is Deepak:
<?php $dbhost = 'localhost:3036'; $dbuser = 'root'; $dbpass = 'rootpassword'; $conn = mysql_connect($dbhost, $dbuser, $dbpass); if(! $conn ) { die('Could not connect: ' . mysql_error()); } $sql = 'SELECT tutorial_id, tutorial_title, tutorial_author, submission_date FROM tutorials_tbl WHERE tutorial_author = "Deepak"'; mysql_select_db('TUTORIALS'); $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['tutorial_id']} <br> ". "Title: {$row['tutorial_title']} <br> ". "Author: {$row['tutorial_author']} <br> ". "Submission Date : {$row['submission_date']} <br> ". "--------------------------------<br>"; } echo "Fetched data successfully\n"; mysql_close($conn); ?>