PHP MySQL Select Data

To retrieve data from a MySQL database, you use the SELECT SQL statement. After fetching the data, PHP can loop through the results and display them in your web application, often inside an HTML table.

The SQL Statement

To select all columns from a table, use the asterisk (*) symbol. To select specific columns, list their names:

-- Select everything
SELECT * FROM Users;

-- Select specific columns
SELECT id, firstname, email FROM Users;

1. MySQLi (Object-Oriented)

In the object-oriented approach, the query() method returns a result object. We then use a while loop with fetch_assoc() to process each row.

<?php
    $conn = new mysqli($servername, $username, $password, $dbname);

    $sql = "SELECT id, firstname, lastname FROM Users";
    $result = $conn->query($sql);

    if ($result->num_rows > 0) {
        // Output data of each row
        while($row = $result->fetch_assoc()) {
            echo "id: " . $row["id"]. " - Name: " . $row["firstname"]. " " . $row["lastname"]. "<br>";
        }
    } else {
        echo "0 results";
    }
    $conn->close();
?>

2. MySQLi (Procedural)

<?php
    $conn = mysqli_connect($servername, $username, $password, $dbname);

    $sql = "SELECT id, firstname, lastname FROM Users";
    $result = mysqli_query($conn, $sql);

    if (mysqli_num_rows($result) > 0) {
        while($row = mysqli_fetch_assoc($result)) {
            echo "id: " . $row["id"]. " - Name: " . $row["firstname"]. " " . $row["lastname"]. "<br>";
        }
    } else {
        echo "0 results";
    }
    mysqli_close($conn);
?>

3. PDO

PDO makes it very easy to fetch all results into an array at once using fetchAll().

<?php
    try {
        $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
        $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        
        $stmt = $conn->prepare("SELECT id, firstname, lastname FROM Users");
        $stmt->execute();

        // Set the resulting array to associative
        $result = $stmt->fetchAll(PDO::FETCH_ASSOC);
        
        foreach($result as $row) {
            echo "id: " . $row["id"]. " - Name: " . $row["firstname"]. " " . $row["lastname"]. "<br>";
        }
    } catch(PDOException $e) {
        echo "Error: " . $e->getMessage();
    }
    $conn = null;
?>

Displaying Data in a Table

A common task is to display database records in a clean Bootstrap table. Here is how you would structure the loop:

<table class="table">
    <thead>
        <tr><th>ID</th><th>Name</th><th>Email</th></tr>
    </thead>
    <tbody>
        <?php while($row = $result->fetch_assoc()): ?>
        <tr>
            <td><?php echo $row['id']; ?></td>
            <td><?php echo $row['firstname'] . " " . $row['lastname']; ?></td>
            <td><?php echo $row['email']; ?></td>
        </tr>
        <?php endwhile; ?>
    </tbody>
</table>

Tip: Always check if num_rows is greater than zero before starting a loop to avoid errors or displaying an empty table.

Summary

Use SELECT to retrieve data from a table.
fetch_assoc() returns a row as an associative array where keys are column names.
A while loop is the standard way to process multiple rows.
PDO::fetchAll() is a convenient way to get all rows into an array in one go.

Pro Tip: To keep your code clean, separate the database logic (fetching data) from the presentation logic (displaying HTML).

« Previous Lesson Next Lesson »