CODEWITHSUNDEEP
phpmysql
Thew
Thew

Reputation: 15959

Make multiple pages out of a mysql query

So, i have this database right, with some fields called 'id', 'title' and 'message'. Now i got like 700 messages in the database. So all i wanna do, is set a limit of max 50 message title's per page, and make multiple pages... How can i do that?

I only know to get the first page, using LIMIT...

Upvotes: 3

Views: 7334

Answers (2)

Pascal MARTIN
Pascal MARTIN

Reputation: 401032

As you guessed, you have to use the LIMIT keyword.

It accepts two value (quoting) :

  • the offset of the first row to return
  • the maximum number of rows to return


In your case, you'll have to use something like this for the first page :

select * from your_table order by ... limit 0, 50

And, then, for the second page :

select * from your_table order by ... limit 50, 50

And for the third one :

select * from your_table order by ... limit 100, 50

And so on ;-)


Edit after the comment : to get the page number, you'll have to receive it from your URLs, that would look like this :

http://www.example.com/page.php?pagenum=2

Then, you'll calculate the first value for the limit, :

$offset = 50 * intval($_GET['pagenum']);

And inject it in your query :

select * from your_table order by ... limit $offset, 50


Constructing URLs to the differents pages is now a matter of getting URLs such as these :

http://www.example.com/page.php?pagenum=0
http://www.example.com/page.php?pagenum=1
http://www.example.com/page.php?pagenum=2
...

If you know you have 700 elements, and 50 per page, you'll have 700/50 pages ;-)
So, something like this should do the trick :

for ($i=0 ; $i<700/50 ; i++) {
    // Use http://www.example.com/page.php?pagenum=$i as URL
}


Of course, 700 is a value that can probably change, and should not be hard-coded : it should be determined from the database, using a count query :

select count(*) as total
from your_table
...

Upvotes: 10

ChrisJ
ChrisJ

Reputation: 5251

Your PHP file may receive a GET argument being the page number.

Then you do your query with LIMIT ($page_number * $messages_per_page), $messages_per_page (pseudo-code).

$messages_per_page = 50 in your case. $page_number is deduced from a GET argument, after sanitizing, the first page being page number 0.

Upvotes: 0

Related Questions