PHP | MySQL Select Query

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:

1. The “res” variable stores the data that is returned by the function mysql_query().
2. Everytime mysqli_fetch_array() is invoked, it returns the next row from the res() set.
3. 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);
?>

Output :