Tutorial on Creating PHP Pagination and MySQL

Tutorial on Creating PHP Pagination and MySQL

Hi Allan,

You can easily do what you want by just following the steps below. I have also some screenshots for you so that you would not have a hard time following the instructions. The script below will show you how to create pagination in PHP.

This will be your output once you are done.

Copy the attached code and put it on your preferred PHP code editor. Then just replace the values with that of your server/database information and other variables

These are the things that you needed to update for this to work on your system

1. Code to connect to your DB – input your connection to DB code.

2. $tbl_name – replace this with your specified table identifier.

3. $adjacents – input the number of adjacent pages you want to be shown on the sides.

4. $targetpage – would be the filename. E. g I saved the file as phppagination.php, my $targetpage is “phppagination”.

5. $limit – indicates the number of items will be shown on each page.

6. SELECT column_name – update this with your column name.

7. Replace your own while..loop here – put your code to echo the results here.

Now save and test your script.

Tutorial on Creating PHP Pagination and MySQL

Hi,

Download one css file  and name it "style.css"  and run this code

Create one database named "stud"

CREATE TABLE student ( id int(2) NOT NULL auto_increment, name varchar(50) NOT NULL, class varchar(10) NOT NULL , mark int(3) NOT NULL default '0', primary KEY (id) )

INSERT INTO student VALUES (1, 'John Deo', 'Four', 75);# 1 row affected.

 

INSERT INTO student VALUES (2, 'Max Ruin', 'Three', 85);# 1 row affected.

 

INSERT INTO student VALUES (3, 'Arnold', 'Three', 55);# 1 row affected.



 INSERT INTO student VALUES (4, 'Krish Star', 'Four', 60);# 1 row affected.

 

INSERT INTO student VALUES (5, 'John Mike', 'Four', 60);# 1 row affected.

 

INSERT INTO student VALUES (6, 'Alex John', 'Four', 55);# 1 row affected.

 

INSERT INTO student VALUES (7, 'My John Rob', 'Fifth', 78);# 1 row affected.

 

INSERT INTO student VALUES (8, 'Asruid', 'Five', 85);# 1 row affected.

 

INSERT INTO student VALUES (9, 'Tes Qry', 'Six', 78);# 1 row affected.

 

INSERT INTO student VALUES (10, 'Big John', 'Four', 55);# 1 row affected.

 

INSERT INTO student VALUES (11, 'Ronald', 'Six', 89);# 1 row affected.

 

INSERT INTO student VALUES (12, 'Recky', 'Six', 94);# 1 row affected.

 

INSERT INTO student VALUES (13, 'Kty', 'Seven', 88);# 1 row affected.

 

INSERT INTO student VALUES (14, 'Bigy', 'Seven', 88);# 1 row affected.

 

INSERT INTO student VALUES (15, 'Tade Row', 'Four', 88);# 1 row affected.

 

INSERT INTO student VALUES (16, 'Gimmy', 'Four', 88);# 1 row affected.

 

INSERT INTO student VALUES (17, 'Tumyu', 'Six', 54);# 1 row affected.

 

INSERT INTO student VALUES (18, 'Honny', 'Five', 75);# 1 row affected.

 

INSERT INTO student VALUES (19, 'Tinny', 'Nine', 18);# 1 row affected.

 

INSERT INTO student VALUES (20, 'Jackly', 'Nine', 65);# 1 row affected.

 

INSERT INTO student VALUES (21, 'Babby John', 'Four', 69);# 1 row affected.

 

INSERT INTO student VALUES (22, 'Reggid', 'Seven', 55);# 1 row affected.

 

INSERT INTO student VALUES (23, 'Herod', 'Eight', 79);# 1 row affected.

 

INSERT INTO student VALUES (24, 'Tiddy Now', 'Seven', 78);# 1 row affected.



 INSERT INTO student VALUES (25, 'Giff Tow', 'Seven', 88);# 1 row affected.

 

INSERT INTO student VALUES (26, 'Crelea', 'Seven', 79);# 1 row affected.

 

INSERT INTO student VALUES (27, 'Big Nose', 'Three', 81);# 1 row affected.

 

INSERT INTO student VALUES (28, 'Rojj Base', 'Seven', 86);# 1 row affected.



 INSERT INTO student VALUES (29, 'Tess Played', 'Seven', 55);# 1 row affected.

 

INSERT INTO student VALUES (30, 'Reppy Red', 'Six', 79);# 1 row affected.

 

INSERT INTO student VALUES (31, 'Marry Toeey', 'Four', 88);# 1 row affected.

 

INSERT INTO student VALUES (32, 'Binn Rott', 'Seven', 90);# 1 row affected.

 

INSERT INTO student VALUES (33, 'Kenn Rein', 'Six', 96);# 1 row affected.

 

INSERT INTO student VALUES (34, 'Gain Toe', 'Seven', 69);# 1 row affected.

 

INSERT INTO student VALUES (35, 'Rows Noump', 'Six', 88);# 1 row affected.

 

Config.php

<?Php

$dbhost_name = "localhost";

$database = "stud";// database name

$username = "root"; // user name

$password = ""; // password



//////// Do not Edit below /////////

try {

$dbo = new PDO('mysql:host=localhost;dbname='.$database, $username, $password);

} catch (PDOException $e) {

print "Error!: " . $e->getMessage() . "<br/>";

die();

}

?>

 

"demo_paging.php"

<!doctype html public "-//w3c//dtd html 3.2//en">



<html>



<head>

<title>Plus2net.com paging script in PHP</title>

</head>



<body>

<?Php

require "config.php";           // All database details will be included here

$page_name="demo_paging2.php"; //  If you use this code with a different page ( or file ) name then change this



////// starting of drop down to select number of records per page /////



@$limit=$_GET['limit']; // Read the limit value from query string.

if(strlen($limit) > 0 and !is_numeric($limit)){

echo "Data Error";

exit;

}



// If there is a selection or value of limit then the list box should show that value , so we have to lock that options //

// Based on the value of limit we will assign selected value to the respective option//

switch($limit)

{

case 2:

$select2="selected";

$select10="";

$select5="";

break;



case 5:

$select5="selected";

$select10="";

$select2="";

break;



default:

$select10="selected";

$select5="";

$select2="";

break;



}



@$start=$_GET['start'];

if(strlen($start) > 0 and !is_numeric($start)){

echo "Data Error";

exit;

}



echo "Select Number of records per page: <form method=get action=$page_name>

<select name=limit>

<option value=10 $select10>10 Records</option>

<option value=5 $select5>5 Records</option>

<option value=2 $select2>2 Records</option>

</select>

<input type=submit value=GO>";



// You can keep the below line inside the above form, if you want when user selection of number of

// records per page changes, it should not return to first page.

// <input type=hidden name=start value=$start>

////////////////////////////////////////////////////////////////////////

//

///// End of drop down to select number of records per page ///////





$eu = ($start – 0);



if(!$limit > 0 ){ // if limit value is not available then let us use a default value

$limit = 10;    // No of records to be shown per page by default.

}                             

$this1 = $eu + $limit;

$back = $eu – $limit;

$next = $eu + $limit;





/////////////// Total number of records in our table. We will use this to break the pages///////

$nume = $dbo->query("select count(id) from student")->fetchColumn();

/////// The variable nume above will store the total number of records in the table////



/////////// Now let us print the table headers ////////////////

$bgcolor="#f1f1f1";

echo "<TABLE width=50% align=center  cellpadding=0 cellspacing=0> <tr>";

echo "<td  bgcolor='dfdfdf' >&nbsp;<font face='arial,verdana,helvetica' color='#000000' size='4'>Name</font></td>";

echo "<td  bgcolor='dfdfdf' >&nbsp;<font face='arial,verdana,helvetica' color='#000000' size='4'>Class</font></td>";

echo "<td  bgcolor='dfdfdf'>&nbsp;<font face='arial,verdana,helvetica' color='#000000' size='4'>Mark</font></td></tr>";



////////////// Now let us start executing the query with variables $eu and $limit  set at the top of the page///////////

$query=" SELECT * FROM student  limit $eu, $limit ";

//////////////// Now we will display the returned records in side the rows of the table/////////

foreach ($dbo->query($query) as $row) {



if($bgcolor=='#f1f1f1'){$bgcolor='#ffffff';}

else{$bgcolor='#f1f1f1';}



echo "<tr >";

echo "<td align=left bgcolor=$bgcolor id='title'>&nbsp;<font face='Verdana' size='2'>$row[name]</font></td>";

echo "<td align=left bgcolor=$bgcolor id='title'>&nbsp;<font face='Verdana' size='2'>$row[class]</font></td>";

echo "<td align=left bgcolor=$bgcolor id='title'>&nbsp;<font face='Verdana' size='2'>$row[mark]</font></td>";



echo "</tr>";

}

echo "</table>";

////////////////////////////// End of displaying the table with records ////////////////////////



/////////////// Start the buttom links with Prev and next link with page numbers /////////////////

echo "<table align = 'center' width='50%'><tr><td  align='left' width='30%'>";

//// if our variable $back is equal to 0 or more then only we will display the link to move back ////////

if($back >=0) {

print "<a href='$page_name?start=$back&limit=$limit'><font face='Verdana' size='2'>PREV</font></a>";

}

//////////////// Let us display the page links at  center. We will not display the current page as a link ///////////

echo "</td><td align=center width='30%'>";

$i=0;

$l=1;

for($i=0;$i < $nume;$i=$i+$limit){

if($i <> $eu){

echo " <a href='$page_name?start=$i&limit=$limit'><font face='Verdana' size='2'>$l</font></a> ";

}

else { echo "<font face='Verdana' size='4' color=red>$l</font>";}        /// Current page is not displayed as link and given font color red

$l=$l+1;

}





echo "</td><td  align='right' width='30%'>";

///////////// If we are not in the last page then Next link will be displayed. Here we check that /////

if($this1 < $nume) {

print "<a href='$page_name?start=$next&limit=$limit'><font face='Verdana' size='2'>NEXT</font></a>";}

echo "</td></tr></table>";





?>

 



</body>



</html>