Re: count() total records for pagination with limit

PJ wrote:

I seem to recall that it is possible to count all instances of a query that is limited by $RecordsPerPage without repeating the same query. I believe that COUNT() had to called immediately after the SELECT word but I neglected to bookmark the source. Dummy!

You're probably thinking of SQL_CALC_FOUND_ROWS (http://dev.mysql.com/doc/refman/5.0/en/limit-optimization.html) It's not always faster though (http://www.mysqlperformanceblog.com/2007/08/28/to-sql_calc_found_rows-or-not-to-sql_calc_found_rows/).

I don't like the idea of count(*) over count() or something like that as it seems rather slow fram what I read. right now I do this: $sql = "SELECT * FROM book

        WHERE id IN (SELECT bookID
        FROM book_author WHERE authID IN (SELECT author.id
        FROM author WHERE LEFT(last_name, 1 ) = '$Auth')) ";

$Count1 = mysql_num_rows(mysql_query($sql, $db));

The problem with this is if your '*' includes 50 fields (from all of the tables in the joins etc) then that is still processed in mysql taking up memory especially. Doing a count() just has 1 field - the count. $sql = "select count(1) as count from book .... "; $result = mysql_query($sql, $db); $row = mysql_fetch_assoc($result); $count = $row['count'];

$Count gives me the actual rows for display - $Count1 gives me the total rows available. Can this be streamlined any?

Not really. -- Postgresql & php tutorials http://www.designmagick.com/