MySQL – WHERE Clause in MySQL
The Where clause is used to specify the condition while fetching the specific records from MySQL Database. In other words, The Where clause is used to fetch only those records that fulfill a specified condition. If the condition is satisfied then only records will be displayed.
Syntex
|
1 2 3 4 5 6 7 |
<em><span class="has-inline-color has-accent-color">SELECT [field1, field2, field3....fieldn] FROM tableName WHERE condition</span></em><span class="has-inline-color has-accent-color">;</span> <strong>OR</strong> <span class="has-inline-color has-accent-color"><em>SELECT * FROM tableName WHERE condition</em>;</span> |
The WHERE clause is not only used in SELECT Query but also used in INSERT, UPDATE, and DELETE Query.
The WHERE clause plays an important role for a large amount data.
Expand Requery Edit Explain Profiling Bookmark Database Database: tutorialdemo
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
Table Name: members +----+----------+---------+--------------------------+---------+ | id | firstname| lastname| address | salary | +---------------+---------+--------------------------+---------+ | 1 | Rahul | Singh | Flat No 30, Allahabad | 2000 | | 2 | Ram | Charan | Flat No 302, Allahabad | 5000 | | 3 | Anil | Kumar | Flat No 301, Allahabad | 3000 | | 4 | Sunil | Kumar | Flat No 303, Allahabad | 8000 | +-------------+-----------+--------------------------+---------+ Simple Select Query: ---------------------------------------------------------------- SELECT * FROM `members` WHERE (`members`.`id` = 2) OR (`members`.`id` = 3); Output: +----+----------+---------+--------------------------+---------+ | id | firstname| lastname| address | salary | +---------------+---------+--------------------------+---------+ | 2 | Ram | Charan | Flat No 302, Allahabad | 5000 | | 3 | Anil | Kumar | Flat No 301, Allahabad | 3000 | +-------------+-----------+--------------------------+---------+ |
Comparison or Logical operators in the WHERE Clause in MySQL
| Operator | Description | Example |
| = | Equal, check with the two operands are equal or not | (A=B) |
| > | Greater than | (A>B) |
| < | Less than | (A<B) |
| <= | Less than or equal | (A<=B) |
| >= | Greater than or equal | (A>=B) |
| <> or != | Not equal | (A<>B)(A!=B) |
| BETWEEN | Between is used for certain date range | Between ’01-Dec-2020′ and ’12-Dec-2020′ |
| LIKE | Like used for search the specifc keword | LIKE ‘%HTML%’ |
| IN | Retrive the multiple records | IN (‘2′,’3’) |
WHERE Clause in MySQL with Logical operators
Example :
The following example query would fetch the id, firstname, lastname, and feeamount fields from members table where feeamount is greater the 3000.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
Table Name: members +----+----------+---------+--------------------------+-----------+ | id | firstname| lastname| address | feeamount | +---------------+---------+--------------------------+-----------+ | 1 | Rahul | Singh | Flat No 30, Allahabad | 3000 | | 2 | Ram | Charan | Flat No 302, Allahabad | 5000 | | 3 | Anil | Kumar | Flat No 301, Allahabad | 3000 | | 4 | Sunil | Kumar | Flat No 303, Allahabad | 2000 | +-------------+-----------+--------------------------+-----------+ <strong>Select Query with Logical operators</strong> SELECT id, firstname, lastname,feeamount FROM members WHERE feeamount > 3000 |
|
1 2 3 4 5 6 7 8 9 |
<strong>Output</strong>: +----+----------+---------+-----------+ | id | firstname| lastname| feeamount | +---------------+---------+-----------+ | 1 | Rahul | Singh | 3000 | | 2 | Ram | Charan | 5000 | | 3 | Anil | Kumar | 3000 | | 4 | Sunil | Kumar | 2000 | +-------------+-----------+-----------+ |
Example 2:
The following SELECT statement would fetch the specific records from the members table using id.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
Table Name : members +----+----------+---------+--------------------------+-----------+ | id | firstname| lastname| address | feeamount | +---------------+---------+--------------------------+-----------+ | 1 | Rahul | Singh | Flat No 30, Allahabad | 3000 | | 2 | Ram | Charan | Flat No 302, Allahabad | 5000 | | 3 | Anil | Kumar | Flat No 301, Allahabad | 3000 | | 4 | Sunil | Kumar | Flat No 303, Allahabad | 2000 | +-------------+-----------+--------------------------+-----------+ <strong>Select Query with Logical operators</strong> SELECT id, firstname, lastname,feeamount FROM members WHERE id="2"; +----+----------+---------+-----------+ | id | firstname| lastname| feeamount | +---------------+---------+-----------+ | 2 | Ram | Charan | 5000 | +-------------+-----------+-----------+ |
The PHP code
The following PHP code will display the id, firstname, lastname, address and feeamount from the members table.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 |
<?php $hostname = "localhost"; $username = "root"; $password = ""; $dbname = "tutorialdemo"; // Create database connection $connection = mysqli_connect( $hostname, $username, $password, $dbname ); // Check database connection. if DB connection not establish print error if ( !$connection ) { die( "Database not connected: " . mysqli_connect_error() ); } $query = "SELECT id, firstname, lastname,address,feeamount FROM members WHERE feeamount > 3000"; $result = mysqli_query( $connection, $query ); if ( mysqli_num_rows( $result ) > 0 ) { ?> <table border="1"> <thead> <tr> <th>Id</th> <th>First Name</th> <th>Last Name</th> <th>Address</th> <th>Fee Amount</th> </tr> </thead> <tbody> <?php while($rows = mysqli_fetch_assoc($result)) { ?> <tr> <td><?php echo $rows['id']; ?></td> <td><?php echo $rows['firstname']; ?></td> <td><?php echo $rows['lastname']; ?></td> <td><?php echo $rows['address']; ?></td> <td><?php echo $rows['feeamount']; ?></td> </tr> <?php } ?> </tbody> </table> <?php } else { echo "Oops Something went wrong."; } mysqli_close( $connection ); ?> |
Share this:
