PHP Pagination Function and MySQL

From CoderLearner
Jump to: navigation, search

Description

Use pagination function to generate pagination from a table.

Table

Create table myorder(
id int not null auto_increment primary key,
item varchar(200) not null,
customer_name varchar(200) not null,
address varchar(200) not null,
price double not null
);

Records



INSERT INTO myorder (item,customer_name,address,price)
VALUES('Java','Bob Maat','2450 Jame Dr, Houston,Tx',30),
      ('PHP/MySQL','Paul Chao','3400 Lake Rd, Houston,Tx',40),
      ('MySQL/Ajax','David','3203 Maka, Houston, Tx',39),
      ('MySQL 5','Paul David','3203 Franklin Dr, Houston, Tx',39),
      ('Postgresql','Lili','430 Mimin, Ruston, LA',32),
      ('PHP/Postgresql','Jamey','7320 CA LA, LA, CA',30),
      ('Perl','Chris d','3203 kilo, Houston, Tx',49),
      ('jQuery','Maki','3320, Houston, Tx',20),
      ('HTML/jQuery','Likinu','320, CA, FL',22),
      ('PHP/jQuery','Maki','3320, Houston, Tx',25),
      ('Mobile/jQuery','Maki','778, Houston, Tx',23),
      ('CSS/jQuery','Maki','3320, Houston, Tx',42),
      ('C++','LiMaki','3320, Houston, Tx',50),
      ('java 5','LOMaki','3320, Houston, Tx',20),
      ('Jscript','Maki','3320, Houston, Tx',40),
      ('Linux','Maki','3320, Houston, Tx',30),
      ('Mobile Design','Maki','3320, Houston, Tx',42),
      ('Android/AJax','Maki','3320, Houston, Tx',60),
      ('Webtop/EMCS','Maki','3320, Houston, Tx',70),
      ('Documentum/Xcp','Maki','3320, Houston, Tx',120),
      ('Javascript/JQuery','Maki','3320, Houston, Tx',20);

Code

<?php
// add the pagination function
require_once 'mypagination_bar.php';

require_once ('adodb.inc.php');

$host = 'localhost';
$user = 'root';
$pass = 'caojiang';
$dbname = 'school';

// connect to the database using adodb5
$conn1 = &ADONewConnection('mysql');
$conn1->PConnect($host, $user, $pass, $dbname);
//$conn1->debug = true;


$totalcounts = 0;
$page = (!empty($_REQUEST['page']) ? $_REQUEST['page'] : 0);
$perpage = 5;
$baseurl = 'mypagination.php?';
$pagevar = 'page';
$nocurr = false;
$return = false;

// Get and count all the records
$sql = "SELECT COUNT(*) AS total FROM myorder";
$rs = $conn1->Execute($sql);
if (is_object($rs) and is_array($rs->fields)) {
    $totalcounts = reset($rs->fields);
}
// finished count records

if ($totalcounts > $perpage) {
    // display pagination
    mypaging_bar($totalcounts, 
           $page, $perpage, $baseurl, $pagevar = 'page', $nocurr = false);

    // get the record
    $sql = "SELECT item, customer_name, address,price FROM myorder";
    $rs = $conn1->SelectLimit($sql, $perpage, $page);
    echo '<table border="1" cellspacing="8" cellpadding="8">';
    echo '<tr><td>Item Name</td><td>Customer Name</td>
               <td>Address</td><td>Price</td></tr>';
    while (!$rs->EOF) {
        echo '<tr><td>' . $rs->fields[0] . '</td><td>' 
           . $rs->fields[1] 
           . '</td><td>' . $rs->fields[2] . '</td><td>' 
           . $rs->fields[3] . '</td></tr>';
        $rs->MoveNext();
    }
    echo '</table>';
    mypaging_bar($totalcounts,
 $page, $perpage, $baseurl, $pagevar = 'page', $nocurr = false);
}

?>

Php mysql and pagination page0.png