how to get the total row count with mysqli

问题:

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.

回答1:

using mysqli you do it the following way (assuming the mysqli object is already created -- you can also use the procedure methods, just slightly different):

$sql = "SELECT SQL_CALC_FOUND_ROWS *, post.id as pid, bla bla 
        FROM account ORDER BY pid ASC". $eb["array"]['querylimit'];
$result = $mysqli->query($sql);
$TotalRcount = $result->num_rows;
while($row=$result->fetch_assoc()){
    $col1 = $row['col1'];  // col1 is a placeholder for whatever column you are reading
    //read columns
}

回答2:

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.

回答3:

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.