[RESOLVED] Advice on retrieving MySQL records

dj2ball

Hey guys.

In my application at the moment, I have a simple SELECT to pull the lat 10 entries from my db, which works well.

$record = mysql_query("SELECT username, message, created_at, competition_cat, prize_cat FROM web_posts ORDER BY created_at DESC LIMIT 10");

I'm trying to figure out a way to implement a button for ' Previous 10' - ie. the 10 records before the ones just pulled out.

I'm new to PHP so I don't want the code itself, but an idea on how you would approach it...

Thanks

halojoy

you use
LIMIT 0, 10 ...... same as your query, returns 10 last: 1-10
LIMIT 10, 10 ...... returns 10 before .............. ie 11-20
LIMIT 20, 10 ..... returns 10 before that ie result rows: 21-30

You can use variables $start, $num, where $num is how many to display each time

<?php 

$start = 10; $num = 10;
// gets the 10 before the last 10
$record = mysql_query("SELECT username, message, created_at, competition_cat, prize_cat 
FROM web_posts 
ORDER BY created_at DESC LIMIT $start, $num"); 

?>

You can make link
<a href="display.php?start=0">First</a>
<a href="display.php?start=1">Previous</a>
<a href="display.php?start=2">Previous2</a>

$start = 10 * $_GET['start'];
$num = 10;

$record = mysql_query("SELECT username, message, created_at, competition_cat, prize_cat 
FROM web_posts 
ORDER BY created_at DESC LIMIT $start, $num");

dj2ball

Brilliant, thanks for your help.

This is the most helpful forum I've ever been on!

laserlight

dj2ball wrote:
I'm new to PHP so I don't want the code itself, but an idea on how you would approach it...

Do you want to implement pagination or just next/previous 10 links?

If you want pagination, then the basic idea is to do a little computation with a page number and entries per page value to set the LIMIT clause. This is what halojoy was getting at.

If you want next/previous 10 links only, and if the created_at column has a unique constraint, then you can implement a more efficient solution. Read this SQLite wiki page on scrolling cursors. Although written for SQLite, it can apply for other relational database systems, including MySQL.