Pagination Query

Calculating 'Prev'

[woldie]

Hello,

I've seen numerous posts for this popular thread on other forums.

However I have a problem calculating 'prev', calculating the 'next' is not a problem. Can anyone assist me on this?

Here's the code.....

<?
if (!isset($offset)) $offset=0;

$result=mysql_query("select aid,field1,field2,field3,field4,field5
from tablename LIMIT $offset,5");
while (list($DBfield1,$DBfield2,$DBfield3,$DBfield4,$DBfield5,$DBfield6)=mysql_fetch_row($result))
{
?>
<tr>
<td><a href="goto.php?aid=<? echo $DBfield1?>"><? echo $DBfield2. ' ' .$DBfield3. ' ' .$DBfield4?></a></td>
<td><? echo $DBfield5?></td>
<td><? echo $DBfield6?></td>
</tr>
<?
}
?>
</table>
<?
if (mysql_num_rows($result)==5)
{
$offset+=5;
echo "<div align=right><a href=\"index.php?offset=$offset\">Next...</a></div>";
}
?>

Many Thanks

:o)

[ukgimp]

[webmasterworld.com...]

[tomda]

Hmmm,

The best way is to count the total number of items (same query but without LIMIT)
$result2=mysql_query("select aid,field1,field2,field3,field4,field5
from tablename");
$count2 = mysql_num_rows($result2);
********************
Now that you have the total number of items, it is easier.
TIP : Call your value 5 by a php variable (here $qty=5) - so that later on users can define their own number

NEXT PAGE SHOULD BE
if($offset+$qty<$count2) {
$offset_beta=$offset+$qty;
echo "<div align=right><a href=\"index.php?offset=$offset_beta\">Next...</a></div>";}

PREVIOUS PAGE SHOULD BE
if ($offset>$count2 ¦¦ $offset>0)
{$offset_beta=$offset-$qty;
echo "<div align=right><a href=\"index.php?offset=$offset_beta\">Previous...</a></div>";}
**************************

This script has not been tested...
Try it and report back

[ssmmxx]

Try This:

<?php
if(!isset($_GET['page'])){
$page = 1;
} else {
$page = $_GET['page'];
}

$max_results = PUT A NUMBER HERE;

$from = (($page * $max_results) - $max_results);

$sql = mysql_query("SELECT * FROM `DATABASE` WHERE `FIELDA` = '$FIELDA' LIMIT $from, $max_results") or die (mysql_error());
$num_rows = mysql_num_rows($sql);

if($_GET['FILEDA'] == "")
{
echo "No query specified please try again.";
}
elseif($num_rows == 0)
{
print "No results available please try again.";
}
else
{

while(list($FIELDA, FIELDB)=mysql_fetch_array($sql)){

//Your output data goes here....

}

$total_results = mysql_num_rows($sql);
$max_results = 50;
$prev = ($page - 1);
$next = ($page + 1);
$total_pages = ceil($total_results / $max_results);
echo "<div align=center>";

if($page > 1){
echo "&lt;&lt;<a href=RESULTS-PAGE.php?FIELDA=", urlencode ($FIELDA),"&page=$prev>&nbsp;Previous</a> - ";
}

for($i = 1; $i <= $total_pages; $i++){
if(($page) == $i){
echo "$i";
} else {
echo "<a href=RESULTS-PAGE.php?FIELDA=", urlencode ($FIELDA),"&page=$i> $i </a>";
}
}

if($page < $total_pages){

echo " - <a href=RESULTS-PAGE.php?FIELDA=", urlencode ($FIELDA),"&page=$next>Next</a>&nbsp;&gt;&gt;";
} elseif($page = $total_pages) {
}}

echo "</div>";

?>

You can just remove the sections you dont need but that script does work.

[woldie]

Thanks Tomda,

It kinda of works, however I've posted the full code. Because I want to display 5 records then hit 'next' then on the next page hit 'previous', because I know I have 7 records in total.

At the moment it displays 7 records when I run the script as it is, instead of displaying 5 records.

You see what I mean.

Perhaps I've placed some code in the wrong place.

Thanks in advance

:o)

<?
if (!isset($offset)) $offset=0;

$result2=mysql_query("select aid,field1,field2,field3,field4,field5
from tablename LIMIT $offset,5");
while (list($DBfield1,$DBfield2,$DBfield3,$DBfield4,$DBfield5,$DBfield6)=mysql_fetch_row($result2))
{
?>
<tr>
<td><a href="goto.php?aid=<? echo $DBfield1?>"><? echo $DBfield2. ' ' .$DBfield3. ' ' .$DBfield4?></a></td>
<td><? echo $DBfield5?></td>
<td><? echo $DBfield6?></td>
</tr>
<?
}
?>
</table>

<?
$qty=5;

// NEXT PAGE SHOULD BE
if ($offset+$qty<$count2)
{
$offset_beta=$offset+$qty;
echo "<div align=right><a href=\"index.php?offset=$offset_beta\">Next...</a></div>";
}

// PREVIOUS PAGE SHOULD BE
if ($offset>$count2 ¦¦ $offset>0)
{
$offset_beta=$offset-$qty;
echo "<div align=right><a href=\"index.php?offset=$offset_beta\">Previous...</a></div>";
}
?>

[woldie]

Thanks ssmmxx

i'll give that a go...

many thanks

[ssmmxx]

If you get any errors its because I removed all the stuff that was there before ( I ripped the code from one of my sites ). Shouldnt be any errors but you never know. Easily fixed though so tell me if you have any problems.

[woldie]

ssmmxx,

It nearly works....

However I've set the $max_results=5, and what it does it displays the first 5 records, but it doesn't display the 'next' option, just displays the number 1. There is 7 records in total, so it should display the next 2 on another page.

I've modified the code a little...

Heres the code

<?
if(!isset($page))
{
$page = 1;
}
else
{
$page = $page;
}

$max_results = 5;
$from = (($page * $max_results) - $max_results);

$result=mysql_query("select *
from tablename
LIMIT $from, $max_results ");
$num_rows = mysql_num_rows($result);

while (list($DBfielda,$DBfieldb)=mysql_fetch_row($result))
{
?>
<tr>
<td class="webbtd"><a href="edit.php?aid=<? echo $DBaid?>"><? echo $DBtitle. ' ' .$DBfirstname. ' ' .$DBlastname?></a></td>

</tr>
<?
}
?>
</table>
<?
$total_results = mysql_num_rows($result);
$max_results = 50;
$prev = ($page - 1);
$next = ($page + 1);
$total_pages = ceil($total_results / $max_results);
echo "<div align=center>";

if($page > 1)
{
echo "&lt;&lt;<a href=index.php?page=$prev>&nbsp;Previous</a> - ";
}

for($i = 1; $i <= $total_pages; $i++)
{
if(($page) == $i)
{
echo "$i";
}
else
{
echo "<a href=index.php?&page=$i> $i </a>";
}
}

if($page < $total_pages)
{
echo " - <a href=index.php?page=$next>Next</a>&nbsp;&gt;&gt;";
}
elseif ($page = $total_pages)
{
}

echo "</div>";

?>

Thanks

:o)

[ssmmxx]

Its easy to modify :) Does it work now?

Give me the URL as well and so I can see your handy work :)

It should simply be a case of changing the $i to next although if you have more than 2 pages you would see next next next next all as links which would be somewhat silly :)

[ssmmxx]

if($page < $total_pages)
{
echo " - <a href=index.php?page=$next>Next</a>&nbsp;&gt;&gt;";
}
elseif ($page = $total_pages)
{
}

That should be

if($page < $total_pages)
{
echo " - <a href=index.php?page=$next>Next</a>&nbsp;&gt;&gt;";
}
if ($page = $total_pages)
{
}

[woldie]

ssmmxx

It still doesn't work :o(.

I just get '1' displayed.

Can I sticky you the URL?

Thanks

[ssmmxx]

Hang on I made a boob :)

[ssmmxx]

Sticky away.

[woldie]

What 'boob' did you make?

Thanks

[ssmmxx]

for($i = 1; $i <= $total_pages; $i++)
{
if(($page) == $i)
{
echo "$i";
}
else
{
echo "<a href=index.php?&page=$i> $i </a>";
}
}

Should be

for($i = 1; $i <= $total_results; $i++){

if($i == $page){
echo " $i ";
} elseif ($i > $total_pages){
echo "";}
else{
echo "<a href=index.php?&page=$i> $i </a>";
}
}}

I screwed up the total results so that it displayed about 66 pages and I missed something else out. Shows what happens when you dont think about something properly :)

[woldie]

I'm afraid it still doesn't work, there's something going astray.

I've left the DB fieldnames in this time

Thanks.

here's the full code:

<?
if(!isset($page))
{
$page = 1;
}
else
{
$page = $page;
}

$max_results = 5;

echo "Page: $page".'<br><br>';
$from = (($page * $max_results) - $max_results);
echo "From: $from".'<br><br>';
echo "Max Results: $max_results".'<br><br>';

$result=mysql_query("select aid,title,firstname,lastname,injury_type,applieddate
from accident
LIMIT $from, $max_results ");
$num_rows = mysql_num_rows($result);

while (list($DBaid,$DBtitle,$DBfirstname,$DBlastname,$DBinjury_type,$DBdate)=mysql_fetch_row($result))
{
?>
<tr>
<td class="webbtd"><a href="acc-edit.php?aid=<? echo $DBaid?>"><? echo $DBtitle. ' ' .$DBfirstname. ' ' .$DBlastname?></a></td>
<td class="webbtd"><? echo $DBinjury_type?></td>
<td class="webbtd"><? echo $DBdate?></td>
</tr>
<?
}
?>
</table>
<?
$total_results = mysql_num_rows($result);
$max_results = 50;
$prev = ($page - 1);
$next = ($page + 1);
$total_pages = ceil($total_results / $max_results);
echo "total Pages: $total_pages".'<br><br>';
echo "<div align=center>";

if($page > 1)
{
echo "&lt;&lt;<a href=index.php?page=$prev>&nbsp;Previous</a> - ";
}

for($i = 1; $i <= $total_results; $i++)
{
if($i == $page)
{
echo " $i ";
}
elseif ($i > $total_pages)
{
echo "";
}
else
{
echo "<a href=index.php?&page=$i> $i </a>";
}
}

if($page < $total_pages)
{
echo " - <a href=index.php?page=$next>Next</a>&nbsp;&gt;&gt;";
}

if ($page = $total_pages)
{
}

echo "</div>";

?>