Tutorial on Creating PHP Pagination and MySQL

Asked By 0 points N/A Posted on -
qa-featured

Hello techyv experts,

I just started leaning PHP and MySQL and I consider myself a nob on these great languages.

I am building an online DBMS (Database Management System) for our school project. The database contains all the students in the school. And I had realized that it should need PHP pagination because of it contains many member. But I am having difficulty on how to this, I don’t know how to start and how to formulate a logic on this.

I am planning to have 10 pages per page, by number 1-10, then every number must be clickable. and between both side of this number contains a clickable next and previous link tat when clicked it should proceed to the next 10 members and vice versa when previous is clicked.

Does this need a complex coding and querying? If anyone had done this ..Please share your ideas and I absolutely appreciate all of your answers.

Thank you,

Allan Caps.

SHARE
Best Answer by valvi
Answered By 0 points N/A #94037

Tutorial on Creating PHP Pagination and MySQL

qa-featured

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.

Best Answer
Best Answer
Answered By 0 points N/A #94038

Tutorial on Creating PHP Pagination and MySQL

qa-featured

Hi,

 
The following code would solve your queries:
 
<?php 
 
 // Connects to your Database 
 
 mysql_connect("your.hostaddress.com", "username", "password") or die(mysql_error()); 
 
 mysql_select_db("address") or die(mysql_error()); 
 
 
 //This checks to see if there is a page number. If not, it will set it to page number 1
 
 if (!(isset($pagenum))) 
 
 { 
 
 $pagenum = 1; 
 
 } 
 
 
 //Here we count the number of results 
 
 //Edit $data to be your query 
 
 $data = mysql_query("SELECT * FROM topsites") or die(mysql_error()); 
 
 $rows = mysql_num_rows($data); 
 
 
 //This is the number of results displayed per page 
 
 $page_rows = 4; 
 
 
 //This tells us the page number of our last page 
 
 $last = ceil($rows/$page_rows); 
 
 
 //this makes sure the page number isn't below one, or more than our maximum pages 
 
 if ($pagenum < 1) 
 
 { 
 
 $pagenum = 1; 
 
 } 
 
 elseif ($pagenum > $last) 
 
 { 
 
 $pagenum = $last; 
 
 } 
 
 
 //This sets the range to display in our query 
 
 $max = 'limit ' .($pagenum – 1) * $page_rows .',' .$page_rows; 
 
Hope this helps you better.
 
Thanks & Regards,
Ben
 
Answered By 0 points N/A #197283

Tutorial on Creating PHP Pagination and MySQL

qa-featured

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>

 

 

Login/Register to Answer

Related Questions