Pagination of MySQL Results

[cheechm]

Hello,
Could someone help paginate the following code? I have tried so many tutorials without results. I would be grateful if it could be 7 rows a page.
Thank you

<?php

$con = mysql_connect("***","***","***");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}

mysql_select_db("b7_463487_LPS", $con);

$query = mysql_query("SELECT * FROM points ORDER BY LastName ASC, FirstName ASC") or die(mysql_error());
$rows = mysql_num_rows($data);

while($row = mysql_fetch_array($query))
{

$link = 'addpoints.php?firstname=' . $row['FirstName'] . '&lastname=' . $row['LastName'] . '&Points=' . $row['Points'];
$link2 = 'confirmdelete.php?firstname=' . $row['FirstName'] . '&lastname=' . $row['LastName'];

echo $row['FirstName'] . " " . $row['LastName'] ." / ". $row['Tutor'] . " / " . $row['Points'] ;
echo " ";
echo " / ";
echo "<a href='".$link."'> +points </a>";
echo "/";
echo "<a href='".$link2."'> -delete </a>";
echo "/";

echo "<br />";
}

mysql_close($con)

?>

[iFrank]

I'm no expert, but here's how I'd do it:

Use a variable that lets PHP know what page we're on:

In your SELECT statement, add LIMIT 7, because that's how many rows you want on each page.

$query = mysql_query("SELECT * FROM points ORDER BY LastName ASC, FirstName ASC LIMIT 7") or die(mysql_error());


After the LIMIT 7 (and a comma), we have to add our offset (i.e., how many rows we're going to skip to get to the first of the seven we want to show). In your case, you don't know what the offset is, so we have to define it.

We can create a variable ($page, let's say) and multiply it by 7 (and create another variable from that), depending what page we're on. Page 2, for example, where we want to show rows 8-15, would have the URI: http://website.com/index.php?page=2

The code to go along with that would go like this:


$page = $_GET['page'];
$pageoffset = $page*7;

$query = mysql_query("SELECT * FROM points ORDER BY LastName ASC, FirstName ASC LIMIT 7,$pageoffset") or die(mysql_error());


I think think think this'll work. Again, I'm not an expert, so I'm not positive, but it seems right to me. Let me know how it works out!

[iFrank]

You might have to change "$pageoffset = $page*7" to this:

if ($page != ' ')
{ $pageoffset = 0; }
else
{ $pageoffset = $page*7; }

[cheechm]

Thank you. That works.

[Fumigator]

For a full-featured pagination class that takes about 10 minutes to integrate, check out http://www.firepages.com.au .