PHP | MySQL WHERE Clause

Last Updated : 03 Mar, 2018

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 :

<?php
try{ 
    $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 :

My Personal Notes

Dec	JAN	Feb
	18
2020	2021	2022

Related Articles

Implementation of WHERE Clause :