How to use WHERE Clause in MySQL

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

SELECT [field1, field2, field3....fieldn]
FROM tableName
WHERE condition;
OR
SELECT *
FROM tableName
WHERE condition;

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 

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

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.

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      |
+-------------+-----------+--------------------------+-----------+
Select Query with Logical operators
  
SELECT id, firstname, lastname,feeamount
FROM members 
WHERE feeamount > 3000

Output:
+----+----------+---------+-----------+
| 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.

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      |
+-------------+-----------+--------------------------+-----------+
Select Query with Logical operators

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.

<?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 );
?>