Scrolling through records retrieved from database

londondude1

Hello, I was wondering if someone could give me the basic idea of how to retrieve records from a MYSQL database and then scroll through them five or ten at a time.

I understand the basics ( how to connect to the database, how to insert and retrieve records ) What I can't seem to understand is how to retrieve the records, and then display them five at a time.

Its the scrolling aspect I want to focus on.

I don't necessarily need code. Just an explanation. Thanks!!

ANDY

nemonoman

one way is to use LIMIT at the end of your query

WHERE id=1
LIMIT 0, 5 //first 5 records

LIMIT 5, 10 // records 5-15
etc.

Here's a typical strategy

You'd display the first 5 number of records in a table.
(LIMIT 0, 5)

Add links at the end of the table (<a href=mysearch.php?nextrecord=5>NEXT 5 RECORDS</a>

Then take the variable $nextrecord and use it to create a new LIMIT clause

LIMIT $nextrecord, 5

You'd write a new NEXT link (nextrecord=10) and, probably a PREVIOUS link.

etc.

Make sense?

londondude1

So that is what Limit 0, 5 means!! I never even really thought of that.

Awesome thanks!!

Im assuming that in the SQL statement you can add a LIMIT $var,$var1 instead of the actual numbers.

That should do it!

Thanks ANDY

Appreciate it.

🙂 🙂 🙂 🙂 🙂

nelsons

This is great - such a simple solution.

What if we limit the query to 5, and there are only 5 in the DB.? Then using this method, we would refer the user to the second page, - supposedly 6 - 10 with no data to be had.

Looking for an efficient method to display Next == only if records or rows actually exist for the next..

nemonoman

You'll have to get the number of results ahead of time, and consider that information in coditionals as you write your page.

nelsons

Agreed, that would be the way to do it - but how? By virtue of limiting the results - for example 5. Then the records or rows returned are 5 as well arent they?

In a circumstance like this, would one do 2 queries, and just grab the numbers off the first query? And then not display any of those records or rows of the first query?

Seems that 2 queries might be heavier than what would be needed. Is there a generic standard method that would be considered common in a circumstance like this?

Am perusing through 4 different php books/manuals and am not finding anything that addresses this - this way.

nemonoman

$result = mysql_query("SELECT * FROM table1 limit 1, 5");
$num_rows = mysql_num_rows($result);

if($num_rows>5) {echo "<a href=index.php?getmore=true&nextrow=6>Next 5 records</a>";}

//After that, use mysql_fetch_array to get the 5 results and display them.

nelsons

Thanks, It's obvious now - Just needed a good example.

cgraz

Here's a commented example I wrote for somebody. http://www.phpbuilder.com/board/showthread.php?s=&threadid=10219415

I had a hard time figuring out how that worked and getting it to work, even searching through forums and online tutorials.

I hope that helps!

Cgraz