PHP and MySQL: Formatting URLs correctly when using pagination to display images of multiple galleries.

Question

I've set up a simple gallery system in PHP that is built to display one image per page. My URL parameter simply uses a gallery variable and a page variable and does not use the image iD in any way to pull the image. This is because I'm setting my htaccess settings to make a friendly URL:

http://mysite.com/images/1/

^ Would pull the first image in my MySQL query

or

http//mysite.com/images/12/

^ Would pull the 12th image in my MySQL query

The PHP page would then look like:

images.php?gallery=images&page=1

and

images.php?gallery=images&page=12

The queries (simplified) would then look like this for each of the above:

For the first image:

SELECT id, img_src 
FROM pictures 
WHERE gallery = images
ORDER BY date_added DESC 
LIMIT 0, 1

and for the 12th image:

SELECT id, img_src
FROM pictures 
WHERE gallery = images 
ORDER BY date_added DESC 
LIMIT 11, 1

It's been working great but I ran into a problem now that I want to add a feature. I was hoping to display thumbnails of the ten most recently added images to the database no matter which gallery they belong to… i.e. this query:

SELECT id, gallery, img_src 
FROM pictures
ORDER BY date_added DESC
LIMIT 10

Is there any way I can know which 'position' or 'page' each image would be for the specific gallery so that I can create the link correctly?

For example, say the ten most recent thumbnails return 4 pictures from the gallery 'images', then 2 pictures from the gallery 'weddings', then 3 pictures from the gallery 'portraits' and then one more image from the gallery 'images', so my links should then be:

http://mysite.com/images/1/
http://mysite.com/images/2/
http://mysite.com/images/3/
http://mysite.com/images/4/
http://mysite.com/weddings/1/
http://mysite.com/weddings/2/
http://mysite.com/portraits/1/
http://mysite.com/portraits/2/
http://mysite.com/portraits/3/
http://mysite.com/images/5/

Thanks for any help. I'm sure I'm overlooking something stupid easy here but I'm hoping to do it most efficiently as far as programming goes. So far my thoughts are that when I'm looping through the output I have to somehow retain each gallery's 'image count' and add one to this count each time an image of that gallery is added.

Answer 1

If you're still building the site I would change the URL's to show the actual image ID's and not a pseudo ID based on pagination.

This is good for several reasons:

• SEO. You're pages stay the same. Google indexes them and when a visitor comes to the site at example.com/wedding/4234 he will actually find what he's looking for and not what Google indexed last time your site was scanned

• Simplicity. You're already facing a problem that would be very easilly resolved if you were using real image ID's on the first place.

• Analytics. You'll know what content is driving people to your site. There is no guessing here. URL's will remain the same always (no matter if new content comes in or not).

Answer 2

Quick and dirty:

$sql = "SELECT id, gallery, img_src 
FROM pictures
ORDER BY date_added DESC
LIMIT 10";

$result = mysql_query($sql);

while($row = mysql_fetch_assoc($result)) {
    if(!$$row['gallery']) $$row['gallery'] = array();
    array_push($$row['gallery'], $row['id']);
    $pagenumber = count($$row['gallery']);
    echo "<a href=\"http://mysite.com/{$row['gallery']}/$pagenumber/\"&gt;
        <img src=\"{$row['img_src']}\" alt=\"\"></a><br>";
        // alt attribute for w3 validation (http://validator.w3.org/)
}

Edit:

Fixed code error. Now will dynamically create the array if it doesn't already exist. (Originally, it illegally used the [ ] operator.)

Answer 3

I'm going to add to Frankie's answer, because I also believe the OP's current method is going to bite him the butt again one day. The OP may not find this useful, but it could help someone else.

You can use http://site.com/images/20, but the 20 represents an image ID instead of a page number. Of course the query to get the image is simple:

SELECT id, img_src 
FROM pictures 
WHERE gallery = images
AND id = 20
LIMIT 1

As a benefit this query is more efficient than using an offset with the LIMIT clause. But the OP said in a comment that he likes his current system because it doesn't require additional database queries to help build pagination links. Well, that's not a problem here.

Lets say you're on the page http://site.com/images/20. Your page links would be the following:

Prev - http://site.com/images/prev/20 Next - http://site.com/images/next/20

Obviously it didn't take any additional queries to build those links. They're just a slightly different URL showing the same image ID. But how do they work to move to the next or prev image? Simple. Instead of the above query, you would use one of these:

If the prev link was clicked, you use this query:

SELECT id, img_src
FROM pictures
WHERE gallery = 'images'
AND id < 20
ORDER BY id DESC
LIMIT 1;

If the next link was clicked, you use this query:

SELECT id, img_src
FROM pictures
WHERE gallery = 'images'
AND id > 20
ORDER BY id ASC
LIMIT 1;

The first query gives the image that came before the current one, and the second query gives the image that comes after the current one.