Image database table

[gsencan]

I am showing 20 pics per page in 9 different categories.

So i wanted to use a database for images since it got out of control...

 

I want to create a table for images.The basic thing i want is:

show image name and image from newest to oldest

 

should something like this work?

ID / NAME(image name) / IMAGE PATH / IMAGE URL

 

Or should i store images directly to database ? Any table structure suggestions?

i dont know much about sql. 

 

[emvy03]

Hi,

I'm far from being even a novice at php but I thought I'd add my 2 pennies worth as I recently had a similar issue.

 

I've got a similar system, whereby I have images for many different galleries all with different gallery names. My table has an ID, Gallery name and image path.

 

That way for each different gallery you can put a WHERE clause in the query and only images designated for that gallery are displayed.

 

Then get the images to save in a given directory.

 

To sort the images by date added I guess having a date_added row would be an idea.

 

Again, don't take my word for it as there are far more qualified people on here.

[The Little Guy]

First off, a database isn't really made to be a file system, so I wouldn't store the images in the database. Instead you should store what you would need to access the file. So, your database may be different depending on how you plan on storing the images.

 

usually I have a root image directory, in there other directories, for example:

sub1

sub2

sub3

 

This will spit the load on each directory. If you do that you would need this (example):

directory, image, extension

 

- directory will contain sub1, sub2 or sub3.

- image will be the image name "My_Cool_Image"

- extension will be the extension of the file: jpg, gif, png, etc.

 

now if you store the images all over the place, such as different servers you would probably do something like this:

server, directory, image, extension

 

- server would be the server saved the image on such as "images.srv1"

- directory would be the location on the server "/downloads/user"

- image would be the image name

- extension would be the extension

 

When you build this it would look like so:

http://images.srv1.site.com/downloads/user/My_Cool_Image.jpg

or

http://images.srv5.site.com/usrData/user/1234/profile_pic.jpg

 

Their are lots of ways to store images in the database, and all ways are different, it just depends on what your needs are.

[gsencan]

My images are in 1 folder and image folder has sub folders like category 1 category 2 .....etc.

So i guess it will be easy to show most recent 20 images for any category.

 

Also can i have 1 table with all images and make a column for category name so i can say something like get most recent 20 images by date from category 1 ? Or make tables for each category for speed ? Atm i have about 500 pics with links to 500 pages

[The Little Guy]

don't worry about database speed, I have a database table with about 950,000,000 rows and it can do a query in under a second.

 

So, I would make a table with at least 4 columns:

 

image_id, directory, image, extension

 

you can then add any other fields you would like to that.

[gsencan]

Ok 1 last question about structure

 

it will be like this:

CATEGORY / IMAGENAME / IMAGEPATH / IMAGEURL / DATE(added)

I will only show image name and image by date on site so as i know so far  :

 

id INT NOT NULL AUTO_INCREMENT,

category VARCHAR(20) NOT NULL,

imagename VARCHAR(30) NOT NULL,

imagepath VARCHAR(60) NOT NULL,

imageurl VARCHAR(60) NOT NULL,

date INT  NOT NULL,                      ==> like : 20110914124005

PRIMARY KEY(id)

 

is it ok? 

[The Little Guy]

why do you have imageurl and imagepath? you can build those two based off the other data you have. Second the date field should be "datetime" not an int. it would be formatted like:

2011-12-12 13:12:20

[gsencan]

I see...  so :

id INT NOT NULL AUTO_INCREMENT,

category VARCHAR(20) NOT NULL,

imagename VARCHAR(30) NOT NULL,

date datetime NOT NULL,                     

PRIMARY KEY(id)

 

i was thinking something like adding time manually in 20110814100102 format (because every date-time has a unique number)  but its ridiculous i guess.

 

[The Little Guy]

sorry, datetime can actually be a timestamp with CURRENT_TIMESTAMP as the default

[fenway]

Or, you can use the UID as the filename.