Displaying Images from MySQL database records using PHP script with MySQLi or PDO

We will not store the image in MySQL table ( plus2_db_images) but store the image name in table record. While displaying the image we will show other details of the record like ID, Price and Name of the image. You can download the script with all images at the end of this tutorial .

Connecting database and executing Query

To manage data we have to connect to MySQL database and execute query to get our date. Here there are two ways to use PHP drivers to connect to MySQL and execute the functions for getting records.

One is using Portable Data Object ( PDO )
Second one is MySQLI ( MysQL Improved )

We used both types and you can download both the scripts inside the same Zip file.

Name	ID	Price
Rose	1	20.34
Dragon Fly	2	12.45
Butter Fly	3	22.34
Anchor	4	20.12

Looing through the records and showing image with other details
MySQLi

if($stmt = $connection->query("SELECT * FROM plus2_db_images")){

  echo "No of records : ".$stmt->num_rows."<br>";

  echo "<table class='table my_table'>
<tr class='info'> <th> Image </th><th>Name</th><th>ID</th><th>Price</th></tr>";
while ($row = $stmt->fetch_assoc()) {
echo "<tr><td><img src=images/$row[img] class='rounded-circle' alt='$row[p_name]'></td>
<td>$row[p_name]</td><td>$row[p_id]</td><td>$row[price] </td></tr>";
  }
echo "</table>";
}else{
echo $connection->error;
}

MySQLi Select query to display records

PDO

$q=" SELECT * FROM plus2_db_images ";
echo "<table class='table my_table'>
<tr class='info'> <th> Image </th><th>Name</th><th>ID</th><th>Price</th></tr>";

foreach ($dbo->query($q) as $row) {
echo "<tr><td><img src=images/$row[img] class='rounded-circle' alt='$row[p_name]'></td>
<td>$row[p_name]</td><td>$row[p_id]</td><td>$row[price] </td></tr>";
  }
echo "</table>";

PDO Select query to display records

How the images are stored .

Images are stored inside image directory. The name of the image is stored in the record. We are not storing the path of the image in our record as we can change the path in our PHP script easily than using SQL to update the records if any changes in path is required.

BLob : Binary Large Object columns can be used to store images.
PDO Blob column adding , updating & displaying records

Style and Layout

We used bootstrap 4 for our layout and images styles ( making it rounded ) , you can use your own style as the basic script remain same.

Downloading Zip file

Download the zip file and use this instructions to run the file.

How to Install and test

Download the zip file at the end of this page.
include\config.php : Add your database login details ( MySQLi connection ).
include\config-pdo.php : Add your database login details ( PDO connection ).
index.php : PHP script to show images from database using MySQLi.
index-pdo.php : PHP script to show images from database using PDO.
sql_dump.txt : MySQL dump to create database table .

This script is kept simple for easy understanding. To this program we can add deleting of image ( along with the record ) and adding of the image to database table. This we will learn in next part.

Displaying images from MySQL table

Connecting database and executing Query

How the images are stored .

Style and Layout

Downloading Zip file

Subscribe