Imagine that you have a recordset containing 100 records, for example. Usually you wouldn't want to display all 100 records at once, as it's far too much information for a web site visitor to take in at once, and it can also make your web pages slow to load. Instead, it's much more desirable to be able to show the user 10 records at a time for example, and let them move back and forth between pages. A perfect example of this is a search engine such as Google, although it can be used on any web site that uses dynamic data.
Advertisement DMXzone Paginator PHP
Add the coolest page navigation to your site and make browsing
through large lists of items or tables easy and quick. Choose from 22
different styles, used by many sites such as Digg, Yahoo, Flickr and
much more, to fit perfectly with your design and display the total
number of items.
All paging styles are fully CSS based, so you can always fine tune the colors and borders to your site design.
Overview
In this tutorial, we first look at creating a page that displayed
the results of a database query, and showing all results returned to
the user. We then look at the MySQL LIMIT command, which returns only
certain records from the results obtained by a query, meaning you only
get the records you are actually going to use, creating faster and more
efficient queries. We use the LIMIT command to adapt our existing code
so that it showed the results in pages of 5 records to a page.
We then create a dynamic navigation bar, which allows the users to
quickly move backwards and forwards between pages. Finally we alter the
code so that if results of a search are being shown, the search
parameters are preserved as the user moves from page to page.
Table of Content:
- What is Recordset Paging?
- Step 1 - Creating some Example Data
- Step 2 - Creating a Database Connection File
- Step 3 - Creating Code to Create and Display a Recordset
- 3.1 Creating the PHP code to read records from the Database
- 3.2 Creating the HTML and PHP to display the Results
- 3.3 Testing the Page
- Step 4 - Adding Recordset Paging
- 4.1 - The MySQL LIMIT command
- 4.2 Adapting the existing code
- 4.3 Creating a Dynamic Navigation Bar
- 4.4 Testing the Complete Page
- 4.5 - Using the Recordset Pages with the Results of a Search
- Summary
Step 2 - Creating a Database Connection File
As always, we'll put the details needed to connect to the
MySQL database in a separate include file, and then
we'll add this file to each page that needs to access the database. This means
in future if the database details change, they only need to be altered in
the include file, rather than on every page which saves a lot of time and
effort.
Open a new PHP page in Dreamweaver MX, and because were
creating a PHP include file, switch into Code View and delete all the existing
DMX generated code. Next, add the following block of code to the page, or
download it as above.
<?php
//
Database Connection Parameters
$dbHost
= "localhost";
$dbUser
= "your_username";
$dbPass
= "your_password";
$dbName
= "database_name";
?>
Code Block 1 -
dbConnection.php Include File
Remember though, that you will need to edit the details
in this file so that it contains your own MySQL
username and password etc.
Save the file as dbConnection.php in a directory called includes.
Now that we have our database connection file, we can move
on and display the data. First, we'll look at how to display the standard
recordset with all records, and then we'll see how
to adapt the code for recordset paging.
Step 3 - Creating Code to Create and Display a Recordset
First, we're going to create the standard PHP Code to read
records from a database. Create a new PHP page in Dreamweaver MX, as save
it as show_all_records.php.
3.1 Creating the PHP code to read records from the Database
Switch into code view, and add the following block of code
to the very top of the page, above the <html> tag.
<?php
//
Include Database Connection File
require_once("C:/webserver/includes/dbConnection.php");
//
Open Database Connection
$dbLink
= mysql_connect($dbHost, $dbUser, $dbPass);
if
(!$dbLink){ die ("Database: Couldn`t connect to mySQL Server");
}
mysql_select_db($dbName,
$dbLink) or die ("Database: Couldn`t open Database");
//
Build SQL query
$sql
= "SELECT UNIX_TIMESTAMP(date) as dateTime,id,title,author ";
$sql
.= "FROM example_data ";
$sql
.= "ORDER BY UNIX_TIMESTAMP(date) DESC";
//
Read in Query Results
$dbResult
= mysql_query($sql, $dbLink) or die ("MySQL Error: " . mysql_error()
);
$numRecords
= mysql_num_rows($dbResult);
$recordset
= "";
for($i=0;$i<$numRecords;$i++){
$recordset[] = mysql_fetch_assoc($dbResult);
}
//
Close Database Connection
mysql_close($dbLink);
?>
Code Block 2 -
PHP Code to read records into a Recordset
As usual, we'll look at each block of code from Code Block
2 above individually.
//
Include Database Connection File
require_once("C:/webserver/includes/dbConnection.php");
First, we add the dbConnection.php
include file that we created earlier, which contains the database connection
parameters.
//
Open Database Connection
$dbLink
= mysql_connect($dbHost, $dbUser, $dbPass);
if
(!$dbLink){ die ("Database: Couldn`t connect to mySQL Server");
}
mysql_select_db($dbName, $dbLink) or die ("Database: Couldn`t open Database");
We then open a connection to the MySQL server using the PHP mysql_connect() command. We check that the connection was successful, and if it wasn't,
we stop the code using the PHP die() command, as there's no point in continuing if we can't connect to
the database.
Next we tell MySQL to change to
the database that we want to use, with the PHP mysql_select_db() command, and again stop the code
if the command fails.
//
Build SQL query
$sql
= "SELECT UNIX_TIMESTAMP(date) as dateTime,id,title,author ";
$sql
.= "FROM example_data ";
$sql .= "ORDER BY UNIX_TIMESTAMP(date) DESC";
The next step is to create the SQL query to read records
from the database. Note that we use the MySQL command
UNIX_TIMESTAMP() passing it the datetime field called date. This changes the format of the
data in the field from the datetime format yy-mm-dd hh:mm:ss to a UNIX timestamp which is an ordinary number (and represents the
number of seconds since the Linux epoch dates). We can now order the data
in descending order using this number, which means that the data is now sorted
in order of date and then time. Turning the datetime
field into a UNIX timestamp also means it can be used directly with other
PHP time and date commands, and is much easier to manipulate.
//
Read in Query Results
$dbResult
= mysql_query($sql, $dbLink) or die ("MySQL Error: " . mysql_error()
);
$numRecords
= mysql_num_rows($dbResult);
$recordset
= "";
for($i=0;$i<$numRecords;$i++){
$recordset[] = mysql_fetch_assoc($dbResult);
}
We then run the SQL query we created using the PHP mysql_query() command, and again stop the code
if the command fails. The mysql_num_rows() command
is then used so that we know how many records have been returned by the query.
Finally we use a PHP for loop which runs once for each record returned by
the query, and reads a record at a time using the mysql_fetch_assoc() command and places it into an array called $recordset.
// Close Database Connection
mysql_close($dbLink);
The final stage is to close the MySQL database connection as it's no longer required, which
we do by using the PHP mysql_close() command.
Now that we have all the records from the database in an
array, we next need to add a mixture of HTML and PHP to display these records
on the web page.
Gareth has a range of skills, covering many computer and internet related subjects. He is proficient in many different languages including ASP and PHP, and is responsible for the setup and maintenance of both Windows and Linux servers on a daily basis.