homepage Welcome to WebmasterWorld Guest from 50.17.176.149
register, free tools, login, search, subscribe, help, library, announcements, recent posts, open posts,
Subscribe to WebmasterWorld
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




msg:3353578
 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




msg:3353625
 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




msg:3353626
 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




msg:3353652
 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




msg:3354015
 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




msg:3354017
 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.
Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About
© Webmaster World 1996-2014 all rights reserved