Home » PHP » PHP programs

How to fetch data from MySQL (MariaDB) database using PDO function?

Learn, how to fetch data from MySQL (MariaDB) database using PDO function in PHP programming?
Submitted by Bhanu Sharma, on September 15, 2019 [Last updated : March 13, 2023]

Database Table

Here, we have created a "students" table in the database (myDB) with the following values:

CREATE TABLE students 
  ( 
     id        INT(4) UNSIGNED auto_increment PRIMARY KEY, 
     firstname VARCHAR(30) NOT NULL, 
     lastname  VARCHAR(30) NOT NULL, 
     class     INT(2) 
  ); 

Inserting Records into Table

INSERT INTO students (firstname, lastname, class) VALUES
    -> ('sathish','kumar',12),
    -> ('rakesh','singh',8),
    -> ('sonam','gupta',11),
    -> ('dilbar','pathak',6),
    -> ('salim','khan',7),
    -> ('kabir','singh',6),
    -> ('arjun','reddy',6);

Here, is the data in the database table (students tables),

We use PHP PDO (PHP Data Objects) to interface with MySQL

PHP code to fetch data from MySQL (MariaDB) database using PDO function

<?php 

echo "<table style='border: solid 1px black;'>";
echo "<tr><th>id</th><th>Firstname</th><th>Lastname</th><th>class</th></tr>";

class TableRows extends RecursiveIteratorIterator {
    function __construct($it) {
        parent::__construct($it, self::LEAVES_ONLY);
    }

    function current() {
        return "<td style='width:150px;border:1px solid black;'>" . parent::current(). "</td>";
    }

    function beginChildren() {
        echo "<tr>";
    }

    function endChildren() {
        echo "</tr>" . "\n";
    }
}


//Define Database variables
$host = "localhost";
$user = "mydbuser";
$password = "mydbpassword";
$db = "myDB";


//Try Statement
try {
//Creating a New PDO Connection
    $conn = new PDO("mysql:host=$host;dbname=$db", $user, $password);
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
//mysql Select * from table
    $stmt = $conn->prepare("SELECT * FROM students");
    $stmt->execute();

// set the resulting array
    $result = $stmt->setFetchMode(PDO::FETCH_ASSOC);
    foreach(new TableRows(new RecursiveArrayIterator($stmt->fetchAll())) as $k=>$v) {
        echo $v;
    }
}
catch(PDOException $e) {
    echo "Error: " . $e->getMessage();
}
$conn = null;
echo "</table>";
?> 

To learn more about the PDO, please read PHP manual: PHP Data Objects (PDO)

PHP Database Programs »

---