Display image in a table below other fields


#1

Can anybody help me i have a database with games listed on it and i am in the process of adding screenshots to the database, my problem is how do i show these.

i have a table that randomly picks a game from the database and I can get it to show the other fields see below

<?php
$sql = "SELECT * FROM games order by rand() limit 1;";
	$result = mysqli_query($conn, $sql) or die("Bad Query: $sql");

	while ($row = mysqli_fetch_assoc($result)) {
    echo  "<tr><th>GAME NAME:    </th><td>{$row['game_name']}</td></tr>";
    /*echo "<br>";*/
    echo  "<tr><th>PLATFORM:         </th><td>{$row['system']}</td></tr>";
  /*  echo "<br>";*/
    echo  "<tr><th>YEAR:         </th><td>{$row['game_year']}</td></tr>";
    /*echo "<br>";*/
    echo  "<tr><th>PUBLISHER:    </th><td>{$row['publisher_name']}</td></tr>";
    echo "<tr><th>ID:</th><td>{$row['id']}</td></tr>";

	}
	?>

what i want is to display the picture underneath the above table.

I put the image in a folder called ‘…/Images/’ and the image field is called shot.


#2

You store the image filename in the table, then use an img tag in the html.

echo '<tr><th>Screenshot:</th><td><img src="../images/'.$row['shot'].'" alt="Screenshot of '.$row['game_name'].'" width="320" height="180"></td></tr>';

On a side note, what are those errant <br> tags doing in your table?


#3

Thanks for your response. i get the following image on screen.

And thanks for the tip about the br tags i have deleted them must have forgot about them


#4

What is in the “shot” column, is it the actual file name of the image or the actual image?
The idea is to put the correct path to the image into the src attribute of the img tag.
The example code I provided may not work as a straight copy as I’m not party to your exact setup, you will probably need to modify it to suit.


#5

I have added a field at the end of the database (i am using phpmyadmin) and setup it like this.

Name - shot
type - MEDIUMBLOB

and nothing else, is this right or is there another way to do this, i have over 500 games on the database at the moment with more to come.

as there is over 500 games i have read that you should not put into the database but use the folder route is this correct?


#6

Blob is used for storing binary data, but you say:-

If the image files are in a folder there is no need to store the image data in the database too. You only need store a reference to the file which will be a string, so may be stored in a text data type column such as varchar or similar.
So for example you have the image file: /images/air-sea-battle.jpg on your server.
All you need to store in the database is the filename as a simple text string: air-sea-battle.jpg

Alternatively you could make the filename a number matching the game’s ID, Eg: 248.jpg then you don’t even need to store any reference to the image, just build the file path from the known folder, the ID number then the extension.
It depends on what naming convention you have for the image files, you probably don’t want to edit 500 image file names to match IDs.


#7

Also as a general point, dont put bulk data (the actual image contents, which is what you’d be potentially looking at with a BLOB) in the database unless it is truly needed.

Hauling the data from the database back to the webserver will be the slowest part of your page rendering (especially if the database is not on the same machine as the webhost).

If the database is serving a single server, keep the images on the disk of the server and load locally (if at all); which is why Sam suggests the things he does.


#8

Thank you luckily i have only one shot at the moment.

so would you suggest the type being a varchar then instead of blob ?

then use the code from above to display them


#9

It’s not that it’s impossible to get the image data from the database (if it’s too much work to alter yuor tables and data) but TBH I don’t know exactly how as it’s something I have never done or thought of doing.
I imagine it involves some kind of base64_encode or suchlike.
I prefer to keep “heavy data” like images out of the database and just reference a file saved on the server for simplicity and efficiency.

It’s not just a case of changing the columns data type, you would also change the data stored to be a file name string.


#10

< kinda-off-topic? >

<img src="data:image/png,base64,$bulkdata">
and then you’d store the encoded image data.

But as said before, it’s not generally recommended for remote storage when a simple file pointer will do.
< /kinda-off-topic >