How would I achieve this type of pagination?

[Jeffro]

I have a php page that's showing the top result in this manner:

$result = mysql_query("SELECT * FROM cities WHERE url LIKE '%$atlanta%' LIMIT 1")

 

Works great.  What I'm wondering... 

 

On the page the result is displaying on..  below the result, can I make a link that says "View Next Result" and on click, have it bring up the next record in the db?  How might I achieve showing that next result... one after another.. until the end? 

[QuickOldCar]

http://www.phpfreaks.com/tutorial/basic-pagination

[Jeffro]

http://www.phpfreaks.com/tutorial/basic-pagination

 

A whole pagination script?   

 

This did the trick.. 

$sql = "SELECT * FROM mytable WHERE id='<?=$_POST['id']?>'";

which let me print..

<a href='?id=<?=db["id"]+1?>'>Next Entry</a>

[xyph]

If your site gets popular enough, rather than re-query the DB using like (slow) for every page, store the results in a session variable, with a unique token. Pass that token along with your page number, and garb the desired results from the session array, rather than the database.

[Jeffro]

If your site gets popular enough, rather than re-query the DB using like (slow) for every page, store the results in a session variable, with a unique token. Pass that token along with your page number, and garb the desired results from the session array, rather than the database.

 

I'll look into what you said... not nearly enough experience to understand it right now... but I'm slowly figuring it out.  I definitely want the code to be the most efficient so I'll be trying to dig into this approach.

 

Thanks!

[Psycho]

A whole pagination script?   

 

This did the trick.. 

$sql = "SELECT * FROM mytable WHERE id='<?=$_POST['id']?>'";

which let me print..

<a href='?id=<?=db["id"]+1?>'>Next Entry</a>

 

Um, yeah . . . a whole pagination script. The only thing different between what you want and what typically constitutes a pagination script is the number of records per page. Whether you want 100 or 1 records on a page, it is still pagination. Your "trick" is invalid it simply shows the record which has an ID that is 1 greater than the last. There are several problems with that: 1) Such a record may not exist, 2) Your original requirement was to show records that match a specific criteria (i.e the URL value). How do you know the record with the next highest id matches that criteria?

 

Yes, you could do a simpler solution, but why? There is already logic for pagination that is easily implemented. If you wanted to do a different solution, then you need to build it from scratch. Off the top of my head you could always retrieve 2 records - the current record and the next record. You the first result to display on the page and the second to create the next link. But, what if you need a back link. Well, you could pass the current id into the next page to use for a previous, but then you need to account for the first page that won't have a previous link.

[Jeffro]

A whole pagination script?   

 

This did the trick.. 

$sql = "SELECT * FROM mytable WHERE id='<?=$_POST['id']?>'";

which let me print..

<a href='?id=<?=db["id"]+1?>'>Next Entry</a>

 

Um, yeah . . . a whole pagination script. The only thing different between what you want and what typically constitutes a pagination script is the number of records per page. Whether you want 100 or 1 records on a page, it is still pagination. Your "trick" is invalid it simply shows the record which has an ID that is 1 greater than the last. There are several problems with that: 1) Such a record may not exist, 2) Your original requirement was to show records that match a specific criteria (i.e the URL value). How do you know the record with the next highest id matches that criteria?

 

Yes, you could do a simpler solution, but why? There is already logic for pagination that is easily implemented. If you wanted to do a different solution, then you need to build it from scratch. Off the top of my head you could always retrieve 2 records - the current record and the next record. You the first result to display on the page and the second to create the next link. But, what if you need a back link. Well, you could pass the current id into the next page to use for a previous, but then you need to account for the first page that won't have a previous link.

 

cool... thx dude.  that's the kind of info I was looking for. 

[Jeffro]

How about this?

 

$items_per_page = 1;
$page = max( 1, (int) $_GET['page'] );
$offset = ($page-1) * $items_per_page;
$limit = $items_per_page;

$sql = "SELECT * FROM 'mytable' LIMIT {$offset}, {$limit}"
http://domain.com/index.php?page=1
http://domain.com/index.php?page=2
http://domain.com/index.php?page=3

[xyph]

Um, yeah . . . a whole pagination script. The only thing different between what you want and what typically constitutes a pagination script is the number of records per page. Whether you want 100 or 1 records on a page, it is still pagination. Your "trick" is invalid it simply shows the record which has an ID that is 1 greater than the last. There are several problems with that: 1) Such a record may not exist, 2) Your original requirement was to show records that match a specific criteria (i.e the URL value). How do you know the record with the next highest id matches that criteria?

 

Yes, you could do a simpler solution, but why? There is already logic for pagination that is easily implemented. If you wanted to do a different solution, then you need to build it from scratch. Off the top of my head you could always retrieve 2 records - the current record and the next record. You the first result to display on the page and the second to create the next link. But, what if you need a back link. Well, you could pass the current id into the next page to use for a previous, but then you need to account for the first page that won't have a previous link.

 

A better way to do this is something like

 

<a href='?id=<?php echo db["id"] ?>&direction=next'>Next Entry</a>

 

Then

 

 $query = 'SELECT `whatever` FROM `table` WHERE `id` > '.sanitize($_POST['id']).' ORDER BY `id` ASC LIMIT 1 

 

Or previous

 

 $query = 'SELECT `whatever` FROM `table` WHERE `id` < '.sanitize($_POST['id']).' ORDER BY `id` DESC LIMIT 1 

[jcbones]

<a href='?id=<?=db["id"]+1?>'>Next Entry</a>

 

What happens when a link gets deleted, and the +1 id value doesn't exist?  It is better to limit the query by the rows, than to blindly pull a row that may or may not exist.