Can anyone help me make this more efficient? (More of an SQL question)

[Jezw]

Okay so i have an image gallery script that creates two links, "Next Image" and "Previous Image". The script works but i'd like to know if i could make it more efficent with a better SQL query.

 

Here's my database

 

 

What i am currently doing is querying the database and getting a list of all the image id's in the same gallery, and putting them into an array. Which doesn't seem very logical (but it's the only solution i could come up with).

 

// CREATE AN ARRAY WITH ALL THE IMAGE ID'S IN THE GALLERY
//
	$query = ("SELECT id FROM images WHERE gallery_id=(SELECT gallery_id FROM images WHERE id='".$_REQUEST['id']."') ORDER BY id DESC");
	$result = mysql_query($query);
	$images_in_gallery = array();

	while ($row = mysql_fetch_array($result))
	{
		array_push($images_in_gallery, $row['id']);
	}

 

And then searching through said the array to get the id of the next image and the previous image.

 

// CREATE THE LINK
//
	$next_link = "<a href=\"?id=".$images_in_gallery[array_search($_REQUEST['id'], $images_in_gallery)+1]."\">$next_text</a>";
	$prev_link = "<a href=\"?id=".$images_in_gallery[array_search($_REQUEST['id'], $images_in_gallery)-1]."\">$prev_text</a>";

 

Is there a way i can do this easier with a better query, for example a query that selects the next image in the gallery and the previous, i am not brilliant with SQL but i do know that if you have a good query your code can be considerably shorter.

 

The full code can be found here on pastebin.

http://pastebin.com/GzTpq6Uj

[Adam]

The problem with your method is that you're assuming the next image will always be the ID + or - 1. What if the user adds an image to an older gallery? Your system won't be able to handle it. Instead try to think of it more like pagination, but only showing a single result per page. You get a count of how many images are in the gallery, and then use a parameter to pass the "page" (or better worded as "image") offset in the gallery. Also you can save yourself the sub-query by passing the gallery ID within the URL.

 

So first, get a count of how many images are in the gallery into a variable ($max_images let's say). Then get the current page/image number from the URL (defaulting to 1 if none has been passed - also remember to subtract 1 from this value, as the LIMIT clause is 0-indexed). Then using that return the image data, limited to the current page/image number (i.e. LIMIT $image, 1), and ordered by the image ID. From this point a simple condition on the current page/image number against 0 or $max_images should tell you whether you need a previous and next link respectively.

 

There's a tutorial on the main site covering Basic Pagination that would probably help you.

[Jezw]

The problem with your method is that you're assuming the next image will always be the ID + or - 1.

 

My array search was placed inside $images_in_gallery[]; so basically it's doing $images_in_gallery[5] with 5 being the key and the variable being the value of the key which could be anything.

 

Here's a screenshot showing what i mean, i won't embed it as it's fairly large (337KB PNG):

http://img33.imageshack.us/img33/9020/debugging.png

 

Passing the gallery id through in the URL seems like a very good idea i'll build that in, it also gives me a lot more flexibility with the data. I'll look through the guide you linked now it looks a lot more straight forward than any other paging guides i've seen.