Jump to content


Check out our Community Blogs

Register and join over 40,000 other developers!


Recent Status Updates

View All Updates

- - - - -

Simple Pagination

pagination

  • Please log in to reply
20 replies to this topic

#1 Guest_Jaan_*

Guest_Jaan_*
  • Guest

Posted 30 September 2009 - 12:09 PM

Hey there.. It has been a long time since I wrote a tutorial.. but now I thought I'll do it.. So let's start :)

First we have to connect to our database:


// Connect to your database
$mysql_con = mysql_connect("localhost", "Username", "Password");

// Let's check if there were any errors
if(!$mysql_con){

// If there was ań error, if there was, lets display an error
die(mysql_error());

}

// Select your database
$mysql_db = mysql_select_db("MyDatabase");

// If there were any errors with selecting your database, display an error
if(!$mysql_db){

die(mysql_error());

}


Now we are connected to database.. Let's get our information.
First we need to get our page number:


// If the page wasn't set, lets set $page to number 1 for the first page
if($page == ""){

$page = "1";

}else{

// If page is set, lets get it
$page = $_GET['page'];

}


Now we need to get all messages from database and count them:


// Now lets get all messages from your database
$sql = "SELECT * FROM messages";
$query = mysql_query($sql);

// Lets count all messages
$num = mysql_num_rows($query);


When these things are done.. lets set our numbers:


// Lets set how many messages we want to display
$per_page = "10";

// Now we must calculate the last page
$last_page = ceil($num/$per_page);

// And set the first page
$first_page = "1";


Now it's time for "Next" , "Previous" First and last page.. :) :


// Here we are making the "First page" link
echo "<a href='?page=".$first_page."'>First page</a> ";

// If page is 1 then remove link from "Previous" word
if($page == $first_page){

echo "Previous ";

}else{

if(!isset($page)){

echo "Previous ";

}else{

// But if page is set and it's not 1.. Lets add link to previous word to take us back by one page
$previous = $page-1;
echo "<a href='?page=".$previous."'>Previous</a> ";

}

}

// If the page is last page.. lets remove "Next" link
if($page == $last_page){

echo "Next ";

}else{

// If page is not set or it is set and it's not the last page.. lets add link to this word so we can go to the next page
if(!isset($page)){

$next = $first_page+1;
echo "<a href='?page=".$next."'>Next</a> ";

}else{

$next = $page+1;
echo "<a href='?page=".$next."'>Next</a> ";

}

}

// And now lets add the "Last page" link
echo "<a href='?page=".$last_page."'>Last page</a>";


Posted Image Posted Image

Now lets do some math and get our messages :


// Math.. It gets us the start number of message that will be displayed
$start = ($page-1)*$per_page;

// Now lets set the limit for our query
$limit = "LIMIT $start, $per_page";

// It's time for getting our messages
$sql = "SELECT * FROM messages $limit";
$query = mysql_query($sql);

echo "<br /><br />";

// And lets display our messages
while($row = mysql_fetch_array($query) or die(mysql_error())){

echo $row['message']."<br />";

}


Voila.. it wasn't that hard right ? :)

Here's the full code with comments:


<?php

// Connect to your database
$mysql_con = mysql_connect("localhost", "Username", "Password");

// Let's check if there were any errors
if(!$mysql_con){

// If there was ań error, if there was, let's display an error
die(mysql_error());

}

// Select your database
$mysql_db = mysql_select_db("MyDatabase");

// If there were any errors with selecting your database, display an error
if(!$mysql_db){

die(mysql_error());

}

// If the page wasn't set, lets set $page to number 1 for the first page
if($page == ""){

$page = "1";

}else{

// If page is set, let's get it
$page = $_GET['page'];

}

// Now lets get all messages from your database
$sql = "SELECT * FROM messages";
$query = mysql_query($sql);

// Lets count all messages
$num = mysql_num_rows($query);

// Lets set how many messages we want to display
$per_page = "10";

// Now we must calculate the last page
$last_page = ceil($num/$per_page);

// And set the first page
$first_page = "1";

// Here we are making the "First page" link
echo "<a href='?page=".$first_page."'>First page</a> ";

// If page is 1 then remove link from "Previous" word
if($page == $first_page){

echo "Previous ";

}else{

if(!isset($page)){

echo "Previous ";

}else{

// But if page is set and it's not 1.. Lets add link to previous word to take us back by one page
$previous = $page-1;
echo "<a href='?page=".$previous."'>Previous</a> ";

}

}

// If the page is last page.. lets remove "Next" link
if($page == $last_page){

echo "Next ";

}else{

// If page is not set or it is set and it's not the last page.. lets add link to this word so we can go to the next page
if(!isset($page)){

$next = $first_page+1;
echo "<a href='?page=".$next."'>Next</a> ";

}else{

$next = $page+1;
echo "<a href='?page=".$next."'>Next</a> ";

}

}

// And now lets add the "Last page" link
echo "<a href='?page=".$last_page."'>Last page</a>";

// Math.. It gets us the start number of message that will be displayed
$start = ($page-1)*$per_page;

// Now lets set the limit for our query
$limit = "LIMIT $start, $per_page";

// It's time for getting our messages
$sql = "SELECT * FROM messages $limit";
$query = mysql_query($sql);

echo "<br /><br />";

// And lets display our messages
while($row = mysql_fetch_array($query) or die(mysql_error())){

echo $row['message']."<br />";

}

?>


I hope it helped you a little :)

Regards,
Jaan

Edited by Alexander, 18 April 2011 - 09:19 PM.
Restoration

  • 0

#2 debtboy

debtboy

    CC Devotee

  • Just Joined
  • PipPipPipPipPipPip
  • 499 posts

Posted 30 September 2009 - 12:29 PM

Good Tutorial +rep :)
  • 0

#3 Guest_Jaan_*

Guest_Jaan_*
  • Guest

Posted 30 September 2009 - 12:43 PM

Thank you :)
  • 0

#4 WingedPanther73

WingedPanther73

    A spammer's worst nightmare

  • Moderator
  • 17757 posts
  • Location:Upstate, South Carolina
  • Programming Language:C, C++, PL/SQL, Delphi/Object Pascal, Pascal, Transact-SQL, Others
  • Learning:Java, C#, PHP, JavaScript, Lisp, Fortran, Haskell, Others

Posted 30 September 2009 - 01:02 PM

Very nice. +rep
  • 0

Programming is a branch of mathematics.
My CodeCall Blog | My Personal Blog

My MineCraft server site: http://banishedwings.enjin.com/


#5 Guest_Jordan_*

Guest_Jordan_*
  • Guest

Posted 30 September 2009 - 02:03 PM

Very handy! +rep
  • 0

#6 John

John

    CC Mentor

  • Moderator
  • 4450 posts
  • Location:New York, NY

Posted 30 September 2009 - 09:53 PM

Epic failure. (just kidding, but in all seriousness)
// Now lets get all messages from your database 
$sql = "SELECT * FROM messages";
$query = mysql_query($sql);

// Lets count all messages
$num = mysql_num_rows($query);
Jordan did this and crippled the CodeCall server.
$num = mysql_query("SELECT COUNT(*) FROM messages");
is the preferred method of counting the rows in a database.
  • 0

#7 amrosama

amrosama

    CC Mentor

  • VIP Member
  • PipPipPipPipPipPipPipPip
  • 2765 posts

Posted 01 October 2009 - 01:31 AM

nice tutorial +rep
@john: is theres a performance difference between these 2:
-"select count(`some_field_name`) from table"
-"select count(*) from table"
  • 0
yo homie i heard you like one-line codes so i put a one line code that evals a decrypted one line code that prints "i love one line codes"
eval(base64_decode("cHJpbnQgJ2kgbG92ZSBvbmUtbGluZSBjb2Rlcyc7"));
www.amrosama.com | the unholy methods of javascript

#8 John

John

    CC Mentor

  • Moderator
  • 4450 posts
  • Location:New York, NY

Posted 01 October 2009 - 03:56 AM

nice tutorial +rep
@john: is theres a performance difference between these 2:
-"select count(`some_field_name`) from table"
-"select count(*) from table"


Yes assuming the MyISAM engine is being used (and not Innodb - MyISAM is default) COUNT(*) will have the best performance. MyISAM caches the table row number so COUNT(*) is instantaneous. COUNT(`col`) could cause MySQL to perform a table scan depending on the column attributes.
  • 0

#9 Guest_Jordan_*

Guest_Jordan_*
  • Guest

Posted 01 October 2009 - 04:47 AM

Epic failure. (just kidding, but in all seriousness)

// Now lets get all messages from your database 
$sql = "SELECT * FROM messages";
$query = mysql_query($sql);

// Lets count all messages
$num = mysql_num_rows($query);
Jordan did this and crippled the CodeCall server.
$num = mysql_query("SELECT COUNT(*) FROM messages");
is the preferred method of counting the rows in a database.



I believe, if we look hard enough, we can find a flaw in everyone's tutorial. I looked over some of your tutorials from the past they also have errors.

nice tutorial +rep
@john: is theres a performance difference between these 2:
-"select count(`some_field_name`) from table"
-"select count(*) from table"


Check this out: COUNT(*) vs COUNT(col) | MySQL Performance Blog

Good performance information there.
  • 0

#10 Guest_Jaan_*

Guest_Jaan_*
  • Guest

Posted 01 October 2009 - 06:09 AM

Well.. What can I say..
both ways work.. so..
  • 0

#11 tonymorrison39

tonymorrison39

    CC Newcomer

  • Just Joined
  • PipPip
  • 20 posts

Posted 21 November 2009 - 08:43 AM

well written thanks
  • 0

#12 Guest_Jaan_*

Guest_Jaan_*
  • Guest

Posted 12 December 2009 - 03:23 PM

Thank you :)
  • 0





Also tagged with one or more of these keywords: pagination