LIMIT clause problems

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

Threaded View
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:

$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/
    $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?


Re: LIMIT clause problems

Quoted text here. Click to load it
 [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";

< Space: disk usage analysis tool

Re: LIMIT clause problems

Quoted text here. Click to load it

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.



Site Timeline