PHP MySQL Select Data

PHP Select Data From MySQL allows selecting the data from the database. MySQL SELECT statement allows fetching zero or multiple rows from database tables or views. The SELECT statement is one of the widely used MySQL queries.

The SELECT statement returns a result that comprises rows and columns, also called a Resultset.

Read Also: PHP MySQL Multiple Insert

Select data from MySQL database in PHP

In a PHP web application, we wish to select data from the MySQL database then first establish a connection between the PHP web application and the MySQL database. After establishing a connection, use the SELECT statement to select data from the MySQL database.

SELECT Statement Syntax

The SELECT Syntax consist of two parts; first, we retrieve all columns representing full table data; second, we retrieve some specific columns from a table.

Select all columns from the table

SELECT * FROM table_name

To Select some columns of data from a table

SELECT col1, col2, col3 FROM table_name

1. SELECT Query using Procedural Method

<?php 

$conn = mysqli_connect("localhost", "user_name", "password", "database"); 
if ($conn == false) { 
    die("ERROR: Could not connect. ".mysqli_connect_error()); 
} 

$query = "SELECT * FROM guest"; 
if ($result = mysqli_query($conn, $query)) { 
    if (mysqli_num_rows($result) > 0) { 
        echo "<table>"; 
        echo "<tr>"; 
        echo "<th>Firstname</th>"; 
        echo "<th>Lastname</th>"; 
        echo "<th>age</th>"; 
        echo "</tr>"; 
        while ($row = mysqli_fetch_array($result)) { 
            echo "<tr>"; 
            echo "<td>".$row['Firstname']."</td>"; 
            echo "<td>".$row['Lastname']."</td>"; 
            echo "<td>".$row['Age']."</td>"; 
            echo "</tr>"; 
        } 
        echo "</table>"; 
    } else { 
        echo "No matching records are found."; 
    } 
} else { 
    echo "ERROR: Could not able to execute $query. ".mysqli_error($conn); 
} 

mysqli_close($conn); 

?>

Output

Explanation

• The data returned by the mysqli_query() is stored in the $result variable.
• Each time mysqli_fetch_array() comes in the picture, it returns the next row from the result set as an array.
• The while loop is useful to loops through all the rows in the result set.
• The value of a field can be fetched from the row either bypassing the index or name to the $row variable like $row[‘id’] or $row[0], $row[‘firstname’] or $row[1], $row[‘lastname’] or $row[2], and $row[‘Age’] or $row[3].
• If we want to use the loop, we can obtain the loop counter value or rows returned by the SQL query bypassing the $result variable to the mysqli_num_rows() function.

2. SELECT Query using Object-Oriented Method 

<?php

$conn = new mysqli("localhost", "user_name", "password", "database"); 

if ($conn == false) { 
    die("ERROR: Could not connect. ".$conn->connect_error); 
}   $query = "SELECT * FROM Data"; 

if ($result = $mysqli->query($query)) { 
    if ($result->num_rows > 0) { 
        echo "<table>"; 
        echo "<tr>"; 
        echo "<th>Firstname</th>"; 
        echo "<th>Lastname</th>"; 
        echo "<th>Age</th>"; 
        echo "</tr>"; 
        while ($row = $result->fetch_array()) { 
            echo "<tr>"; 
            echo "<td>".$row['Firstname']."</td>"; 
            echo "<td>".$row['Lastname']."</td>"; 
            echo "<td>".$row['Age']."</td>"; 
            echo "</tr>"; 
        } 
        echo "</table>"; 
        $result->free(); 
    } else { 
        echo "No matching records are found."; 
    } 
} else { 
    echo "ERROR: Could not able to execute $query. " .$conn->error; 
} 
$mysqli->close(); 
?> 

Output

3. SELECT Query using PDO Method 

<?php

try { 
    $pdo = new PDO("mysql:host = localhost; dbname=database", "user_name", "password"); 
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); 
} catch (PDOException $e) { 
    die("ERROR: Could not connect. ".$e->getMessage()); 
} 
try { 
    $query = "SELECT * FROM Data"; 
    $result = $pdo->query($query); 
    if ($result->rowCount() > 0) { 
        echo "<table>"; 
        echo "<tr>"; 
        echo "<th>Firstname</th>"; 
        echo "<th>Lastname</th>"; 
        echo "<th>Age</th>"; 
        echo "</tr>";

        while ($row = $result->fetch()) { 
            echo "<tr>";
            echo "<td>".$row['Firstname']."</td>"; 
            echo "<td>".$row['Lastname']."</td>"; 
            echo "<td>".$row['Age']."</td>"; 
            echo "</tr>"; 
        } 

        echo "</table>"; 
        unset($result); 
    } else { 
        echo "No matching records are found."; 
    } 
} catch (PDOException $e) { 
    die("ERROR: Could not able to execute $query. ".$e->getMessage()); 
} 
unset($pdo); 

?> 

Output

Must Read: PHP MySQL WHERE Clause

Conclusion

In a PHP web application, we wish to select data from the MySQL database then first establish a connection between the PHP web application and the MySQL database.

I hope you find this article helpful:)