5 replies to this topic

[Mr. Matt]

Ok say i am displaying image with an ID of 10 on a page. I want to select the image next in line and the previous image in the database.

Is there a way to do this in a query? I thought i had a way using the LIMIT but it didnt work out

Any ideas?

Thanks

[someguy]

If I understand you correctly, I think what you need to do is find out what the next id is by selecting the id in a select query, then adding 1 to it. For example:

<?
include("db.php");
$query = mysql_query("SELECT * FROM images ORDER BY id DESC") or die (mysql_error());
while($r = mysql_fetch_array($query)){
 $id = $r['id'];
 $next = $id + 1;
 $prev = $id - 1;
}
?>

[Lang]

someguy, on Mar 20 2006, 11:31 AM, said:

If I understand you correctly, I think what you need to do is find out what the next id is by selecting the id in a select query, then adding 1 to it. For example:

<?
include("db.php");
$query = mysql_query("SELECT * FROM images ORDER BY id DESC") or die (mysql_error());
while($r = mysql_fetch_array($query)){
 $id = $r['id'];
 $next = $id + 1;
 $prev = $id - 1;
}
?>

Unfortunately this code has a flaw. What happens if you delete a picture? Then the chain is broken! What you have to do is have a variable. And yes you will use a limit!

SELECT * FROM images ORDER BY id LIMIT $pictureNumber, 1

Now your links will be:

<a href="image.php?pictureNumber=1">Previous</a> | <a href="image.php?pictureNumber=3">Next</a>

In the LIMIT statement the first number is the record to start showing. The second is how many to show. Hope this helps!

[Mr. Matt]

that wouldnt and doesnt work either as:

Quote

<a href="image.php?pictureNumber=1">Previous</a> | <a href="image.php?pictureNumber=3">Next</a>

all that is doing is then telling it to pull either the picture with number ID 1 or 3 out of the database which isnt what i am after.

What i am more after i think is doing a loop which checks through the values one by one for the previous or next result in the database, i think there was a post somewhere on here but i cant find it

Edited by deadly, 21 March 2006 - 03:43 AM.

[rc69]

There was a post about this before. I can't find it either.
The simplest thing i can think to do would be to run a query to select the first and next ids.

$sql1 = mysql_query("SELECT id FROM images WHERE id > ".$cur_pic_id." ORDER BY id ASC LIMIT 1");
$sql2 = mysql_query("SELECT id FROM images WHERE id < ".$cur_pic_id." ORDER BY id DESC LIMIT 1");

Then just get the id's of the pictures from the results. Keep in mind, there may be a better solution, i just can't think of one right now. Also, i'm not sure of the exact order the results are returned in matters, but if they aren't ordered then you could potentially select any random id.

[Mr. Matt]

that is exactly what i am looking for, thanks mate!