如何获取PHP mysqli预处理语句不带LIMIT的查询结果总行数

你当前$sqlitemsparse语句中保留了预处理占位符?，直接调用mysqli_query执行时没有传入对应的绑定参数$classid，导致SQL执行失败返回布尔值false，将false传入mysqli_num_rows时就触发了参数类型不匹配的警告。

推荐使用COUNT(*)构造专门的统计查询获取总行数，该方案不需要拉取全量符合条件的数据，性能远高于查询全量数据后统计行数：

$conn = new mysqli("localhost", "xxxx", "xxxx", "xxxx");
// 绑定变量
$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);
    $rowsitemsparse = [];
    while($rowitemsparse = mysqli_fetch_assoc($resultitemsparse)) {
        $rowsitemsparse[] = $rowitemsparse;
    }
}

// 新增统计总行数逻辑
$count_sql = "SELECT COUNT(*) AS total FROM itemSparse INNER JOIN item ON item.id = itemSparse.id WHERE item.ClassID = ?";
$count_stmt = mysqli_stmt_init($conn);
if (mysqli_stmt_prepare($count_stmt, $count_sql)) {
    mysqli_stmt_bind_param($count_stmt, "s", $classid);
    mysqli_stmt_execute($count_stmt);
    $count_result = mysqli_stmt_get_result($count_stmt);
    $count_row = mysqli_fetch_assoc($count_result);
    $number_filter_row = $count_row['total'];
}

如果你的业务场景确实需要拉取全量不带LIMIT的所有数据，也可以先执行不带LIMIT的$sqlitemsparse预处理，通过mysqli_stmt_num_rows($stmt)获取总行数后再处理分页逻辑，但该方案仅适合数据量极小的场景。

内容的提问来源于stack exchange，提问作者Berstos