Page 1 of 1

Prev & Next links

Posted: Thu Oct 16, 2003 6:52 am
by Joackim
I'm rebuling a webshop that uses textfiles to a databasedriven shop..

I can't figure out how to make a

prev link and a next link

The list of items is sorted by category and title so I cant, just use
"... WHERE id < ".$currentid

Any ideas?

Posted: Thu Oct 16, 2003 6:59 am
by twigletmac
You need to limit the number of records returned from the database by specifying the record to start with (this is not related to the record's ID) and the number of records to return. Check out the LIMIT clause in MySQL's documentation (assuming that's the database you're using of course):
http://www.mysql.com/doc/en/SELECT.html

Mac

Posted: Thu Oct 16, 2003 7:36 am
by Joackim
Well of course, my problem is to write a WHERE-clause that gives me the right record.
So far I've come up with the following SQL query

SELECT id, title FROM art_info WHERE title < '".mysql_escape_string($title)."' AND cat_id = '".$cat."' ORDER BY title ASC LIMIT 0,1"

But it doesn't return the right one.. :(

Posted: Thu Oct 16, 2003 7:48 am
by Nay
mMm.....where does $title and $cat come from? I'm guessin it's from a URL. Do it somewhat like:

Code: Select all

$q = "SELECT * FROM art_info WHERE title = '{$_GET['title']}' AND cat_id = '{$_GET['cat']}' ORDER BY title ASC LIMIT 0, 1";
-Nay

Posted: Thu Oct 16, 2003 8:03 am
by Joackim
Oh sorry about that, I forgot to explain them...

$title and $cat are found in details.php in which I show some info about the selected record.

I want the record found before the current and the one after.

Just like in the PHP-manual where you can click your way to the previous or next function in a certain category.

See http://www.php.net/manual/en/function.array-sum.php

I hope this makes things a bit more clear..

Posted: Thu Oct 16, 2003 8:50 am
by twigletmac

Code: Select all

SELECT id, title FROM art_info WHERE title &lt; '".mysql_escape_string($title)."' AND cat_id = '".$cat."' ORDER BY title ASC LIMIT 0,1"
are you only trying to return one record? That SQL query will only ever return the first record where title == $title and cat_id == $cat. I don't know your database structure but would it be possible to do the query something like:

Code: Select all

SELECT id, title FROM art_info WHERE cat_id=$cat ORDER BY title ASC LIMIT $limit_start, 1
You'll need to pass $limit_start somehow, maybe through the query string in which case before the query you can do:

Code: Select all

$limit_start = (!empty($GET['limit_start']) && is_numeric($_GET['limit_start'])) ? $_GET['limit_start'] : 0;
then you need to give values to the next and previous links, maybe something like

Code: Select all

if ($limit_start > 0) {
     $prev = $limit_start - 1;
     echo '<a href="page.php?limit_start='.$prev.'"><<< Previous</a>&nbsp;&nbsp;';
}
$next = $limit_start + 1;
     echo '<a href="page.php?limit_start='.$next.'">Next >>></a>';
Mac

Posted: Thu Oct 16, 2003 9:19 am
by Joackim
Is there no way to determine what record the is before the current when list is sorted by title?

Or should I describe my problem once again prehaps..

I have a listing of my category based on a column, cat_id in the table art_info.
From that list you can jump to a new page that shows detailed info about a specific record.
Now I want to get the record found before my current record and create a link to that one.
As if I jumped back to the list and clicked the record above.

I also want to be able to sort the list by title. How do I do that? :?

Posted: Thu Oct 16, 2003 9:37 am
by liljester
ok... so you want PREV and NEXT links.. ive never tried just single records at a time... but here is what im thinking

you'll need to know the current record (duh) which is the one that is currently being viewed. when the next button is clicked all you need to pass that it was the next button that was clicked, and the current record id.

Code: Select all

<?php
if($action == "Next") { $query = "SELECT * FROM table WHERE id > current_record ORDER BY id ASC LIMIT 0,1"; }

if($action == "Prev") { $query = "SELECT * FROM table WHERE id < current_record ORDER BY id ASC LIMIT 0,1"; }
?>
doing somehting like this will get you the next or prev record and return it.

Posted: Thu Oct 16, 2003 9:48 am
by Joackim
Thou.. there is a problem with that..

my list gets generated by

Code: Select all

<?php
$query = "SELECT * FROM art_info WHERE cat_id = '".$_GET['cat']."' ORDER BY title";
?>
So, id may not be in right order..

Posted: Thu Oct 16, 2003 9:51 am
by liljester
to avoid groping in the dark even more, could you post the snippet of your code that you need help with?

Posted: Thu Oct 16, 2003 10:02 am
by Joackim
From the orginal german shop...

Code: Select all

<?php

/* My own comments... 
$title = $art[$i][2]
*/

if (($art[$i-1][1])&&($art[$i-1][2])): // Zum vorigen Artikel
echo
"<< <a href="details.php?id=$id&lan=$lan&show=".rawurlencode($art[$i-1][1].$art[$i-1][2])."&no_cache=".md5(uniqid(rand()))."" target="$maintarget" class="small">".$art[$i-1][2]." </a>";
else:
$l=((count($zeile))-1);
echo
"<< <a href="details.php?id=$id&lan=$lan&show=".rawurlencode($art[$l][1].$art[$l][2])."&no_cache=".md5(uniqid(rand()))."" target="$maintarget" class="small">".$art[$l][2]."</a>";
endif;

echo
"</td><td class="main-head" align="right">";

if (($art[$i+1][1])&&($art[$i+1][2])): // Zum naechsten Artikel
echo
"<a href="details.php?id=$id&lan=$lan&show=".rawurlencode($art[$i+1][1].$art[$i+1][2])."&no_cache=".md5(uniqid(rand()))."" target="$maintarget" class="small">".$art[$i+1][2]."</a> >>";
elseif ($i=(count($zeile))):
echo
"<a href="details.php?id=$id&lan=$lan&show=".rawurlencode($art[0][1].$art[0][2])."&no_cache=".md5(uniqid(rand()))."" target="$maintarget" class="small">".$art[$i-$i][2]."</a> >>";
endif;

?>