[SOLVED] Retrieving database records with next and previous buttons

[magga]

Hi I currently have a query which pulls out picture information from a database. Underneath is a previous and next button which adds 1/takes away 1 from the 'id' which is the auto_increment column of the database.

The problem is, if for some reason there is an 'id' missing (for example if a picture is removed) then the previous/next button disappears. I know why it does this, I just can't work out how to write the code correctly so it will then carry on and search for the next id in that gallery. I do want the previous/next buttons to disappear however when there IS no previous or next picture.

Hope you can understand what im saying here... here is the code:

[code]<? $previd=$id-'1'; $tempres=mysql_query("SELECT *FROM galleries WHERE gallery='$gallery' AND type='picture' AND id='$previd'");
if($values=mysql_fetch_array($tempres)) echo "<a href='?gallery=$gallery&type=picture&id=".$previd."'><img src='images/previous.gif' border='0'></a>";?>
  <a href="http://www.magga-james.co.uk/galleries.php?gallery=<?=$gallery; ?>#pictures"><img src='images/thumbs.gif' border='0'>
        <?  $nextid=$id+'1'; $tempres=mysql_query("SELECT *FROM galleries WHERE gallery='$gallery' AND type='picture' AND id='$nextid'");
if($values=mysql_fetch_array($tempres)) echo "<a href='?gallery=$gallery&type=picture&id=".$nextid."'><img src='images/next.gif' border='0'></a>";?>[/code]

I did make a while loop so it kept adding +1 to the id until it found the next record but obviously when it did get to the last record, it kept searching and searching for a record that never existed so that's obviously not the solution.

Any help would be appreciated. Thanks in advance.

[fenway]

I'm have no idea why you're using the UID for this.

[magga]

Which way would you go about it ?

If it helps, you can see it in action here:

[url=http://www.magga-james.co.uk/galleries.php?gallery=nye06]www.magga-james.co.uk/galleries.php?gallery=nye06[/url]

[magga]

Ignore.

[sloshire1]

Try getting the count of images to determine your upper and lower bounds for the previous and next buttons.  For your query, try the following:

select * from images order by id asc limit $page,1

This will give you the images in order, but give you an offset into the table that is based on the page number.  Hope this helps.

[magga]

I'm not sure what you mean about $page 1.... all the images for a gallery should be on the same page.

[sloshire1]

I misunderstood, I thought you wanted to go to the next page of the gallery.  The $page variable should be the page number, and the 1 should be the page size.  Maybe this will be a clearer example:

$pageNum = $_GET['PageNumber'];
$pageSize = 20; //Set to your maximum page size.
$offset = $pageSize * $pageNum;
$query = "select * from image order by id asc limit $offset,$pageSize";


The result of this query should be a subset of your images from the gallery to display on the page.  Is this what you are looking for?

[magga]

No, its ok. Thats not really what I'm trying to do take a look at this page and click on one of the pictures:

[url=http://www.magga-james.co.uk/galleries.php?gallery=nye06]www.magga-james.co.uk/galleries.php?gallery=nye06[/url]

The next and previous buttons add + or - to the 'id' which make it display the next/previous picture. Now if there is an id missing for example, the next or previous button disappears. I need to be able for it to carry on adding to the id until it find the next row that matches the query.

I have got this far but then, when it gets to the last picture in the gallery, it keeps searching and searching and searching in an endless loop.

Is there anyway of killing a while statement when it reaches the end of the table ?

[sloshire1]

Ok, got it.  You might try just recalling the next larger and smaller id number.  If there isn't one, you know to stop.  Maybe something like this.

$currentID = $_GET['id'];
$nextQuery = "select id from image where id > $currentID limit 0,1";
$prevQuery = "select id from image where id < $currentID limit 0,1";

The result of each of these queries should be the next ID in the sequence.  Does this help?

[magga]

Ok I thought it was working.....

If you click next it goes to the next available image as expected but if you click previous, it goes back to the first image of the gallery !

Obviously cos its reading the table from the start and the first record it finds, the id is smaller than the current one so thats the one you get to

[magga]

Ok i've sorted it thanks for all your help.

[skyer2000]

[quote author=magga link=topic=121565.msg500124#msg500124 date=1168297401]
Ok i've sorted it thanks for all your help.
[/quote]

How is the issue sorted out with it not grabbing the first image in the gallery?

[skyer2000]

bumping this because I think the "Solved" icon is turning people away from this thread, even though I have a question that goes along with however he solved this

[artacus]

I'm a little late for the party, but I use a custom function I wrote for this purpose. Someone may find it useful.

[code]
function walk($table,$location,$where,$dir=1) {
//Walks through a table and returns either the next item or the previous one
//$dir 1 = walk forward / $dir -1 = walk backwards

//set where_clause
for ($i=0; $i < count($where); $i++) {
if (current($where) == 'IS NOT NULL' || current($where) == 'IS NULL') {
    $where_clause .= " AND ".key($where).' '.current($where);
} else {
    $where_clause .= " AND ".key($where)."='".current($where)."'";
}
next($where);
}

//set direction
if ($dir > 0) {
    $gtlt = '>';
$order = '';
} else {
$gtlt = '<';
$order = 'DESC';
}
$column = key($location);
$position = current($location);
if (!$position) {
    $position = 0;
}

$query = "SELECT $column FROM $table WHERE $column $gtlt $position $where_clause
  ORDER BY $column $order LIMIT 1";

$result = mysql_query($query);
if (mysql_num_rows($result)) {
    return mysql_result($result, 0);
} else {
return 0;
}
}
[/code]

[skyer2000]

DUH!

ORDER BY id DESC gets rid of the problem of it going straight to the first record.

Thanks!