The WHERE Clause is used to filter only those records that are fulfilled by a specific condition given by the user. in other words, the SQL WHERE clause is used to restrict the number of rows affected by a SELECT, UPDATE or DELETE query.
Syntax :
The basic syntax of the where clause is –
SELECT Column1 , Column2 , ….
FROM Table_Name
WHERE Condition
Implementation of WHERE Clause :
Let us consider the following table “Data” with three columns ‘FirstName’, ‘LastName’ and ‘Age’.
To select all the rows where the “Firstname” is “ram”, we will use the following code :
Where Clause using Procedural Method :
<?php$link = mysqli_connect("localhost", "root", "", "Mydb"); if($link === false){ die("ERROR: Could not connect. " . mysqli_connect_error()); } $sql = "SELECT * FROM Data WHERE Firstname='ram'"; if($res = mysqli_query($link, $sql)){ if(mysqli_num_rows($res) > 0){ echo "<table>"; echo "<tr>"; echo "<th>Firstname</th>"; echo "<th>Lastname</th>"; echo "<th>age</th>"; echo "</tr>"; while($row = mysqli_fetch_array($res)){ echo "<tr>"; echo "<td>" . $row['Firstname'] . "</td>"; echo "<td>" . $row['Lastname'] . "</td>"; echo "<td>" . $row['Age'] . "</td>"; echo "</tr>"; } echo "</table>"; mysqli_free_result($res); } else{ echo "No Matching records are found."; } } else{ echo "ERROR: Could not able to execute $sql. " . mysqli_error($link); } mysqli_close($link); ?> |
Output :
Code Explanation :
- The “res” variable stores the data that is returned by the function mysql_query().
- Everytime mysqli_fetch_array() is invoked, it returns the next row from the res() set.
- The while loop is used to loop through all the rows of the table “data”.
Where Clause using Object Oriented Method :
<?php$mysqli = new mysqli("localhost", "root", "", "Mydb"); if($mysqli === false){ die("ERROR: Could not connect. " . $mysqli->connect_error); } $sql = "SELECT * FROM Data WHERE Firstname='ram'"; if($res = $mysqli->query($sql)){ if($res->num_rows > 0){ echo "<table>"; echo "<tr>"; echo "<th>Firstname</th>"; echo "<th>Lastname</th>"; echo "<th>Age</th>"; echo "</tr>"; while($row = $res->fetch_array()){ echo "<tr>"; echo "<td>" . $row['Firstname'] . "</td>"; echo "<td>" . $row['Lastname'] . "</td>"; echo "<td>" . $row['Age'] . "</td>"; echo "</tr>"; } echo "</table>"; $res->free(); } else{ echo "No matching records are found."; } } else{ echo "ERROR: Could not able to execute $sql. " . $mysqli->error; } $mysqli->close(); ?> |
Output :
Where Clause using PDO Method :
<?phptry{ $pdo = new PDO("mysql:host=localhost; dbname=Mydb", "root", ""); $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); } catch(PDOException $e){ die("ERROR: Could not connect. " . $e->getMessage()); } try{ $sql = "SELECT * FROM Data WHERE Firstname='ram'"; $res = $pdo->query($sql); if($res->rowCount() > 0){ echo "<table>"; echo "<tr>"; echo "<th>Firstname</th>"; echo "<th>Lastname</th>"; echo "<th>Age</th>"; echo "</tr>"; while($row = $res->fetch()){ echo "<tr>"; echo "<td>" . $row['Firstname'] . "</td>"; echo "<td>" . $row['Lastname'] . "</td>"; echo "<td>" . $row['Age'] . "</td>"; echo "</tr>"; } echo "</table>"; unset($res); } else{ echo "No records matching are found."; } } catch(PDOException $e){ die("ERROR: Could not able to execute $sql. " . $e->getMessage()); } unset($pdo); ?> |
Output :
