Fetching image from database in PHP and display in table is similar to fetch any data from database and show in HTML Table.
We generally store images at the server and its path on the database table.
We are considering an example of products to demonstrate the purpose.
Steps to fetch images and data from the database is as below
- Create a MySql database table
- Upload image and store data in 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.
1 2 3 4 5 6 7 8 9 10 11 12 | 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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 | 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
- Connect 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 connection established or not if $connect==false means connection is not established.
- Create a query and pass it to
mysqli_query(.....)
- It will return result in
$result
. - if there are results then iterate each row and print in tabular format
Display products from database in PHP
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 | <?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
1 | <img src="$row['image'].' width=100px height=" 100px"=""> |
Read More
- PHP MySQL CRUD Tutorial with MySqli and PHPMyAdmin
- How to Export and Import MySql Database using phpMyAdmin
- PDO in PHP
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