Posted by Mike on 08/06/05 13:42

New to PHP and MySQL.
Using PHP5 and MySQL 4.1
Windows XP Pro
IIS 5.1

I'm trying to page a recordset, and am using a LIMIT clause to fetch a
defined range of records from my db. However, the returned dataset is not
limited to the range I have in the SQL clause.

Here's the code:

<?php
$link_id=mysql_connect("localhost", "username", "password");
mysql_select_db("stories", $link_id);
global $records_per_page;

$records_per_page = 5;


$query = "SELECT StoryID FROM Stories";
$result = mysql_query($query);
$total_recs = mysql_num_rows($result);



if(empty($_GET['next_page'])) {
$_GET['next_page'] = 0;
}

$cur_page = $_GET['next_page'];
$page_num = $cur_page + 1;
$record = ($cur_page * $records_per_page) + 5;
$total_num_page = $last_page_num = ceil($total_recs/
$records_per_page);

$limit_str = "LIMIT ". $cur_page * $records_per_page . ", $record";
$query = "SELECT StoryID, Headline FROM STORIES ORDER BY Headline
$limit_str ";
$result = mysql_query($query);

while($query_data = mysql_fetch_array($result)) {
$story_id = $query_data["StoryID"];
$headline = $query_data["Headline"];
echo "<A href=\"story.php?id=$story_id\">$headline</a><br>\n";
}

if($page_num > 1) {
$prev_page = $cur_page - 1;
echo "<A href=\"paging.php?next_page=0\">[Top]</a>";
echo "<A href=\"paging.php?next_page=$prev_page\">[Prev]</a>";
}

if ($page_num < $total_num_page) {
$next_page = $cur_page + 1;
$last_page = $total_num_page - 1;
echo "<A href=\"paging.php?next_page=$next_page\">[Next]</a>";
echo "<A href=\"paging.php?next_page=$last_page\">[Last]</a>";


}

echo "<br>" . $limit_str

?>

I echo out the Limit clause, and it gives me what I would expect to see on
each page, but the actual number of records returned increases by 10 for
each page I navigate.

Interestingly, when I try the SQL in MySQL Query Browser, it seems to
ignore the upper boundary in the LIMIT clause.

I'm probably being a total noddy, but can anyone see what I've done wrong?

TIA
Mike

[Back to original message]