how to get the total row count with mysqli

Question

i am trying to understand mysqli extension and did google but got very few info on this except php.net which was helpful.

now after all this i am trying to achieve what i could with mysql extension which is as follows:

// MYSQL STYLE OF fetching array, query limit and perform total row count all at once

$sql = "SELECT SQL_CALC_FOUND_ROWS *, post.id as pid, bla bla FROM account ORDER BY pid ASC". $eb["array"]['querylimit'];

$result = mysql_query($sql, $eb["con"]);
$TotalRcount = mysql_fetch_row(mysql_query("SELECT FOUND_ROWS()"));

// Performing record count [current]
// $RecordCount = mysql_num_rows($result);

while($row = mysql_fetch_array($result)){
    // read columns
}

with mysqli how can i achieve this? am sure i am missing many things. please help me with example on how to achieve my goal.

Answer 1

You may try this:

//Establish connection using mysqli api
$conn = mysqli_connect('hostname', 'username', 'password', 'database_name');

$sql = "SELECT SQL_CALC_FOUND_ROWS *, post.id as pid, bla bla FROM account ORDER BY pid ASC". $eb["array"]['querylimit'];

$sql2 = "SELECT FOUND_ROWS()";

$result1 = $conn->query($sql);
$result2 = $conn->query($sql2);
$TotalRcount = $result2->fetch_row();

// Performing record count [current]
// $RecordCount = $result->num_rows();

while($row = $result->fetch_array(MYSQLI_BOTH)){
    // read columns
}

In a while loop i have used MYSQLI_BOTH constant but you may change it to MYSQLI_NUM or MYSQLI_ASSOC whichever you need.

Answer 2

SQL_CALC_FOUND_ROWS is generally used in SELECT statements with a LIMIT clause.

From the MySQL manual (https://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_found-rows):

A SELECT statement may include a LIMIT clause to restrict the number of rows the server returns to the client. In some cases, it is desirable to know how many rows the statement would have returned without the LIMIT, but without running the statement again. To obtain this row count, include a SQL_CALC_FOUND_ROWS option in the SELECT statement, and then invoke FOUND_ROWS() afterward

In your example above, you don't use LIMIT to keep the number of results down so using FOUND_ROWS() will only mean an extra call back to the database. Please check out the other answers here for information on how to get the number of rows returned. Good luck.