When there's more than one column involved in paging there isn't much that
we need to modify. We only need to decide how to count the total number of
rows we have in the table. Consider the student table. This table have five
columns as shown in the SQL below.
CREATE TABLE student(
id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
name VARCHAR(30) NOT NULL,
address VARCHAR(50) NOT NULL,
age TINYINT UNSIGNED NOT NULL,
register_date DATE NOT NULL,
PRIMARY KEY (id)
);
In the select query we just select all the columns. You can also use SELECT
* instead of mentioning all the column names ( SELECT id, name, address, age,
register_date ). But personally i prefer writing the column names in the query
so that by reading the code i know what the column names in a table without
having to check the database.
<?php
include 'library/config.php';
include 'library/opendb.php';
// how many rows to show per page
$rowsPerPage = 3;
// by default we show first page
$pageNum = 1;
// if $_GET['page'] defined, use it as page number
if(isset($_GET['page']))
{
$pageNum = $_GET['page'];
}
// counting the offset
$offset = ($pageNum - 1) * $rowsPerPage;
$query = "SELECT id, name, address, age, register_date
FROM student
LIMIT $offset, $rowsPerPage";
$result = mysql_query($query) or die('Error, query failed');
// print the student info in table
echo '<table border="1"><tr><td>Student
Id</td><td>Name</td><td>Address</td><td>Age</td><td>Register
Date</td></tr>';
while(list($id, $name, $address, $age, $regdate) = mysql_fetch_array($result))
{
echo "<tr><td>$id</td><td>$name</td><td>$address</td>
<td>$age</td><td>$regdate</td></tr>";
}
echo '</table>';
echo '<br>';
// ... more code here
?>
In this example we print the result in table. Before looping through the
array we just echo the starting table code and the header which displays the
column names. Then in the loop we just print the values in a HTML table row.
The next thing is finding out the total number of rows. There are several
ways to do it. The first one is shown below. It's the same method used in
previous examples. We just use the COUNT() function
<?php
// ... previous code here
// how many rows we have in database
$query = "SELECT COUNT(id) AS numrows FROM student";
$result = mysql_query($query) or die('Error, query failed');
$row = mysql_fetch_array($result, MYSQL_ASSOC);
$numrows = $row['numrows'];
// ... just the same code that prints the prev & next link
?>
You can also count any other columns since they all yield the same result.
So your query can be rewritten into this :
<?php
// ...
$query = "SELECT COUNT(name) AS numrows FROM student";
// ...
?>
Or this :
<?php
// ...
$query = "SELECT COUNT(age) AS numrows FROM student";
// ...
?>
There is another way to count the total rows. Instead of using COUNT() function
in the query you use a simple SELECT <column> and use myql_num_rows()
to see how many rows returned.
Take a look at the code below. We now separate the query into two parts.
One is the normal SELECT query and the second is the SQL that performs the
paging. After finish printing the result you can reuse the first part of the
query to find the total number of rows.
<?php
// ... same old code to get the page number and counting the offset
$query = "SELECT id, name, address, age, register_date
FROM student ";
$pagingQuery = "LIMIT $offset, $rowsPerPage";
$result = mysql_query($query . $pagingQuery) or die('Error,
query failed');
// ... the code that prints the result in a table
// how many rows we have in database
$result = mysql_query($query) or die('Error, query failed');
$numrows = mysql_num_rows($result);
// ... and here is the code that print the prev & next links
?>
There is another advantage in separating the original query with the paging
query. In case you only wish to list all student whose age is older than 15.
You just need to modify the original query and you don't have to worry about
changing the query to find the total number of rows. The example is shown
below :
<?php
// ... same old code to get the page number and counting the offset
$query = "SELECT id, name, address, age, register_date
FROM student
WHERE age > 15";
$pagingQuery = "LIMIT $offset, $rowsPerPage";
$result = mysql_query($query . $pagingQuery) or die('Error, query failed');
// ... the code that prints the result in a table
// how many rows we have in database
$result = mysql_query($query) or die('Error, query failed');
$numrows = mysql_num_rows($result);
// ... and here is the code that print the prev & next links
?>
The disadvantage of this method is that the second execution of mysql_query()
will retrieve all columns from the database. This is very useless since
we're not going to use them. We only interested in finding the total rows
returned by that query. In the end it's really up to you to decide which method
you prefer.