Is there a way to use SELECT FOUND_ROWS() in php and mysqli?

Question

I'm trying to implement a pagination system into my website and can't get past this one stubborn error. I'm not exactly sure if you can use FOUND_ROWS() in mysqli as opposed to PDO but that's what I came here for.

I have the latest version of PHP and everything worked up to this point in the pagination.

$stmt = $conn->prepare('SELECT count(*) FROM owned_assets WHERE uid=? AND type=? LIMIT '.$start.', '.$rLim);
    $stmt->bind_param('ii', $uid, $assetType);
    $stmt->execute();
    $total = $conn->query('SELECT FOUND_ROWS() as total')->mysqli_fetch_array()['total'];

What's supposed to happen (so far) is mysql will count the rows found within the matching query and I can work further on from there.

This is my current error:

Fatal error: Uncaught Error: Call to a member function mysqli_fetch_array() on bool

hmm, adding another query to use `FOUND_ROWS()` for the sake of getting the count is not needed, just fetch and use the initial query return value — Kevin, Jul 10 '19 at 03:08

Your Common Sense · Answer 1 · 2019-07-10T04:43:34.443

Of course there is but you have to follow the proper routine.

Your current query makes no sense as it counts the number you already know, stored in $rLim (all right not always but that's not the point).

To use FOUND_ROWS() for pagination you must add SQL_CALC_FOUND_ROWS to your query that fetches the data for a single page.

Then you will be able to get the total number of rows without limit by means of running another query with FOUND_ROWS().

That said, using SQL_CALC_FOUND_ROWS is not recommended as it is as slow as fetching all rows without LIMIT. And this is the reason why this function was recently deprecated.

So you have to make two queries, one fetching the actual data with LIMIT clause and one with count(*) and without LIMIT.

Kevin · Accepted Answer · 2019-07-10T05:52:44.620

0

You already have the counting query, just fetch that value. No need for another second query FOUND_ROWS().

$stmt = $conn->prepare('SELECT count(*) FROM owned_assets WHERE uid=? AND type=?');
$stmt->bind_param('ii', $uid, $assetType);
$stmt->execute();
$result = $stmt->get_result();
$total = $result->fetch_assoc();
echo $total['count(*)'];

Sidenote: You can use an alias to count(*) AS total in the query, and access the index as $total['total'] in the return value.

edited Jul 10 '19 at 05:52

answered Jul 10 '19 at 03:12

Kevin

41,694
12
53
70

Well it worked when I made another query doing that (as when I did it with the first one it wouldn't return other values) – RandomRando Jul 10 '19 at 03:22
@RandomRando and actually doing `FOUND_ROWS()` right after the count will yield the incorrect result, I have not tested but I think doing `FOUND_ROWS()` will give you `1` every time since `count()` gives you one row. it won't work the way you're expecting it to be. remove the count and replace it to `SELECT *` then `FOUND_ROWS()` is the correct sequence. but that would need two trips, just use `COUNT(*)` and then fetch that column and thats it. – Kevin Jul 10 '19 at 03:41
This query is essentially useless as it counts the predefined number of rows. Just saying. – Your Common Sense Jul 10 '19 at 04:26
@YourCommonSense good point, it should have not been there in the first place having the limit – Kevin Jul 10 '19 at 05:53

score -1 · Answer 3 · answered Jul 10 '19 at 03:13

-1

You don't need another query to get the total number of rows, as you already have it with COUNT(). You can just bind the result of the first query to the $total with bind_result():

$stmt->bind_result($total);

answered Jul 10 '19 at 03:13

Obsidian Age

41,205
10
48
71

Is there a way to use SELECT FOUND_ROWS() in php and mysqli?

3 Answers3