PHP MySQL: Querying Data from Database

Summary: in this tutorial, you will learn how to query data from the MySQL database by using PHP PDO.

To query data from a table using PHP, you follow these steps:

First, connect to the MySQL database.
Second, create a prepared statement.
Third, execute the prepared statement with data.
Finally, process the result set.

Querying all rows from a table

The following select.php script retrieves all rows from the tasks table:

<?php
require_once 'config.php';

try {
    // connect to MySQL server
    $conn = new PDO("mysql:host=$host;dbname=$dbname", $username, $password);
    
    // execute a query
    $sql = 'select * from tasks';
    $stmt = $conn->query($sql);
    $stmt->setFetchMode(PDO::FETCH_ASSOC);

    // process the results
    $tasks = [];
    while ($row = $stmt->fetch()) {
        $tasks[] = [
            'title' => $row['title'], 
            'completed' => $row['completed'] == 0 ? false : true
        ];
    }
    
    // display the task list
    require 'select.view.php';
} catch (PDOException $e) {
    die($e);
}
Code language: PHP (php)

How it works.

First, include the config.php file that contains the database configuration:

require_once 'config.php';Code language: PHP (php)

Second, construct a SELECT statement:

$sql = 'select * from tasks';Code language: PHP (php)

Third, execute the SELECT statement by calling the query() method of the PDO object:

$stmt = $conn->query($sql);Code language: PHP (php)

Fourth, set the fetch mode to PDO::FETCH_ASSO so that the result sets will be an associative array.

 $stmt->setFetchMode(PDO::FETCH_ASSOC);Code language: PHP (php)

Fifth, iterate over the rows and append them to the $tasks array:

$tasks = [];
while ($row = $stmt->fetch()) {
    $tasks[] = [
        'title' => $row['title'],
        'completed' => $row['completed'] == 0 ? false : true
    ];
}Code language: PHP (php)

Finally, include the select.view.php script to display the to-do list. The select.view.php iterates over the $tasks array and displays each item:

<!DOCTYPE html>
<html lang="en">
    <head>
        <meta charset="UTF-8">
        <meta name="viewport" content="width=device-width, initial-scale=1.0">
        <title>Todo</title>
    </head>
    <body>
        <table>
            <h1>My Todo</h1>
            <thead>
                <tr>
                    <th>Title</th>
                    <th>Completed</th>
                </tr>
            </thead>
            <tbody>
                <?php foreach ($tasks as $task): ?>
                <tr>
                    <td><?= $task['title'] ?></td>
                    <td><?= $task['completed'] ?  '✅' : '⬜' ?></td>
                </tr>
                <?php endforeach ?>
            </tbody>
        </table>
    </body>

</html>Code language: PHP (php)

Summary

Use the query() method of a prepared statement to retrieve data from a table.

Was this tutorial helpful?