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

Recommended image-record pattern

For ordinary website images, keep the image file in a controlled upload directory and store only its generated file name or relative path in MySQL. Escape both text and the generated HTML attribute values when displaying records.

<?php
$result = $connection->query('SELECT id, name, image_file FROM image_gallery ORDER BY id DESC');

while ($row = $result->fetch_assoc()) {
    $name = htmlspecialchars($row['name'], ENT_QUOTES, 'UTF-8');
    $file = rawurlencode(basename($row['image_file']));

    echo '<p>' . $name . '</p>';
    echo '<img src="uploads/' . $file . '" alt="' . $name . '" class="img-fluid">';
}
?>

If an application intentionally stores binary image data in a database, use a BLOB workflow designed for that purpose rather than mixing binary data with a file-path example.

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.
Delete Image and record from MySQL Add Image and record to MySQL

PHP MySQL Collecting single record from MySQL

PHP MySQL Collecting single record from MySQL PHP Code generator for PDO & mysqli

Subhendu Mohapatra

Author & Instructor at plus2net

I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.

About the author Watch on YouTube

Connect: