How to fetch image from database in PHP and display in table

Fetching an image from a database in PHP and display in the table is similar to fetch any data from a database and show in HTML Table.

We generally store images on the server and their path on the database table.

We are considering an example of products to demonstrate the purpose.

The steps to fetch images and data from the database is as below

Create a MySql database table
Upload images and store data in a database table using PHP
Fetch images and data from the database using PHP

1 Create MySql Database Table

For this, we have created a table product in ebhor database.

CREATE TABLE product 
  ( 
     id       BIGINT(10) UNSIGNED NOT NULL auto_increment, 
     name     VARCHAR(100) NOT NULL, 
     price    DECIMAL(10, 2) NOT NULL, 
     category VARCHAR(100) NOT NULL, 
     image    VARCHAR(100) NOT NULL, 
     adddate  TIMESTAMP DEFAULT CURRENT_TIMESTAMP, 
     PRIMARY KEY (id) 
  ) 
engine=innodb 
DEFAULT charset=utf8

CREATE TABLE product

(

id BIGINT(10) UNSIGNED NOT NULL auto_increment,

name VARCHAR(100) NOT NULL,

price DECIMAL(10, 2) NOT NULL,

category VARCHAR(100) NOT NULL,

image VARCHAR(100) NOT NULL,

adddate TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

PRIMARY KEY (id)

)

engine=innodb

DEFAULT charset=utf8

2 Upload image and store data in database table using PHP

Uploading images and data are not discussed here.

After uploading images and data our product table will look like this.

We are providing above data for copy and create your database

INSERT INTO `product` 
            (`id`, 
             `name`, 
             `price`, 
             `category`, 
             `image`, 
             `adddate`) 
VALUES      (1, 
             'Television', 
             '15000.00', 
             'Electronics', 
             'http://ebhor.com/wp-content/uploads/2020/12/tv-min.jpg', 
             '2020-12-30 06:45:34'), 
            (2, 
             'Mobile', 
             '12000.00', 
             'Electronics', 
'http://ebhor.com/wp-content/uploads/2020/12/mobile-min-683x1024.jpg', 
'2020-12-30 06:46:40'), 
            (3, 
             'Headphone', 
             '2000.00', 
             'Electronics', 
'http://ebhor.com/wp-content/uploads/2020/12/headphone-min.jpg', 
'2020-12-30 06:47:33'), 
            (4, 
             'Camera', 
             '60000.00', 
             'Electronics', 
             'http://ebhor.com/wp-content/uploads/2020/12/camera-min.jpg', 
             '2020-12-30 06:48:10');

INSERT INTO `product`

(`id`,

`name`,

`price`,

`category`,

`image`,

`adddate`)

VALUES (1,

'Television',

'15000.00',

'Electronics',

'http://ebhor.com/wp-content/uploads/2020/12/tv-min.jpg',

'2020-12-30 06:45:34'),

(2,

'Mobile',

'12000.00',

'Electronics',

'http://ebhor.com/wp-content/uploads/2020/12/mobile-min-683x1024.jpg',

'2020-12-30 06:46:40'),

(3,

'Headphone',

'2000.00',

'Electronics',

'http://ebhor.com/wp-content/uploads/2020/12/headphone-min.jpg',

'2020-12-30 06:47:33'),

(4,

'Camera',

'60000.00',

'Electronics',

'http://ebhor.com/wp-content/uploads/2020/12/camera-min.jpg',

'2020-12-30 06:48:10');

Here Image column contains the URL where the image is uploaded.

You can learn more about PHP and MySql.

3 Fetch image and data from database using PHP

As you have already seen images are stored in the server file system and its path is stored in the database.

To access data from the database we can use either the Procedural way using mysqli or PDO or the object-oriented way.

we will consider here a procedural way to access data.

This is a simple PHP MySql select statement.

Steps to access MySQL database with PHP

Connecting PHP with MySql using mysqli_connect(“localhost”, “root”, “”, “ebhor”); localhost means the database is in the same system, the username is the root, the password is “”, and ebhor is the database name.
check whether the connection is established or not if $connect==false means the connection is not established.
Create a query and pass it to mysqli_query(…..)
It will return results in $result.
if there are results then iterate each row and print in tabular format

Display products from database in PHP

<?php
//connect to mysql server with username password and database name
$connect = mysqli_connect("localhost", "root", "", "ebhor");
// Check connection
if ($connect === false) {
    die("Opps Unable to connect " . mysqli_connect_error());
}
// create query to select data
$sql = "SELECT * FROM product";
if ($result = mysqli_query($connect, $sql)) {
    if (mysqli_num_rows($result) > 0) {
        echo '<table border="1px">';
        echo "<tr>";
        echo "<th>Product Name</th>";
        echo "<th>Price (INR)</th>";
        echo "<th>Category</th>";
        echo "<th>Image</th>";
        echo "</tr>";
        while ($row = mysqli_fetch_array($result)) {
            echo "<tr>";
            echo "<td>" . $row['name'] . "</td>";
            echo "<td>" . $row['price'] . "</td>";
            echo "<td>" . $row['category'] . "</td>";
            echo "<td>" . "<img src=".$row['image'].' width=100px height="100px">' . "</td>";
            echo "</tr>";
        }
        echo "</table>";
        // Free result set
        mysqli_free_result($result);
    } else {
        echo "No records found";
    }
} else {
    echo "ERROR: Could not able to execute $sql. " . mysqli_error($connect);
}
// Close connection
mysqli_close($connect);
?>

<?php

//connect to mysql server with username password and database name

$connect = mysqli_connect("localhost", "root", "", "ebhor");

// Check connection

if ($connect === false) {

die("Opps Unable to connect " . mysqli_connect_error());

}

// create query to select data

$sql = "SELECT * FROM product";

if ($result = mysqli_query($connect, $sql)) {

if (mysqli_num_rows($result) > 0) {

echo '<table border="1px">';

echo "<tr>";

echo "<th>Product Name</th>";

echo "<th>Price (INR)</th>";

echo "<th>Category</th>";

echo "<th>Image</th>";

echo "</tr>";

while ($row = mysqli_fetch_array($result)) {

echo "<tr>";

echo "<td>" . $row['name'] . "</td>";

echo "<td>" . $row['price'] . "</td>";

echo "<td>" . $row['category'] . "</td>";

echo "<td>" . "<img src=".$row['image'].' width=100px height="100px">' . "</td>";

echo "</tr>";

}

echo "</table>";

// Free result set

mysqli_free_result($result);

} else {

echo "No records found";

}

} else {

echo "ERROR: Could not able to execute $sql. " . mysqli_error($connect);

}

// Close connection

mysqli_close($connect);

By iterating mysqli_fetch_array($result) we are fetching rows one by one and showing in tabular format.

To fetch images from URL we use an HTML tag <img src =""> as below

<img src="$row['image'].' width=100px height=" 100px"="">

1	<img src="$row['image'].' width=100px height=" 100px"="">

Resources:

Photo by Pok Rie from Pexels
Image by ADMC from Pixabay
Photo by C D-X on Unsplash
Photo by ATC Comm Photo from Pexels