Get amount of rows in prepared statement

Question

I'm using a prepared statement query which is builded with a string concatenation:

$sqlitemsparse- the "Select" clause
$sqlitemsparse . - the "Where" clause
$sqlitemsparse2 - the "Limit" selection

This is working as expected.

Now, additionally, I need the amount of result rows from the $sqlitemsparse and $sqlitemsparse . query (without the "LIMIT" selection).

I've tried it with the mysqli_num_rows function in combination with a new mysqli_query but I get the following error:

Warning: mysqli_num_rows() expects parameter 1 to be mysqli_result, bool given in...

Code:

$conn = new mysqli("localhost", "xxxx", "xxxx", "xxxx");

// Variable to bind
$classid = "4";

$sqlitemsparse = "SELECT * FROM itemSparse INNER JOIN item ON item.id = itemSparse.id";

$sqlitemsparse.= " WHERE item.ClassID = ?";

$sqlitemsparse2 = " LIMIT 0, 10";

$stmt = mysqli_stmt_init($conn);

if (!mysqli_stmt_prepare($stmt, $sqlitemsparse . $sqlitemsparse2)) {
    echo "SQL Failed";
} else {

    mysqli_stmt_bind_param($stmt, "s", $classid);
    mysqli_stmt_execute($stmt);

    $resultitemsparse = mysqli_stmt_get_result($stmt);

    while($rowitemsparse = mysqli_fetch_assoc($resultitemsparse)) {
        $rowsitemsparse[] = $rowitemsparse;
    }
}

$number_filter_row = mysqli_num_rows(mysqli_query($conn, $sqlitemsparse));

First of all, enable error reporting for mysqli [How to get the error message in MySQLi?](https://stackoverflow.com/a/22662582/1839439) — Dharman, Aug 23 '21 at 11:38
I enabled the error reporting and the output is the "Warning" message. My idea was to repeat the `sqlitemsparse` query so I can count the rows without the `$sqlitemsparse2` limit query. — Berstos, Aug 23 '21 at 11:41

score 0 · Accepted Answer · answered Aug 23 '21 at 11:46

If you want to get the count of the rows matched by the same prepared statement but without the LIMIT then you need to execute another prepared statement, but this time with SELECT COUNT(*) instead of SELECT *.

<?php

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$conn = new mysqli("localhost", "xxxx", "xxxx", "xxxx");

// Variable to bind
$classid = "4";

$sqlitemsparse = "SELECT * FROM itemSparse INNER JOIN item ON item.id = itemSparse.id";
$sqlitemsparse .= " WHERE item.ClassID = ?";

$sqlitemsparse2 = " LIMIT 0, 10";

$stmt = $conn->prepare($sqlitemsparse . $sqlitemsparse2);
$stmt->bind_param('s', $classid);
$stmt->execute();
$resultitemsparse = $stmt->get_result();
$rowsitemsparse = $resultitemsparse->fetch_all(MYSQLI_ASSOC);

$sqlitemsparse = "SELECT COUNT(*) FROM itemSparse INNER JOIN item ON item.id = itemSparse.id";
$sqlitemsparse .= " WHERE item.ClassID = ?";
$stmt = $conn->prepare($sqlitemsparse);
$stmt->bind_param('s', $classid);
$stmt->execute();
$resultitemsparse = $stmt->get_result();
$row_count = $resultitemsparse->fetch_row()[0]; // first column of first row

So there is no other possibility than modifying the `$sqlitemsparse` query with `SELECT COUNT(*)`? My initial idea was to use the exact same `$sqlitemsparse` two times. — Berstos, Aug 23 '21 at 11:56
@Berstos No. The other way would be to fetch everything from database into PHP and let PHP do all the work, but that would defeat the purpose of having a database. SQL can efficiently perform a count so this is really not a problem. — Dharman, Aug 23 '21 at 12:01

Get amount of rows in prepared statement

1 Answers1