Pagination of Data using PHP-MySQL

Pagination of Data (Paging) using PHP (Hyper Text Pre Processor) and MySQL (the great database)

 

Note: I wrote this post first on October , 2007 , nearly 3 years back and since then this post got a lot of audiences. Surely this topic is one of the good ones for the newbies. After reading the responses for this post I saw that users are facing some errors and after a long while I got some time to have a second look. So I have again installed PHP, APACHE, MYSQL and MYSQL WorkBench in my local machine and tested the script like a user. I have found some new bugs and made the modifications accordingly. Now the Script is completely new and tested fully.

To simplify I tried to use some out of box functionality so that one can run the example right away.

For the sample database I have used “SAKILA” database from MySQL. I have only used the “Customer” table for the example, but this can be extended to a bigger example. Hope you’ll find this useful.

First: Install the SAKILA Database to your local MySQL instance. You can download it from here. We will be using the CUSTOMER table. Here is the table for your reference:

CREATE TABLE customer ( customer_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT, store_id TINYINT UNSIGNED NOT NULL, first_name VARCHAR(45) NOT NULL, last_name VARCHAR(45) NOT NULL, email VARCHAR(50) DEFAULT NULL, address_id SMALLINT UNSIGNED NOT NULL, active BOOLEAN NOT NULL DEFAULT TRUE, create_date DATETIME NOT NULL, last_update TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (customer_id), KEY idx_fk_store_id (store_id), KEY idx_fk_address_id (address_id), KEY idx_last_name (last_name), CONSTRAINT fk_customer_address FOREIGN KEY (address_id) REFERENCES address (address_id) ON DELETE RESTRICT ON UPDATE CASCADE, CONSTRAINT fk_customer_store FOREIGN KEY (store_id) REFERENCES store (store_id) ON DELETE RESTRICT ON UPDATE CASCADE )ENGINE=InnoDB DEFAULT CHARSET=utf8;

Second: This table already have a lot of data. You can verify it by the simple select command.

Initially it has nearly 599 rows. A very good amount of data to test our code.

Third: Create and Test this script

Create a file named pagination.php and put the below contents to that file. Upload that file to your local server or main server.

I have uploaded that on my local server where php is running. If you are testing this script elsewhere then change http://localhost with your server host name.

You’ll see that I have used a variable called $page_name to hold the page name with full address. This is because the link to the page is being used multiple times inside the coding. So, only one place to change your page name, rest will be taken care of automatically.

<?php //Global Variable:  $page_name = “http://localhost/pagination.php”; //REMEMBER TO CONNECT TO DATABASE! $link = mysql_connect(‘localhost’,'root’,'rootpassword’) or die(“Error connecting to Database: ” . mysql_error()); mysql_select_db(‘sakila’) or die(“Error Selecting Database: ” . mysql_error()); //**EDIT TO YOUR TABLE NAME, ETC. $t = mysql_query(“SELECT count(*) as cnt FROM customer”); if(!$t){ die(“Error quering the Database: ” . mysql_error()); } $a = mysql_fetch_object($t); $total_items = $a->cnt; echo “Total Number of records in Database: “.$total_items; echo “<br/>”; $limit= (isset($_GET["limit"])) ? $_GET["limit"] : 10; $page= (isset($_GET["page"]))? $_GET["page"] : 1; echo “This is Page Number: ” . $page . “<br/>”; echo “Current Limit: “. $limit. “<br/>”; //Set defaults if: $limit is empty, non numerical, //less than 10, greater than 50 if((!$limit) || (is_numeric($limit) == false) || ($limit < 10) || ($limit > 50)) { $limit = 10; //default } //Set defaults if: $page is empty, non numerical, //less than zero, greater than total available if((!$page) || (is_numeric($page) == false) || ($page < 0) || ($page > $total_items)) { $page = 1; //default } //calculate total pages $total_pages = ceil($total_items / $limit); $set_limit = ($page * $limit) – $limit; echo “Total Pages: $total_pages <br/>”; //query: **EDIT TO YOUR TABLE NAME, ETC. $q = mysql_query(“SELECT * FROM customer LIMIT $set_limit, $limit“); if(!$q) die(mysql_error()); $no_row = mysql_num_rows($q); if($no_row == 0) die(“No matches met your criteria.”); //Results per page: echo(“<br/>Records Per Page: <a href=$page_name?limit=10&page=1> 10</a> | <a href=$page_name?limit=25&page=1> 25</a> | <a href=$page_name?limit=50&page=1> 50</a><br/> “); //show data matching query: while($object = mysql_fetch_object($q)) { //print your data here. echo(“<br/>————————————–<br/>”); echo(“First Name: “.$object->first_name.”<BR/>”); echo(“Last Name: “.$object->last_name.”<BR/>”); echo(“Email: “.$object->email.”<BR/>”); echo(“————————————–<br/>”); } //Results Per Page: Same as earlier one echo(“<br/>Records Per Page: <a href=$page_name?limit=10&page=1> 10</a> | <a href=$page_name?limit=25&page=1> 25</a> | <a href=$page_name?limit=50&page=1> 50</a><br/> “); //prev. page $prev_page = $page – 1; if($prev_page >= 1) { echo(“<b><<</b> <a href=$page_name?limit=$limit&page=$prev_page> <b>Prev.</b></a>”); } //Display middle pages: for($a = 1; $a <= $total_pages; $a++) { if($a == $page) { echo(“<b> $a</b> | “); //no link } else { echo(“ <a href=$page_name?limit=$limit&page=$a> $a </a> | “); } } //Next page: $next_page = $page + 1; if($next_page <= $total_pages) { echo(“ <a href=$page_name?limit=$limit &page=$next_page><b>Next</b></a> > >”); } //all done ?>

Four: Run this script in browser and see pagination with php and mysql in action.

Explanation: First this script will query the database and get total number of rows by the “SELECT count(*) as cnt FROM customer ” query. It will return the count as cnt. Now get the count and store it in the variable $total_items.

Now get the limit and page number from the GET variables. For the first time both will be null, so by default we will set the limit to 10 and page to 1.

If this variables are not blank, i.e. if the users click on the next or previous or page links then the page number and number of items to displayed will be not null and we will display data accordingly.

If some how this limit and page values are greater than the specified values or less than the specified values we will set them as default.

Now we will calculate how many pages the data will span. i.e. if we select 10 data to displayed at one time and total number of data are 85 then there will be 9 pages and by default the first page will be displayed. In this way if we choose to display 25 data at one time then the total number of pages will be 4, first 3 will display 25 data each and the last will display 10 data.

Hope this tutorial will help you to explore the vast world of pagination using php and mysql.

Kindly send me any queries that you might have.

 

Here is the code to download. Its currently in a Word Document.

Connect MySQL From Java (JDBC)

Download MySQL Server: 

The main download page for both the database server and the connector as of September 2004 is http://dev.mysql.com/downloads/. 
Several different versions of the database server are available for downloading:

MySQL 5.0 — Generally Available (GA) release for production use

Upcoming Releases:
MySQL 5.1 — Release Candidate release, Test new features early!
MySQL 6.0 — Alpha – Falcon Preview New!
Snapshots — source code snapshots of the development trees
Older Releases:
MySQL 4.1 — Previous GA release
Archives of Older Releases

This list can be expected to change over time as new versions of the database server are released.  Thus, the links in the above list will become obsolete.  When that happens, you should revert back to the download page at http://dev.mysql.com/downloads/ and download the version that best suits your needs at that time.

Download MySQL Connector J:http://dev.mysql.com/downloads/index.html 

MySQL Connector/J — for connecting to MySQL from Java

• MySQL Connector/J 5.1 — Generally Available (GA) release (recommended)
• MySQL Connector/J 5.0 — Previous Generally Available (GA) release
• Older releases — older releases (only recommended for special needs)
• Snapshots — source code snapshots of the development trees

I think you will find many tutorials which will guide you through the MySQL Server installation process. Istallation of MySQL Server is very much easy. But one thing I like to add that donot forgot to add mysql bin directory to the Windows PATH variable.

Eclipse: For Java editing

You must have some Java editors. But I like ecplise (http://www.eclipse.org). Download and install ecplise. http://www.eclipse.org/downloads/download.php?file=/technology/epp/downloads/release/20070927/eclipse-java-europa-fall-win32.zip

Installation of ecplise is very easy. Just upzip the package anywhere and run ecplise.exe.

Now we will create a Project named DemoMySQLJavaConnectivity . (I think you will find it very easy to create a project in ecplise. I will think about writing a blog that will show us how to create a Java Project in ecplise. )

First create a package named:  demo.mysql.java.connectivity

Now I will create a Java source file named ConnectToMySQLSimple.java

package demo.mysql.java.connectivity;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class ConnectToMySQLSimple {

 public static void main(String args[]) {
  Connection con = null;

  try {
   Class.forName(“com.mysql.jdbc.Driver”);
   String url = “jdbc:mysql://localhost:3306/mytestdb”;

   // Get a connection to the database for a
   // user named auser with the password
   // drowssap, which is password spelled
   // backwards.
   con = DriverManager.getConnection(url, “root”,”mypass”);
   Statement st = con.createStatement();
   ResultSet rs = st.executeQuery(“select * from mytable”);
   while (rs.next()) {
    System.out.println(“Id ” + rs.getString(1));
    System.out.println(“Val ” + rs.getString(2));
   }
  } catch (Exception e) {
   e.printStackTrace();
   System.out.println(“Exception: ” + e.getMessage());
  } finally {
   try {
    if (con != null)
     con.close();
   } catch (SQLException e) {
    
   }
  }
 }
}
Now complie this code and run.
Explanation: First we have to add mysql-connector-java-5.1.5-bin.jar to the CLASSPATH.
Then we should look at the code ->

Class.forName(“com.mysql.jdbc.Driver”);
We know that this line returns the Class object associated with the class or interface with the given string name, using the given class loader, which is mysql is this example.

String url = “jdbc:mysql://localhost:3306/mytestdb”;

Now we will define a JDBC URL. Now MySQL Server runs on port 3306 by default. If your server runs on another port other that 3306 then you should put that port instead of 3306. Now localhost is  for your local machine. If your mysql server runs on another host then you should put that in place of localhost. Like jdbc:127.0.0.1:3306/mytestdb

And lastly mytestdb is the database we want to connect using this code.

And now we will gett he connection by 
   con = DriverManager.getConnection(url, “root”,”mypass”);
where root is my mysql server user name and mypass  is my mysql server password for root.

And now,

Statement st = con.createStatement();
ResultSet rs = st.executeQuery(“select * from mytable”);

here we create a statement object and executes the query which will return a Resultset object.

Now if I create the table : mytable as,

CREATE TABLE mytable (id INT(4) AUTO_INCREMENT, Value VARCHAR(20));
and then insert some rows there,

INSERT INTO mytable(Value) VALUES(“Root”);
INSERT INTO mytable(Value) VALUES(“UBK”);
INSERT INTO mytable(Value) VALUES(“MySQL”);

Now if we execute the source java code given earlier, then it will return all the ID and Values stored in mytable of Database mytestdb, using the portion:
while (rs.next()) {
    System.out.println(“Id ” + rs.getString(1));
    System.out.println(“Val ” + rs.getString(2));
   }

Next we will see some advanced examples:

package demo.mysql.java.connectivity;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class DemoMySQLJavaAdvanced {
 public static void main(String args[]) {
  try {
   Statement stmt;
   ResultSet rs;

   // Register the JDBC driver for MySQL.
   Class.forName(“com.mysql.jdbc.Driver”);

   // Define URL of database server for
   // database named JunkDB on the localhost
   // with the default port number 3306.
   String url = “jdbc:mysql://localhost:3306/mytestdb”;

   // Get a connection to the database for a
   // user named auser with the password
   // drowssap, which is password spelled
   // backwards.
   Connection con = DriverManager.getConnection(url, “root”,”mypass”);

   // Display URL and connection information
   System.out.println(“URL: ” + url);
   System.out.println(“Connection: ” + con);

   // Get a Statement object
   stmt = con.createStatement();

   // As a precaution, delete myTable if it
   // already exists as residue from a
   // previous run. Otherwise, if the table
   // already exists and an attempt is made
   // to create it, an exception will be
   // thrown.
   
   //Query to drop table;
   try {
    stmt.executeUpdate(“DROP TABLE myTable”);
   } catch (Exception e) {
    System.out.print(e);
    System.out.println(“No existing table to delete”);
   }// end catch
   
   // Create a table in the database named
   // myTable.
   
  stmt.executeUpdate(“CREATE TABLE myTable(test_id int,”+ “test_val char(15) not null)”);
           
   // Insert some values into the table
   stmt.executeUpdate(“INSERT INTO myTable(test_id, “
     + “test_val) VALUES(1,’One’)”);
   stmt.executeUpdate(“INSERT INTO myTable(test_id, “
     + “test_val) VALUES(2,’Two’)”);
   stmt.executeUpdate(“INSERT INTO myTable(test_id, “
     + “test_val) VALUES(3,’Three’)”);
   stmt.executeUpdate(“INSERT INTO myTable(test_id, “
     + “test_val) VALUES(4,’Four’)”);
   stmt.executeUpdate(“INSERT INTO myTable(test_id, “
     + “test_val) VALUES(5,’Five’)”);

   // Get another statement object initialized
   // as shown.
   stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,
     ResultSet.CONCUR_READ_ONLY);

   // Query the database, storing the result
   // in an object of type ResultSet
   rs = stmt.executeQuery(“SELECT * “
     + “from myTable ORDER BY test_id”);

   // Use the methods of class ResultSet in a
   // loop to display all of the data in the
   // database.
   System.out.println(“Display all results:”);
   while (rs.next()) {
    int theInt = rs.getInt(“test_id”);
    String str = rs.getString(“test_val”);
    System.out.println(“\ttest_id= ” + theInt + “\tstr = ” + str);
   }// end while loop

   // Display the data in a specific row using
   // the rs.absolute method.
   System.out.println(“Display row number 2:”);
   if (rs.absolute(2)) {
    int theInt = rs.getInt(“test_id”);
    String str = rs.getString(“test_val”);
    System.out.println(“\ttest_id= ” + theInt + “\tstr = ” + str);
   }// end if

   // Delete the table and close the connection
   // to the database
   //stmt.executeUpdate(“DROP TABLE myTable”);
   con.close();
  } catch (Exception e) {
   e.printStackTrace();
  }//end catch
 }//end main
}//end class DemoMySQLJavaAdvanced

I think this will help you to get what you need primarily about mysql java connectivity.
You will find this project here.