Welcome to WebmasterWorld Guest from 54.167.175.157

Forum Moderators: coopster & jatar k

PHP/MySQL pagination

   
3:20 pm on May 30, 2007 (gmt 0)

5+ Year Member



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

4:01 pm on May 30, 2007 (gmt 0)

10+ Year Member



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.

4:07 pm on May 30, 2007 (gmt 0)

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



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

Maybe it will also give you some ideas. :)

4:34 pm on May 30, 2007 (gmt 0)

5+ Year Member



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

11:58 pm on May 30, 2007 (gmt 0)

10+ Year Member



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.

12:04 am on May 31, 2007 (gmt 0)

10+ Year Member



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.
 

Featured Threads

Hot Threads This Week

Hot Threads This Month