LIMIT clause problems

[Mike]

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

[Andy Hassall]

On Sat, 06 Aug 2005 10:42:33 GMT, Mike <us**@domain.com> wrote:

$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";

http://dev.mysql.com/doc/mysql/en/select.html
[LIMIT {[offset,] row_count | row_count OFFSET offset}]

It's not LIMIT from_offset, to_offset.
it's LIMIT offset, row_count.

So surely something more like:

$limit_str = "LIMIT ". $cur_page * $records_per_page . ", $records_per_page";

--
Andy Hassall / <an**@andyh.co.uk> / <http://www.andyh.co.uk>
<http://www.andyhsoftware.co.uk/space> Space: disk usage analysis tool

[Morris]

Andy Hassall <an**@andyh.co.uk> wrote in
news:29********************************@4ax.com:

On Sat, 06 Aug 2005 10:42:33 GMT, Mike <us**@domain.com> wrote:

$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";

http://dev.mysql.com/doc/mysql/en/select.html
[LIMIT {[offset,] row_count | row_count OFFSET offset}]

It's not LIMIT from_offset, to_offset.
it's LIMIT offset, row_count.

So surely something more like:

$limit_str = "LIMIT ". $cur_page * $records_per_page . ",
$records_per_page";

Yep, that works fine. Thanks.

The book I'm using wasn't clear how to use the LIMIT clause, and it
looked like it was from_offset, to_offset. The sample code provided
*definitely* produces from_offset, to_offset.

Wrox are normally very good, but "Beginning PHP5" is not up to standard.
I've noticed quite a few typos in sample code.

I'll just have to make more use of the official documentation.

Mike

Mike