How to reference image stored in directory in MySQL table?

Question

How to reference image stored in directory in MySQL table?

7.6k Views Asked by Gloire At 01 July 2016 at 18:24

I have a set of png images (16x16) stored in a folder that's on my web server. Path: c:/wamp/www/website/images/flags . These images represent the different flags of the world's countries.

I want to have access to these images dynamically when I'll output the list of countries and their respective flags in a table through a PHP script.

In my database I have created the following table

CREATE TABLE country (
  ID INT UNSIGNED NOT NULL AUTO_INCREMENT,
  countryCode CHAR(2) NOT NULL, 
  countryName VARCHAR (50) NOT NULL,
  phoneCode CHAR (3) NOT NULL, 
  imagePathName VARCHAR (254) NOT NULL,
  PRIMARY KEY(ID)
) ENGINE = InnoDB;

How does image referencing work in such a case? Do I store the path just like any CSS or HTML ?

Should I have to store a path, do I simply add it in the following form:

INSERT INTO country VALUES ('', 'CD', 
'Democratic Republic of Congo, '243',   'c:/wamp/www/website/images/flags/cd.png')

or must I have a column with a BLOB data type. Should that be the case how will the INSERT statement look like ?

Original Q&A

There are 2 best solutions below

Mahesh Madushanka On 01 July 2016 at 18:28

my recommendation is as follows

1 don't store images in My sql DB. it will increase the db size( page count) and it will impact on DB performance

2 store image folder path in your config file (PHP)

3 store only the image name or id in the database table and using your app you can combine name + path and get the full image path

i think this will make sense to you

**Gloire** · Accepted Answer · 2016-07-02T00:07:20.100000

Following Mahesh's suggestion:

Here's the code

<?php
$dbc = mysqli_connect('127.0.0.1','root','','fortesting') or die (mysqli_error($dbc)); 

$query = "SELECT countryCode AS 'Code', countryName AS 'Name', filename AS 'Flag' 
FROM country";

$execute = mysqli_query($dbc, $query);
?>
<html>
    <head>
        <title>test</title>
    </head>
    <body>
        <table>
        <?php while ($result = mysqli_fetch_array($execute)){ ?>
            <tr>
                <td><?php echo $result['Name'] ?> </td>
                <td><img src="images/flags/<?php echo $result['Flag']?>.png"></td>
            </tr>
        <?php } ?>
        </table>
    </body>
</html>

In my table I have three main columns which are countryCode, countryName and filename. I inserted a few values such as:

INSERT INTO country (countryCode, countryName, filename) VALUES
('DZ', 'Algeria', 'dz');

The while() loop displays the content of the MySQL table.

In the first column of the HTML table the name of the country is displayed through:

<td><?php echo $result['Name'] ?> </td>

In the second column of the HTML table the file name of the flag that represents that country is printed inside a URL link:

<td><img src="images/flags/<?php echo $result['Flag']?>.png"></td>

The names of the countries and flags are displayed dynamically. Exactly what I needed.

How to reference image stored in directory in MySQL table?

There are 2 best solutions below

Related Questions in MYSQL

Related Questions in SQL

Related Questions in DATABASE

Trending Questions

Popular # Hahtags

Popular Questions