Next and Previous ID in mysql array

How do I get the info from just 1 row before and after a specified row?

[Gahein]

I'm a bit of a newb, but I'm learning a lot very fast.

I have a gallery where each image has it's own unique ID (MediaID) in my MYSQL database table, and they are all in exact numerical order from 1-300.

The page that the image is displayed on is created through a query string, rather, if the link is "gallery/index.php?MediaID=IMG00050" then the image shown on the page is "IMG00050_lg.jpg, naturally. And all of the other information is take from the associated columns.

I want to add a section to the page where users can see the next image in the array and the previous image in the array, and then have them be links to those images. In this case IMG00049 and IMG00051. I know that any image tag or link tag will look something like this:

href=\"/test123/gallery/sngl_img.php?MediaID=" . $nxtlink . "\""

But alas, I know that I can't just subtract 1 or add 1 to change the number, even if that's really all I need to do, all thanks to the "IMG" at the beginning of the ID#.

How do I do this?

[breham]

You could do something like:

$page=50 //get this from your db I guess
$nextpage=$page+1;
$prevpage=$page-1;

href=\"/test123/gallery/sngl_img.php?MediaID=IMG00" . $nextpage . "\""

href=\"/test123/gallery/sngl_img.php?MediaID=IMG00" . $prevpage . "\""

You'd need to do some error checking on $page so that if page=1 don't display the previous link - and if $page=300 - don't display a next link

Brett

[Gahein]

yes, I know how to do that last part, but how do I get the "50" from the Database?

If all of my MediaID's are "IMG" and then five numbers, I need some way to seperate the "IMG" from the numbers.

Otherwise, I need to figure out some way to have the PHP figure out which row in the DB it's on, assuming it corresponds to the MediaID, which it should, and then paste that to the end of the tag.

The only other option, aside from that, that I can think of, is to add another column to my database, with just the numbers so that I can do the function you've just described. I was just hoping there was another way.

BTW, I'm calling the page info with this php

$mediaID = $_REQUEST['MediaID'];
$result = mysql_query("SELECT * FROM Media WHERE MediaID='$mediaID'");
$row = mysql_fetch_array($result);

[OutdoorWebcams]

You could also try:

list($numerical_id) = sscanf [php.net]( $mediaID, "IMG%05d" );
$nextpage = $numerical_id+1;
$prevpage = $numerical_id-1;

[lokeshshettyk]

Try this,

$mediaID = $_REQUEST["MediaID"];
$imageID = intval(substr($mediaID, 6)); // will get you the number from 1 - 300, then
$nxtlink = "IMG000".($imageID + 1);
$prvlink = "IMG000".($imageID - 1);

Hope this helps

Cheers :)

[Gahein]

Okay, one thing that's important to clarify is that when I said I only had 300 images, I meant currently. Over time that number is meant to increase, which is why I left those other two zeros open. So we have plenty of room to expand the gallery.

Thanks a lot for the suggestions so far. The first one was able to get me the number of the image before and after but not in the format that I need. (i.e. IMG00050 gave me $nextpage = 49 and $prevpage = 51) but I needed at least 00049 and 00051 in order for the php to be happy with me. To be honest, I'm not entirely sure why this didn't work. In theory sscanf should be perfect for this kind of job considering the format tool, at least I would think so. hmmm.

The second suggestion worked even better, though. IMG00050 gave me IMG00049 and IMG00051, but because of the nature of the substr(, it would only work for images 11 through 99. (i.e. "00005" gave me "0004" and "0006" with only 4 digits and "00150" gave me "000149" and "000151" with an additional sixth digit.)

I really appreciate the help, though. I can't stop learning this stuff! :]

[OutdoorWebcams]

Try this:

list($numerical_id) = sscanf( $mediaID, "IMG%05d" );
$nextpage = sprintf [php.net]("IMG%05d",$numerical_id+1);
$prevpage = sprintf [php.net]("IMG%05d",$numerical_id-1);

[Gahein]

hey, thanks for all the help! what you had was actually working for a while, and definitely helped me learn lots about php that I didn't know before.

but in the end what I was looking for was more like this:

$mediaID = $_REQUEST['MediaID'];

$prevquery = mysql_query("SELECT MediaID FROM public WHERE MediaID>'$mediaID' AND SortID='$category' ORDER BY MediaID ASC LIMIT 1")
$nextquery = mysql_query("SELECT MediaID FROM public WHERE MediaID<'$mediaID' AND SortID='$category' ORDER BY MediaID DESC LIMIT 1")

$prevrow = mysql_fetch_array($prevquery);
$nextrow = mysql_fetch_array($nextquery);

and then:

<a href=\"/gallery/sngl_img.php?MediaID=".$prevrow['MediaID']."\"><img src=\"http://www.example.com/thumbs/".$prevrow['MediaID']."_sm.jpg\" /></a>

thank you for all your help!

[edited by: eelixduppy at 8:39 pm (utc) on Mar. 16, 2009]
[edit reason] exemplified [/edit]