0

I'm executing a SQL Statement in PHP, using LIMIT $start, $offset to paginate results.

An unexpected hurdle, is $stmt->num_rows is returning all rows regardless of SQL LIMIT. Here is my code, just incase I haven't made a silly mistake:

$term = '%'.$search.'%'; // I have used htmlspecialchars() above
    $reclimit = 20;
    $start = ($page - 1) * $reclimit;
    $offset = ($page + 2) * $reclimit; // I want to get 60 results in total, or 3 pages of results.

    $mysqli = new mysqli("localhost","username","password","db");

    $stmt = $mysqli->prepare("SELECT title,description FROM content WHERE title LIKE ? LIMIT $start, $offset");

    $stmt->bind_param('s', $term);
    $stmt->execute();

    $stmt->store_result();
    $stmt->bind_result($title, $desc);

    $rowcount = $stmt->num_rows; // This will get all results, regardless of `LIMIT`
    $tpages = ceil($rowcount / $reclimit); // $rowcount returning incorrect integer messes up my pagination

    echo $start;
    echo $offset;
    echo $rowcount;
    echo $tpages; // Testing all variables to see which was the culprit

    $wcount = 0;
    $wmax = 20;

    while(($stmt->fetch()) and ($wcount < $wmax))
    {

        // Echoing HTML --snipped--

        $wcount++;
    }

    $stmt->close();

    $mysqli->close();
    }

I can't see anything I've done incorrectly, and it seems strange $rowcount would return a value from all returned rows, regardless of LIMIT as the data returned has been limited before the sum has been called. Have I misunderstood something?

Thanks for your time

PublicDisplayName
  • 325
  • 5
  • 17
  • Possible duplicate of [Get total number of rows when using LIMIT?](http://stackoverflow.com/questions/12887266/get-total-number-of-rows-when-using-limit) – Jeff Dec 10 '16 at 02:10
  • http://stackoverflow.com/questions/5928611/find-total-number-of-results-in-mysql-query-with-offsetlimit – Jeff Dec 10 '16 at 02:11
  • 1
    What about the actual result? Do you have all the records fetched or only 60 records? – EhsanT Dec 10 '16 at 02:46
  • I cant get it to not work! I've had a play with your code and besides being able to blow it up with illegal $start and $offset values, it behaves. Only an empty search string gives me all rows as expected. Mysql 5.7.11, PHP 5.6.19 and PHP 7.04. i.e I get the expected rowcount values. – TimBrownlaw Dec 10 '16 at 02:53
  • 1
    The syntax for limit is `LIMIT offset, row_count`, not `LIMIT start, end`. For (just) your first page you should give you your 60 rows though. – Solarflare Dec 10 '16 at 12:00
  • @EhsanT It only fetches 60 results, but counts all regardless of `Limit start,end` – PublicDisplayName Dec 10 '16 at 12:54
  • 1
    have you tried moving this line: `$rowcount = $stmt->num_rows;` immediately after this line: `$stmt->store_result();` because according to [php manual](http://php.net/manual/en/mysqli-stmt.num-rows.php) _If you use mysqli_stmt_store_result(), mysqli_stmt_num_rows() may be called immediately._ – EhsanT Dec 10 '16 at 13:12
  • @EhsanT it appears my `$offset` var doesn't have a value, despite `$start` having one. No idea why this is happening - thanks for the suggestion – PublicDisplayName Dec 10 '16 at 16:02
  • 1
    It's weird that `$offset` variable does not have a value. You are setting it right after `$start`. And then it's more weird, even if because of `$offset` not having a value and you get the value of all records with `$stmt->num_rows` then you should also have all records in your `resultset` not only 60 records! – EhsanT Dec 10 '16 at 22:04
  • @EhsanT I managed to fix all of the issues and got it working correctly. I decided to hardcode `$offset` to 60 as I only want to fetch 60 results from the current `$start` - amongst other issues it's working now. Thanks for your replies, been very helpful! – PublicDisplayName Dec 10 '16 at 23:48
  • :) great, have fun coding... – EhsanT Dec 10 '16 at 23:49

0 Answers0