Php Mysql Pagination Tutorial

Paginating MySQL results with PHP has a number of benefits. First, it speeds up the MySQL query, as it only returns a small number of results. Second, it improves the user's experience, as it shows a manageable number of results per page. Third, it saves bandwidth by not sending unneeded results. For these reasons, many sites, including Google, Yahoo and WordPress, have adopted its use. To use MySQL pagination, you need to use the SQL "LIMIT" command and the "GET" parameters in the URL.

Instructions

1. Create a table "phonebook" in your MySQL database with the columns "id," "name" and "telephone." Populate the table with a few entries. Here are the SQL queries for creating and populating the table, respectively:

CREATE TABLE phonebook (`id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(100) NOT NULL, `telephone` varchar(20) NOT NULL, PRIMARY KEY (`id`));

INSERT INTO phonebook (`name`, `telephone`) VALUES ('Jack', '201-379-5406'), ('Jake', '331-123-5196'), ('John', '222-342-3250');

2. Create a new file "pagination.php" and open it with a text editor such as Notepad. Write a segment of code that connects to your MySQL server, using PHP's "mysql_connect" function, and selects the database containing the "phonebook" table, using PHP's "mysql_select_db" function. Here is an example:

mysql_connect('localhost', 'admin', 'password');

mysql_select_db('test');

Replace the four strings with your host name, user name, password and database, respectively.

3. Write a segment of code that sets the variable "$rowsPerPage" to the number of rows to be displayed per page, sets "$currentPage" to the page requested via "$_GET['page']" or zero if none and sets "$offset" to the offset from which to fetch rows from the database, calculated by subtracting one from "$currentPage" and multiplying by "$rowsPerPage." Here is an example:

$rowsPerPage = 5;

$currentPage = ((isset($_GET['page']) && $_GET['page'] > 0) ? (int)$_GET['page'] : 1);

$offset = ($currentPage-1)*$rowsPerPage;

4. Write a line of code that retrieves "$rowsPerPage" rows starting from the offset set in "$offset" from the table "phonebook" by executing a "SELECT" query via PHP's "mysql_query" function and saves the result into a new variable. Here is the code:

$result = mysql_query('SELECT * FROM phonebook LIMIT ' . $offset . ', ' . $rowsPerPage);

5. Write a "while" loop that retrieves the rows from the query's result set, "$result," using PHP's "mysql_fetch_assoc" function and displays them as a list or table. Here is an example:

echo 'IDNamePhone Number';