PHP | MySQL Select Query

Last Updated : 26 Jul, 2022

The SQL SELECT statement is used to select the records from database tables.
Syntax : The basic syntax of the select clause is –

To select all columns from the table, the $*$ character is used.

Implementation of the Select Query : Let us consider the following table ‘ Data ‘ with three columns ‘ FirstName ‘, ‘ LastName ‘ and ‘ Age ‘.

To select all the data stored in the ‘ Data ‘ table, we will use the code mentioned below. SELECT Query 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"; 
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”.

SELECT Query 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"; 
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 :

SELECT Query 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"; 
    $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 matching records are found."; 
    } 
} 
catch (PDOException $e) { 
    die("ERROR: Could not able to execute $sql. " 
                                .$e->getMessage()); 
} 
unset($pdo); 
?> 