Is there a way to get my images to show up on my PHP page through MySQL or phpMyAdmin?

Question

I am wondering if I can get my PHP page running because I try to load image files from the database in phpMyAdmin and on MySQL. Here is the line of code I was working on:

CREATE DATABASE WORLD;

use WORLD;

CREATE TABLE COUNTRY (
    country_Order INT AUTO_INCREMENT NOT NULL,
    continent_Name VARCHAR(225),
    country_Name VARCHAR(225),
    img BLOB,
    country_Region VARCHAR(225),
    country_Population INT,
    country_Info VARCHAR(225),
    PRIMARY KEY (country_Order)
)  ENGINE=INNODB;

INSERT INTO COUNTRY (country_Order, continent_Name, country_Name, img, country_Region, country_Population, country_Info)
VALUES (1, "Asia", "Afghanistan", LOAD_FILE('\Users\sammyabdulkader\desktop\World-Data\img\Afghanistan.png'), "Central Asia", 38928346,"Afghanistan is a country located in Central Asia. It is a mountainous landlocked country.");

I want manually insert my own images that why from my folder path. This is being done on my MacBook Pro. Here is my PHP code:

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Frameset//EN">
<html>
  <body>
    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
        <title>World Database</title>
        <style type="text/css">
        h1 {
            color: #42963d;
            font-family: 'Open Sans', sans-serif;
            font-size: 34px;
            font-weight: 300;
            line-height: 40px;
            margin: 0 0 16px;
            text-align: center;
        }

        h2 {
            font-family: 'Open Sans', sans-serif;
            font-weight: 300;
            text-align: center;
            color: #50d248;
        }

        p.footer {text-align: center}
        table.output {font-family: Ariel, sans-serif}

        table {
            border-collapse: collapse;
            width: 100%;
            color: #4ee44e;
            font-family: monospace;
            font-size: 25px;
            text-align: center;
        }

         th {
            background-color: #0b7208;
            color: white;
        }

        tr:nth-last-child(even) {background-origin: #f2f2f2}

         hr {
            height: 12px;
            border: 0;
            box-shadow: inset 0 12px 12px -12px rgba(0, 0, 0, 0.5);
        }

        a {
            color: red;
            font-family: helvetica;
            text-decoration: none;
            text-transform: uppercase;
        }

         a:hover {
            text-decoration: underline;
        }

        a:active {
            color: black;
        }

        a:visited {
            color: purple;
        }
        </style>
    </head>
   <body>
<?php
// Get connection
//$Conn = odbc_connect('database name','database user','database user & password');
$Conn = odbc_connect('WORLD', 'WORLD-User', 'WORLD-User+password');

// Test connection
if (!$Conn)
{
    exit("ODBC Connection Failed: " . $Conn);
}
// Create SQL statement
$SQL = "SELECT * FROM COUNTRY";

$RecordSet = odbc_exec($Conn, $SQL);

// Test existence of recordset
if (!$RecordSet)
{
    exit("SQL Statement Error: " . $SQL);
}

?>
                        <!--  Page Headers -->
    <h1>List of countries from around the global.</h1>
        <hr />
    <h2>All Country's Alphabetical Order</h2>
<?php
// Table headers 
echo "<table class='output' border='1'>
        <tr>
        <th>Country Order</th>
        <th>Continent Name</th>
        <th>country Name</th>
        <th>Country Flag</th>
        <th>Continent Region</th>
        <th>Country Population</th>
        <th>Country Info</th>
        </tr>";

// Table data
while ($RecordSetRow = odbc_fetch_array($RecordSet))
{
    echo "<tr>";
    echo "<td>" . $RecordSetRow['country_Order'] . "</td>";
    echo "<td>" . $RecordSetRow['continent_Name'] . "</td>";
    echo "<td>" . $RecordSetRow['country_Name'] . "</td>";
    echo "<td>" . $RecordSetRow['img'] . "</td>";
    echo "<td>" . $RecordSetRow['country_Region'] . "</td>";
    echo "<td>" . $RecordSetRow['country_Population'] . "</td>";
    echo "<td>" . $RecordSetRow['country_Info'] . "</td>";
    echo "</tr>";
}
echo "</table>";

// Close connection
odbc_close($Conn);
?>
        <br />
         <hr />
        <p class="footer"><a href="../World-Data/index.html">Return to World Data</a></p>
       <hr />
  </body>
</html>

My problem is that when I did the manual way, I get null values for my PHP table. The other problem is doing it on phpMyAdmin makes it look worse. Instead, it returns stranges characters.

Is there a possible way to fix my problem so that I can get my images in my database table up and running? phpMyAdmin way inserting the images from the online database Manual way from MySQL insert

You can't insert binary images as-is into an HTML page. You either need to have regular img tags where the source is something like "return-image.php?id=image-id" (and write that PHP page) or as Base64 - https://stackoverflow.com/questions/2807251/can-i-embed-a-png-image-into-an-html-page — Dave S, Aug 01 '20 at 20:25
You need to save `$RecordSetRow['img']` into a binary file and then display it like``. I see no reason saving binary data and put them into files. Better keep the files and store a reference to the image into DB. — Markus Zeller, Aug 01 '20 at 20:26
@MarkusZeller: Generally bad advice, if not mentioned alongside the mayor downside: When there are files in the filesystem and only paths to them in the database, which are just ordinary strings for the DBMS, then the DBMS cannot assure integrity (at least not without any further complicated extra measures that need to be implemented manually). There might be paths without a file or files not referenced anywhere. If integrity is of any concern in the application, then storing the file content in the database is the better/easier way. — sticky bit, Aug 01 '20 at 20:33
phpMyAdmin is a MySQL administration tool written in PHP, it is not a database itself. You are probably using MySQL or MariaDB as your DB. — Dharman, Aug 01 '20 at 20:40
@stickybit Filesystems are for files, Databases for data. MySQL is bad in storing large BLOBs. There is no integrity problem, only if you produce such. — Markus Zeller, Aug 02 '20 at 10:04
@MarkusZeller: File content ultimately is just data too. And I didn't say there was an immediate integrity issue but that such cannot be prevented. And preventing inconsistencies is one of the main reasons behind the philosophy of databases and why one would use them. — sticky bit, Aug 02 '20 at 15:49
@stickybit You are absolutely right. This is very opinion based and depends on use case. I would mostly not recommend storing files in DB, because a) you mostly never search in that b) Databases are files c) Overhead putting them back to files d) Imports/Exports take much longer time e) Often errors in export/importing back f) Huge dumps g) harder to manage over multiple servers (master/slave, etc.) h) space increases — Markus Zeller, Aug 02 '20 at 19:38

score 0 · Answer 1 · answered Aug 02 '20 at 10:16

0

This is just an example approach on how to do it.

The idea is to create a unique filename for each image. A simple fast way is a MD5 hash. Then we check if the file is there, and if not we create it. This caching improves performance on reload and image does not need to be created again.

With the use of IMG tag we just display the (newly created) file.

Note: You may need to style it with CSS and place the images into a directory for better organization.

while ($RecordSetRow = odbc_fetch_array($RecordSet))
{
    $filename = md5($RecordSetRow['img']) . '.png';
    if(!file_exists($filename)) {
          file_put_contents($filename, $RecordSetRow['img']);
    }

    echo "<tr>";
    echo "<td>" . $RecordSetRow['country_Order'] . "</td>";
    echo "<td>" . $RecordSetRow['continent_Name'] . "</td>";
    echo "<td>" . $RecordSetRow['country_Name'] . "</td>";

    // Use HTML image tag with that file
    echo "<td><img src='{$filename}' alt=''></td>";

    echo "<td>" . $RecordSetRow['country_Region'] . "</td>";
    echo "<td>" . $RecordSetRow['country_Population'] . "</td>";
    echo "<td>" . $RecordSetRow['country_Info'] . "</td>";
    echo "</tr>";
}
echo "</table>";

answered Aug 02 '20 at 10:16

Markus Zeller

8,516
2
29
35

https://sabdul.it.pointpark.edu/World-Data/world.php – Sammy Abdulkader Aug 04 '20 at 08:49
It works however, the images show halfway. How can I complete have it displayed all at once – Sammy Abdulkader Aug 04 '20 at 08:50
Compare the written image with the original. Compare the blob size with the original file size. Also make sure using the [right sized BLOB type](https://dev.mysql.com/doc/refman/8.0/en/blob.html) to fit all image data. – Markus Zeller Aug 04 '20 at 09:16
Try MEDIUMBLOB or even LONGBLOB. – Markus Zeller Aug 04 '20 at 09:23
I try both. Both still the images don't even display properly – Sammy Abdulkader Aug 04 '20 at 15:46
You need to reimport them after changing BLOB type, of course, because 2^16 was too small. When the data was truncated, it will still remain! Besides that you did not write any about size comparison I've asked for. – Markus Zeller Aug 04 '20 at 16:24
TINYBLOB L+1 bytes, where L < 28 (256 bytes) BLOB L+2 bytes, where L < 216 (65 kilobytes) MEDIUMBLOB L+3 bytes, where L < 224 (16 megabytes) LONGBLOB L+4 bytes, where L < 232 (4 gigabytes) – Sammy Abdulkader Aug 04 '20 at 17:22
It's something like this one of the images I upload on PHPMyAdmin has a memory 201 kb. Does that mean I use BLOB or LONGBLOB data type? – Sammy Abdulkader Aug 04 '20 at 17:24
Look at the table structure what column type it is. Change to MEDIUMBLOB and do the import again, as suggested before. – Markus Zeller Aug 04 '20 at 18:41
Mark Zeller, I decided to change my plan and go with the emoji flags. How can I make sure my Emoji column doesn't display ?? questions marks. Here is my another question I post: https://stackoverflow.com/questions/63255331/how-can-i-display-my-emoji-on-my-php-through-mysql-database https://sabdul.it.pointpark.edu/World-Data/world.php – Sammy Abdulkader Aug 05 '20 at 17:34
You need to use the correct encoding, of course. I suggest utf8mb4_general_ci. Make sure the database connection itself uses the same. If the DB tool shows question marks, you need to update or reinsert the records! – Markus Zeller Aug 05 '20 at 18:52

Is there a way to get my images to show up on my PHP page through MySQL or phpMyAdmin?

1 Answers1