View Full Version : Using PHP & MySQL to display images...

09-07-2005, 03:03 AM
I am stuck on this one issue. I have created a database of images that users can upload images to along with information about each photo, such as a title, location, city, etc. I have also built a CMS tool that allows me to accept or deny each photo, a field which I called "published".

What I need to do is have a page that displays the most recent image in the database and then work backwards and only show images that are published. If I knew the exact number of photos this would be easy but photos are going to be contiually added to the database and I want to make sure that the query that I write doesn't tax the server. There could end up being 10,000 images in the database so I want to write it properly. If there is a huge amount I would think that having a query that asks for every image that is published would not be a good idea.

I am thinking that it should grab the most recent image with its info first, then when you press next, it will display the image before that with its info, and so on. Should I use a LIMIT and have it grab a certain amount at a time? Say 20 or 50? And if I do that, how would I store all that info while the user is navigating through?

Any help would be much appreciated.

09-07-2005, 04:32 AM
I think the best way to go here would be ensuring that every image has a unique ID number (if they do not already). That is, a field in the images table marked as "auto_increment" and "UNIQUE".

This would ensure that you can always target a specific image, and in the case of navigating through a set of images this makes it much easier to know exactly which image to pick.

Also, if you are going to have 10,000 results then you definatley will want to use a LIMIT. While the time taken for MySQL to select from 10,000 is going to be a fraction of a second (MySQL selecting 12,242 rows from my local development server took 0.165 seconds), getting the query time down lower is definatley a plus.

If you are going to be showing only 1 image per page, then I would reccomend only load 1 image per page, instead of load 50 and then try and find some way of storing them. Im not 100% what you meant here but it sounded like 1 image per page.

If you are showing 1 image per page, and you have your auto incrmement field in the database, then you can store the image ID in the page URL (eg: www.website.com/viewimage.php?imageid=123). Then, when your users click the next image link (assuming thats how its going to work), your query from the database should select the next image after imageid (eg:

$data = mysql_query("SELECT * FROM images WHERE imageid > ".mysql_real_escape_string($_GET['imageid'])." ORDER BY imageid ASC LIMIT 1");

(Take not of mysql_real_escape_string(), its important to stop people trying anything funny with your script.)

So in the example, when the user clicks the next image link, PHP will load the next greatest ID number from the database, after their current ID number (so they dont view the same one twice). Then, when you display that image, the link to the next page should contain imageid=the newly loaded image id in the query string (eg: www.website.com/viewimage.php?imageid=456), which would then mean that clicking on that link would again show them the next image.

Hope that helps you somewhat

09-07-2005, 07:07 AM
If you insert the date it was submitted too, you could show the most recent image doing something like this

$recent = mysql_query("SELECT * from images order by date desc LIMIT 1 ");
Then you could use some sort of pagination.