Home » PHP » PHP Examples

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

Fetching data from database in PHP: Here, we are going to learn how to fetch data from MySQL (MariaDB) database using PDO function in PHP programming?
Submitted by Bhanu Sharma, on September 15, 2019

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) 
  ); 
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),

fetch data from MySQL (MariaDB) database using PDO function

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

PHP code to fetch data from database

<?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)






Comments and Discussions

Ad: Are you a blogger? Join our Blogging forum.





Languages: » C » C++ » C++ STL » Java » Data Structure » C#.Net » Android » Kotlin » SQL
Web Technologies: » PHP » Python » JavaScript » CSS » Ajax » Node.js » Web programming/HTML
Solved programs: » C » C++ » DS » Java » C#
Aptitude que. & ans.: » C » C++ » Java » DBMS
Interview que. & ans.: » C » Embedded C » Java » SEO » HR
CS Subjects: » CS Basics » O.S. » Networks » DBMS » Embedded Systems » Cloud Computing
» Machine learning » CS Organizations » Linux » DOS
More: » Articles » Puzzles » News/Updates


© https://www.includehelp.com some rights reserved.