PHP/MySQL Pagination

Question

Hey, I am new to php and sql, and i have one tiny question about how to realize sql query , that can:

• Take for example 5 entries from DB, insert them on 1st page (1-5)
• Than take next 5 entries from same DB and insert them on another page (5-10)
  and so on :)

Thank you )

Answer 1

SELECT col FROM table LIMIT  0,5; -- First page, rows 1-5
SELECT col FROM table LIMIT  5,5; -- Second page, rows 6-10
SELECT col FROM table LIMIT 10,5; -- Third page, rows 11-15

Read the LIMIT section on the MySQL SELECT helppage. If you want to display the total number of rows available, you can either do an extra count, or use the ROW_COUNT function.

Answer 2

There are several ways to do this, I think the fastest performance is following one:

$ItemsPerQuery = 5;
$CurrentPage = 0; //here you can add the current site through $_GET array or other ways, but don't forget to use mysql_real_escape_string(), because of mysql injection 

$Query = mysql_query("SELECT * FROM table LIMIT ".($ItemsPerQuery*$CurrentPage).",".$ItemsPerQuery);

while($row = mysql_fetch_assoc($query))
{
    echo $row['column_name'];
}

Stuff you should use:

• mysql_real_escape_string()
• mysql_fetch_assoc()

Answer 3

This should help:

Pagination of MySQL Query Results

Answer 4

If your query does not return a prohibitive amount of results, consider using Jquery to page it with a Tablesorter function. It takes a properly formatted HTML table and pages, sorts, etc on the fly...no need for additional query variables if you don't wish. It's SO much easier than going the full-blown PHP paging route, and is much faster for the user.

My personal favorite example is here: http://datatables.net/

It may not fully suit your needs, but it is cool for many applications. If you do it, just don't forget to format your table with thead and tbody so that it can properly pick up the table elements.