HomePHP Page 3 - Previous or Next? Paginating Records with PHP, part 4
Creating the database table - PHP
In the fourth and final article in our series about paginating records with PHP, we will look at the source code for the "pager" PHP class we developed in part three, and show several examples to demonstrate possible uses in production environments.
In the first place, we need to define a database and create a table. So, we're going to create a database called "songs," which will contain only one table. The SQL code for creating our "songs" database is as simple as the following line:
CREATE DATABASE songs;
Whew, that was a lot of work! I feel really exhausted. But let's go on and create the "songs" table, using the SQL code listed below:
CREATE TABLE songs ( songid INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, name VARCHAR(30) NOT NULL, author VARCHAR(30) NOT NULL );
As you can see, I've defined a basic table with three fields: "songid," "name" and "author," respectively. The first field, "songid," is the primary key, which I've specified directly. Also, I've decided this will be an integer (notice that its data type is INT), and that this field should be unsigned. As usual, we've taken advantage of the AUTO_INCREMENT capability, so that MySQL can handle this field for us.
The rest of the fields, "name" and "author," are defined to hold string type data, using VARCHAR, to allocate the amount of storage required (plus one byte). The alternative would be using CHAR, which will make room for 30 characters for storage, and generally is faster.
Now, it's time to populate the database with some records. After all, it's pointless to have an empty table. Using a multi-row insertion method, let's add the corresponding records:
INSERT INTO songs VALUES (NULL,"Chariots of fire","Vangelis"), (NULL,"Friends of Mr. Cairo","Vangelis"), (NULL,"Lucifer","The Alan Parson Project"), (NULL,"The eye in the sky","Alan Parson Project"), (NULL,"Caribean Blue","Enya"), (NULL,"Only Time","Enya"), (NULL,"Music","John Miles"), (NULL,"The turn of a friendly card","The Alan Parson Project"), (NULL,"Children","Robert Miles"), (NULL,"One and One","Robert Miles"), (NULL,"Sadness","Enigma"), (NULL,"Mea Culpa","Enigma"), (NULL,"Cherry Blossom Girl","Air"), (NULL,"Hot stuff","Donna Summer"), (NULL,"Bad Girls","Donna Summer"), (NULL,"I will survive","Gloria Gaynor"), (NULL,"Rimes and reasons","John Denver"), (NULL,"Touch and go","Emerson,Lake and Powell"), (NULL,"In Jeopardy","Roger Hogdson"), (NULL,"Ameno","Era");
Utilizing the above SQL code, we've populated the "songs" table with a few records that include information about the song's title and author. Perhaps this might sound like common sense, but before creating any database or tables, make sure you have the proper privileges to perform these operations.
Finally, we have a database table to work with, suitable for testing our pager class. In next section, we'll see several examples to demonstrate the power and flexibility of the class. Let's jump right into the samples.