get rows in the middle of table

mattis

What is the best way to solv this:

I get a list from the db with... let's say 100 rows. But I don't want to show 100 rows directly. I want to do like this:

row 1
row 2
row 3
row 4
row 5

1 2 3 4 5 6 7 8

When I click nr 2 i get row 6 to 10 and with nr 3 I get row 11 to 15 etc...

The best thing I think would be to get only 5 rows from the db. Is there a way to do what I want without looping thogh the whole table?

Thanks //mattis

tbach2

use LIMIT in your query. Each 1 2 3 4 would be links to different values in the LIMIT part of the query.

clearbreach

Just use a select statement like so:

SELECT * FROM tablename LIMIT 0,4

Or something similar, and just change the "LIMIT" part based on what page number the user is on. And, of course, you could determine the number of necessary pages based on the number of results and the number you want per page.

I hope that's what you mean. If so, maybe someone could come and expand on that. I'm really bad at figuring things out on the spot.

mattis

That's exactly what I was looking for, thanks !

I didn't know that about LIMIT. I was only aware of LIMIT 10...

mattis

hmm... I may have been a little to quick.

When I use LIMIT 20,10 I will get 10 row in result. So far so good, but how do I know how many pages there will be? (1 2 3 4 5...)
In some way I have to devide the total numbers of rows with how many rows I want to see?

Do I have to select all rows anyway?

tbach2

heheh, everything gets complicated, doesn't it?

first, do a count of the rows,
(select count(*)...) then when you have that number, things should fall into place.

kevinc

Bear in mind, that if you have 43 results, and you're systematically incrementing the limit values, say, 0,10 , 10,10, 20,10 and so on, PHP will return 5 sets, 4 with 10 rows, 1 with 3 rows. If the "amount per page" as such is relative to the amount of rows, then use count(*), however, careful you don't use this in conjunction with limit, as this will return "10" in the above instances.(With the exception of the last limit.)

The idea here is, you need to first determine the amount of rows in the table, without your limit getting in the way. Take away your WHERE clauses and perform the query. (Or, the "query relative" WHERE clauses.)

You may alternatively research the mysql_affected_rows() function, if count(*) gets a little confusing. Once again, without the specific WHERE clauses.

Hope it helps.

Kev.

Weedpacket

There are also two threads on pagination cited in the FAQ at the top of the forum that covers much of this ground.