Hello every.
I have table news on database mysql.
When display list news on page, I must get total rows for paging.

After, I often use 2 statement mysql for on task, but I see a php coder guru only use a statement mysql.

Please help me use one statement for this solve.

Use mysql_num_rows() after SELECT query.

Thank for reply.

The proper way to do this is with 2 queries:

PHP Code:

$page = 1;
$perPage = 10;
$start = ($page - 1) * $perPage + 1;
$where = " WHERE condition = 'value'";

$countSql = "SELECT COUNT(1) as num_records FROM my_table {$where}";
$resultSql = "SELECT * FROM my_table {$where} LIMIT {$start}, {$perPage}";

$count = mysql_query($countSql);
$count = mysql_fetch_assoc($count);
$total = $count["num_records"];  // Total number of records in your table

$results = mysql_query($resultSql);

$displayCount = mysql_num_rows($results);  // 
$end = $start + $displayCount;

$displayText = "Displaying {$start} to {$end} of {$total} results."; 


The first query will return the total number of records matching your WHERE clause, and the second will return 10 records on the page specified.

If you use only the 2nd query without a limit, you'll retrieve all the data from your database (could be thousands or even millions of records), which will put an unnecessary load on the DB.

The above is the proper solution for maximum performance.

Other way to solve.

PHP Code:

$sql="SELECT SQL_CALC_FOUND_ROWS {$this->listField} FROM ".$this->tableName." WHERE {$condition} ORDER BY {$order} LIMIT {$limit}";
            
$db->query($sql);//get list records to display
$result=$db->fetchAll($key);
//get total row effect
$rows = $db->query("SELECT FOUND_ROWS() AS total_rows");
$total_row = (int)$rows[0]['total_rows']; 


SQL_CALC_FOUND_ROWS is often (usually?) slower than running a separate limit query and count. And by the way, it doesn't simplify anything on the PHP side... you still have to process 2 SQL queries.

Edit: another point... SQL_CALC_FOUND_ROWS is MySQL specific... best to get in the habit of writing SQL code that is as cross-DB compliant as possible... if you've ever had to convert from one engine to another, or write an app that would be iinstallable on multiple DB types, you know what I'm talking about.

Generally - you are right, but not 100% of all cases. It depends on how frequently are queried table updated. More info here

About what could be simplified - a lot. Example, your application DB abstract layer could 'listen' for 'SQL_CALC_FOUND_ROWS' in select query and execute auto-magically 'SELECT FOUND_ROWS()' (and store the total result).

Quick example:

PHP Code:

$this->total_results = false;
if ((strpos($query,'SQL_CALC_FOUND_ROWS') !== FALSE) && (strpos($query,'SELECT') !== FALSE))
{    // Need to get the total record count as well. Return code is a resource identifier
    // Have to do this before any debug action, otherwise this bit gets messed up
    $fr = mysql_query('SELECT FOUND_ROWS()', $this->mySQLaccess);
    $rc = mysql_fetch_array($fr);
    $this->total_results = (int) $rc['FOUND_ROWS()'];
} 


You could do the same with a COUNT query. However, you could use SQL_CALC_FOUND_ROWS in SQL without having to set parameters in a query or stored procedure, for example:

SELECT SQL_CALC_FOUND_ROWS, * FROM my_table;
INSERT INTO my_table_results (rows, search_date) VALUES (FOUND_ROWS(), CURRENT_TIMESTAMP);

You could theoretically run this in one query using mysqli, which would return your single resultset, meaning you'd never have to bother working with the row count.