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

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

Bringing information from the database in PHP: Here, we will figure out how to bring information from MySQL (MariaDB) database utilizing PDO function in PHP programming?

Here, we have made an “understudies” table in the database (myDB) with the accompanying 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 information in the database table (understudies tables),

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

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

PHP code to bring information from the 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 get familiar with the PDO, if it’s not too much trouble read PHP manual: PHP Data Objects (PDO)

Recent Posts

error: Alert: Content is protected!!