3 replies to this topic

[greggreg]

hey fellas,
having a little bit of mysql/php woes...

--the quick version--
what im thinking is i need a way to determine what record number or row in the table a perticular "id" is on and then to select the adjacent rows based on this information.

--the longer version--
im building a photo site based around a mysql database and when any given photo is displayed im trying to display thumbnails of the previous and next photos in the table. i want all of the photos to have their own perminent url derived from their auto-incrementing "id" in the mysql table so that even if photos get deleted the url would stay the same. only problem is i cant figure out how to retrieve the photo data from the previous and next records in the table when i search for a specific photo by its "id".


heres a snippet of what im looking at:

id - name - description
----| ----------| ------------
4 | fun.jpg | i am having fun
6 | tom.jpg | tom is makin burgers
10 | alf.jpg | alf is alf

say the url is like index.php?picID=10. i have no problem retrieving picture id=10 and displaing all its info, but since i have no idea what row in the table it is on i cant tell mysql to return the previous and next picture data for dislpay.

thanks so much in advance for any help or comments that may come.

-greg

Edited by greggreg, 21 January 2006 - 04:33 PM.

[Chaos King]

The only way I know how to do this is to run a query that will basicly map out your table. It will put all the id numbers into an array, and then just call out what ever array you want.

<?php

$photo_table = array (
						0 => 501,
						1 => 620,
						2 => 84654,
						);

echo $photo_table[0] .'<br />';
echo $photo_table[1] .'<br />';
echo $photo_table[2] .'<br />';

?>

That is an example of what the array would look like.

And that would display

501
620
84654

Have fun.

Edited by Chaos King, 21 January 2006 - 06:27 PM.

[greggreg]

thanks for the help!

if i were to dump all the "id"s into an array like that one, is there a way to determine what position in the array a specific id is? for example if i knew i wanted to access id number 620 how would i be able to determine it is in the 1st spot in the array so i can retrieve the info for the 0th and 2nd locations?

also, say the database gets 1000+ pictures over time, is this dumping to an array process going to make it substantially slower?

thanks again.

-greg

[rc69]

Well, i've seen mysql insert records, filling the table from the bottom up. If an existing entry was deleted, the record being inserted will take the old entry's slot. So unless you use an ORDER BY clause, you'd never know what's previous, and what's next if you delete a picture.

So, since everything happens to have it's own unique id, i'd simply select their id's, rather then trying to find out which row they happen to be on.

$sql = mysql_query("SELECT * FROM table WHERE id = ".($_GET['picID']-1)." OR id = ".($_GET['picID']+1)." ORDER BY id ASC LIMIT 2");
while($r = mysql_fetch_array($sql)){
  $photos[$r['id'] > $_GET['picID'] ? 'next' : 'prev'] = $r;
}

die(highlight_string(print_r($photos,1),1));

Edit: To answer your second question. Yes, it will.
If you still want to do it, take a look at php.net's array section: http://php.net/manual/en/ref.array.php

Edited by rc69, 21 January 2006 - 06:49 PM.