How to layout mysql for simply gallery

[farkewie]

hi i am creating a simple gallery for my personal photos. there will be nousers gallerys only people will login and look.

 

i want to leave it open for extending in the future though.

 

how should i create my tables?

 

at the moment when my script creates all the thumbnail fo an album it inserts all data into 1 tabe "albums"

 

it has the rows

 

id, album, image, thumb, date.

 

to me this seems quit limiting. but i cant think of the best way to lay it out also keeping performance in mind. as there will be a lot of photos. and one day comments and things of the like.

[farkewie]

ive tried googling it but cant find anything helpful... does anyone know where to look for this kind of advise?

[Dragen]

I'd stire all of the information for the image itself in one table.

you could also record a description of the image..

 

If your wanting comments at a later date, they can easily be added later as a separate table.

something with columns like:

id, image_id, album, user_id, date.

where the image id corresponds to the id in the 'albums' table for that image, and user_id is obviously the id of the user who left the comment. (I'd advise against using the username to check users, as names can change)

[farkewie]

so do i create a new table for each album?

 

so i would have

 

table = album1 cols = , id, imagepath , imagethumbpath, date, description

table = album2 cols = , id, imagepath , imagethumbpath, date, description

 

do i store theimage path or just name in the db?

 

at the moment i have a congih file that set the direcotory to the root path and sore the rest of the path in the db

 

eg

 

<?php

 

$gal_dir = "mygal";

 

$root = "http://".$_SERVER['HTTP_HOST']."/".$gal_dir."/";

 

//then to call the images

 

echo <img src=\"".$root.$row['imagepath']."\" alt=\"my text\"/>

 

?>

[Dragen]

no, just use one table for all the galleries.

table = album; cols = id, album, imagepath , imagethumbpath, date, description

 

Name the table whatever you like. The column `album` will contain the album name that the image belongs to.

You could just save the image name in the database (i.e: 'image01.png'). and ouput it as you have.

 

To output all the image in the gallery you could try something like this (this code is very basic):

<?php
$album = 'mygal';
$root = 'http://' . $_SERVER['HTTP_HOST'] . '/' . $album . '/';

$sql = "SELECT * FROM `album` WHERE `album` = '" . $album . "'";
	if($sqlres = mysql_query($sql)){
		if(mysql_num_rows($sqlres) > 0){
			while($row - mysql_fetch_assoc($sqlres)){
				echo '<img src="' . $root . $row['imagepath'] . '" alt="my text"/><br />';
			}
		}else{
			echo 'no images found in that album';
		}
	}else{
		echo mysql_error();
	}
?>