homepage Welcome to WebmasterWorld Guest from 54.226.10.234
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member
Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
Forum Library, Charter, Moderators: coopster & jatar k

PHP Server Side Scripting Forum

    
PHP/MySQL pagination
bysonary

5+ Year Member



 
Msg#: 3353576 posted 3:20 pm on May 30, 2007 (gmt 0)

Hey everyone, still learning here and still liking it, I know there are probably loads of pagination how tos on this forum but I wanted to ask again, mainly so i can put it into the context of my problem.

I am writing an IP logger for a website, so far the script is pretty basic. I was hoping someone here might set me off on the right direction for how to limit the number of records per page to 20 or something like that and then display a load of linsk with page numbers etc. here is the code I currently have


<?php
require_once('/home/www/juttuffi/lib/dbcon.php');

$timestamp = mktime();
$date = date('l d F Y');
$time = date('G:i:s');
$hostname = gethostbyaddr($_SERVER['REMOTE_ADDR']);
$ip = $_SERVER['REMOTE_ADDR'];

echo 'Your IP Address is: <span class="text">'. $ip .'</span><br />';
echo 'Your Hostname is: <span class="text">'. $hostname .'</span><br /><br />';
echo 'Your IP Address and hostname have been logged.<br><br>LOGGED IP ADDRESSES<br><br>';

$insQuery = "INSERT INTO iplog (hostname, ip, stamp, date, time) VALUES ('$hostname','$ip','$timestamp','$date','$time')";
$insResult = mysql_query($insQuery);

$selQuery = mysql_query("SELECT * FROM iplog ORDER BY stamp DESC");
?>
<table cellspacing="0" cellpadding="0" class="titletable">
<tr>
<td class="td1" width="35%">Hostname</td>
<td class="td1" width="20%">IP</td>
<td class="td1" width="30%">Date</td>
<td class="td1" width="15%">Time</td>
</tr>
</table>
<table width="100%" cellspacing="0" cellpadding="0" class="borderfull">
<?
while($selResult = mysql_fetch_array($selQuery,MYSQL_ASSOC))
{
$i = ($i + 1) % 2;
//echo "<table border=\"0\" cellspacing=\"0\" cellpadding=\"0\" width=\"100%\" class=\"table_row$i\">";
echo "<tr class=\"table_row$i\">";
echo '<td width="35%">'.$selResult['hostname'].'</td>';
echo '<td width="20%">'.$selResult['ip'].'</td>';
echo '<td width="30%">'.$selResult['date'].'</td>';
echo '<td width="15%">'.$selResult['time'].'</td>';
echo '</tr>';
}
?>
</table>
<?
mysql_close($dbc);
?>

What changes might I need to make to add pagination to this script and what approach should I take? I assume its going to involve the LIMIT feature of SQL but how do I do go about implementing it?

any help would be appreciated.

Thanks

ChrisW

 

slade7

10+ Year Member



 
Msg#: 3353576 posted 4:01 pm on May 30, 2007 (gmt 0)

There are a lot of ready made scripts for it, but it's simpler than most of them look. Most of the fluff is in dealing with the display.

Here's a simple approach I've used for many different applications - it displays the page to page links only at the bottom

before ANYTHING you need to set a couple of variables - like this:

//////////// pagination
$getcount = mysql_query ("SELECT COUNT(*) FROM yourtable");
$postnum = mysql_result($getcount,0);
$limit = 20;
if($postnum > $limit){
$tagend = round($postnum % $limit,0);
$splits = round(($postnum - $tagend)/$limit,0);

if($tagend == 0){
$num_pages = $splits;
}else{
$num_pages = $splits + 1;
}

if(isset($_GET['pg'])){
$pg = $_GET['pg'];
}else{
$pg = 1;
}
$startpos = ($pg*$limit)-$limit;
$limstring = "LIMIT $startpos,$limit";
}else{
$limstring = "LIMIT 0,$limit";
}

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

then in your existing query, just add the LIMIT string like:

SELECT * FROM yourtable
ORDER BY whatever
$limstring

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

then wherever you want the page navigation links, do this

///////////////// pagination links
if($postnum > $limit){
echo "<strong>Pages:</strong> &nbsp;&nbsp;&nbsp;";
$n = $pg + 1;
$p = $pg - 1;
$thisroot = $_SERVER['PHP_SELF'];
if($pg > 1){
echo "<a href=\"$thisroot?pg=$p\"><< prev</a>&nbsp;&nbsp;";
}
for($i=1; $i<=$num_pages; $i++){
if($i!= $pg){
echo "<a href=\"$thisroot?pg=$i\">$i</a>&nbsp;&nbsp;";
}else{
echo "$i&nbsp;&nbsp;";
}
}
if($pg < $num_pages){
echo "<a href=\"$thisroot?pg=$n\">next >></a>";
}
echo "&nbsp;&nbsp;";
}
////////////// end pagination

On some scripts I do some math to avoid showing every page link... just show 5 on either side of the current page, etc.. but this does the job with a lot less code than most scripts use.

eelixduppy

WebmasterWorld Senior Member eelixduppy us a WebmasterWorld Top Contributor of All Time 5+ Year Member



 
Msg#: 3353576 posted 4:07 pm on May 30, 2007 (gmt 0)

I wrote this a few months back: [webmasterworld.com...]

Maybe it will also give you some ideas. :)

bysonary

5+ Year Member



 
Msg#: 3353576 posted 4:34 pm on May 30, 2007 (gmt 0)

Cheers guys

slade7 I am been putting your code in with my code (thanks for that its much appreciated) and It diaplays the page links perfect but the number of records per page isnt correct, i made the limit 5 and its still displaying all of the records, for each page. Incidently there are 5 pages being listed. I just changed it to 20 like it was in your example and the page shows more than 20 records, there are 2 pages and each page shows the exact same records (basically all the records in the database)

heres the code, bit long now but if you could take a look and make sure I have done this properly I would appreciate it.

<form name="searchip" action="<?php $_SERVER['PHP_SELF']?>" method="post">
<input type="text" name="search" />
<input type="submit" name="submitsearch" value="Search" />
</form>
<?php
//$_POST['search'] = "gdfgdfg";
require_once('/home/www/juttuffi/lib/dbcon.php');

//////////////////////////
$getcount = mysql_query ("SELECT * FROM iplog");
$postnum = mysql_result($getcount,0);
$limit = 20;
if($postnum > $limit)
{
$tagend = round($postnum % $limit,0);
$splits = round(($postnum - $tagend)/$limit,0);

if($tagend == 0)
{
$num_pages = $splits;
}
else
{
$num_pages = $splits + 1;
}

if(isset($_GET['pg']))
{
$pg = $_GET['pg'];
}
else
{
$pg = 1;
}

$startpos = ($pg*$limit)-$limit;
$limstring = "LIMIT $startpos,$limit";
}
else
{
$limstring = "LIMIT 0,$limit";
}
/////////////////////////////////////

//$selQuery = mysql_query("SELECT * FROM iplog ORDER BY stamp DESC");
if(!isset($_POST['search']))
{
mysql_real_escape_string($search = $_POST['search']);
if($search == "")
{
$selQuery = mysql_query("SELECT * FROM iplog ORDER BY stamp DESC $limitstring");
}
else
{
$selQuery = mysql_query("SELECT * FROM iplog WHERE ip='$search' ORDER BY stamp DESC $limitstring");
}
}
else
{
mysql_real_escape_string($search = $_POST['search']);
$selQuery = mysql_query("SELECT * FROM iplog WHERE ip='$search' ORDER BY stamp DESC $limitstring");
}

if(mysql_num_rows($selQuery) == 0)
{
?>
<table cellspacing="0" cellpadding="0" class="titletable">
<tr>
<td class="td1" width="35%">Hostname</td>
<td class="td1" width="20%">IP</td>
<td class="td1" width="30%">Date</td>
<td class="td1" width="15%">Time</td>
</tr>
</table>
<table width="100%" cellspacing="0" cellpadding="0" class="borderfull">
<tr>
<td width="100%">Sorry your search for <? echo $search;?> returned <? echo mysql_num_rows($selQuery);?> Results</td>
</tr>
</table>
<?
////////////////////////
if($postnum > $limit)
{
echo "<strong>Pages:</strong> &nbsp;&nbsp;&nbsp;";
$n = $pg + 1;
$p = $pg - 1;
$thisroot = $_SERVER['PHP_SELF'];

if($pg > 1)
{
echo "<a href=\"$thisroot?pg=$p\"><< prev</a>&nbsp;&nbsp;";
}

for($i=1; $i<=$num_pages; $i++)
{
if($i!= $pg)
{
echo "<a href=\"$thisroot?pg=$i\">$i</a>&nbsp;&nbsp;";
}
else
{
echo "$i&nbsp;&nbsp;";
}
}

if($pg < $num_pages)
{
echo "<a href=\"$thisroot?pg=$n\">next >></a>";
}
echo "&nbsp;&nbsp;";
}
/////////////////
}
else
{
?>
<table cellspacing="0" cellpadding="0" class="titletable">
<tr>
<td class="td1" width="35%">Hostname</td>
<td class="td1" width="20%">IP</td>
<td class="td1" width="30%">Date</td>
<td class="td1" width="15%">Time</td>
</tr>
</table>
<table width="100%" cellspacing="0" cellpadding="0" class="borderfull">
<?
while($selResult = mysql_fetch_array($selQuery,MYSQL_ASSOC))
{
$i = ($i + 1) % 2;
//echo "<table border=\"0\" cellspacing=\"0\" cellpadding=\"0\" width=\"100%\" class=\"table_row$i\">";
echo "<tr class=\"table_row$i\">";
echo '<td width="35%">'.$selResult['hostname'].'</td>';
echo '<td width="20%">'.$selResult['ip'].'</td>';
echo '<td width="30%">'.$selResult['date'].'</td>';
echo '<td width="15%">'.$selResult['time'].'</td>';
echo '</tr>';
}
?>
</table>
<?
////////////////////////
if($postnum > $limit)
{
echo "<strong>Pages:</strong> &nbsp;&nbsp;&nbsp;";
$n = $pg + 1;
$p = $pg - 1;
$thisroot = $_SERVER['PHP_SELF'];

if($pg > 1)
{
echo "<a href=\"$thisroot?pg=$p\"><< prev</a>&nbsp;&nbsp;";
}

for($i=1; $i<=$num_pages; $i++)
{
if($i!= $pg)
{
echo "<a href=\"$thisroot?pg=$i\">$i</a>&nbsp;&nbsp;";
}
else
{
echo "$i&nbsp;&nbsp;";
}
}

if($pg < $num_pages)
{
echo "<a href=\"$thisroot?pg=$n\">next >></a>";
}
echo "&nbsp;&nbsp;";
}
/////////////////
}
mysql_close($dbc);
?>

I haven't had a propper chance to fully study the code so not sure where things could be going wrong however could it be the initial count query?

Hope you can help me on this one, if i can get this working I would be so pleased :-)

Thanks Again

ChrisW

slade7

10+ Year Member



 
Msg#: 3353576 posted 11:58 pm on May 30, 2007 (gmt 0)

use $limstring instead of $limitstring in your query

I'm a notorious abbreviator :)

Also, eelixduppy in the link he gave - look at how he sanitizes the $pg input to make sure it's a numerical value - it is a good idea to incorporate that.

slade7

10+ Year Member



 
Msg#: 3353576 posted 12:04 am on May 31, 2007 (gmt 0)

oh and if you are showing search results instead of just a list, you will have to pass whatever it was they were searching for from page to page (via GET) - in the page links at bottom.

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved