How to fetch data from Database in PHP PDO using loop ?

Last Updated : 11 Mar, 2024

The PDO (PHP Data Objects) defines the lightweight, consistent interface for accessing databases in PHP.

Approach: Make sure you have XAMPP or WAMP installed on your windows machine. In case you're using Linux then install the LAMP server. In this article, we will be using the XAMPP server.

Follow the steps to fetch data from the Database in PHP PDO:

1. Create Database: Create a database using XAMPP, the database is named "geeksforgeeks" here. You can give any name to your database.

2. Create Table: Create a table named "fetch_record" with 2 columns to store the data.

3. Create Table Structure: The table "fetch_record" contains 2 fields.

id – primary key – auto increment
studentname – varchar(100)

The datatype for studentname is varchar. The size can be altered as per the requirement. However, 100 is sufficient, and the datatype for "id" is int and it is a primary key. Set the primary key to auto-increment, so that the value of id increase automatically. A primary key also called a primary keyword, is a key in a relational database that is unique for each record. It is a unique identifier, such as a driver's license number, telephone number (including area code), or vehicle identification number (VIN).

To create a table copy and paste the following code into the SQL panel of your PHPMyAdmin.

DROP TABLE IF EXISTS `fetch_record`;
CREATE TABLE IF NOT EXISTS `fetch_record` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `studentname` varchar(100) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

To do this from SQL Panel refer to the following screenshot.

The structure of the table will look like this

4. Insert student record: Here I have only taken the name and id of students. You can add more fields, according to your requirements.

Copy and paste the following code into the SQL panel of your PHPMyAdmin.

INSERT INTO `fetch_record` (`id`, `studentname`) VALUES (NULL, 'Neha'), (NULL, 'Honey'), (NULL, 'Amulaya Sharma'), 
(NULL, 'Kajal Singhal'), (NULL, 'Neeraj Pandey'), (NULL, 'Nikhil Kumar');

After inserting the information, the table will look like this.

5. Create a folder "fetch", that includes the two following files: The folder should be in "C:\xampp\htdocs\" (or where your XAMPP is installed).

5.1. index.php: Here foreach construct provides an easy way to iterate over arrays. foreach works only on arrays and objects and will issue an error when you try to use it on a variable with a different data type or an uninitialized variable. There are two syntaxes:

foreach (array_expression as $value)
    statement

foreach (array_expression as $key => $value)
    statement

The following SQL query is used to fetch all data from the table.

SELECT * FROM fetch_record;

Example:

HTML

<!DOCTYPE html>
<html lang="en">

<head>
    <meta charset="UTF-8">
    <meta name="viewport" content=
        "width=device-width, initial-scale=1.0">
    <meta http-equiv="X-UA-Compatible" content="ie=edge">

    <link rel="stylesheet" href=
"https://maxcdn.bootstrapcdn.com/bootstrap/4.3.1/css/bootstrap.min.css">
    <title>Attendance Page</title>
</head>

<body>
    <div class="container">
        <div class="row">
            <h2>Attendance</h2>
            <table class="table table-hover">
                <thead>
                    <tr>
                        <th>Sno.</th>
                        <th>Student Name</th>
                        <th>Attendance</th>
                    </tr>
                </thead>

                <tbody>
                    <?php
                        include_once('connection.php');
                        $a=1;
                        $stmt = $conn->prepare(
                                "SELECT * FROM fetch_record");
                        $stmt->execute();
                        $users = $stmt->fetchAll();
                        foreach($users as $user)
                        {
                    ?>
                    <tr>
                        <td>
                            <?php echo $user['id']; ?>
                        </td>
                        <td>
                            <?php echo $user['studentname']; ?>
                        </td>

                        <td>
                            <div class="form-check form-check-inline">
                                <input class="form-check-input"
                                        type="radio" name="''"
                                        
                                        id="inlineRadio1"
                                    value="'..$a..'">
                                <label class="form-check-label"
                                    for="inlineRadio1">A</label>
                            </div>

                            <div class="form-check form-check-inline">
                                <input class="form-check-input"
                                    type="radio" name="'..$a..'"
                                    id="inlineRadio2" value="option2">

                                <label class="form-check-label"
                                    for="inlineRadio2">P</label>
                            </div>
                        </td>
                    </tr>
                    <?php
                    }
                    ?>
                </tbody>
            </table>

            <input class="btn btn-primary"
                    type="submit" value="Submit">
        </div>
    </div>
</body>

</html>

5.2. connection.php:

PHP

<?php

$conn = "";

try {
    $servername = "localhost:3306";
    $dbname = "geeksforgeeks";
    $username = "root";
    $password = "";

    $conn = new PDO(
        "mysql:host=$servername; dbname=$dbname;",
        $username, $password
    );
    
    $conn->setAttribute(PDO::ATTR_ERRMODE,
                PDO::ERRMODE_EXCEPTION);
    
} catch(PDOException $e) {
    echo "Connection failed: "
        . $e->getMessage();
}

?>

6. After completing all these steps, now do the following steps:

Run XAMPP
Start Apache server and MySQL
Type http://localhost/fetchData/dashboard.php in your browser.

The table will look like this and that's how you fetch the information from the Database in PHP PDO.

PHP is a server-side scripting language designed specifically for web development. You can learn PHP from the ground up by following this PHP Tutorial and PHP Examples.