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
?>
0 comments:
Post a Comment