Counting MySQL records with a LIMIT

Question

As I am trying to count the number of records in a table, even when the SQL statement has a LIMIT into it, overall it works, however something weird happens, the code:

$sql = "SELECT COUNT(*) AS count FROM posts
        ORDER BY post_date DESC
        LIMIT 5";

// ... mysql_query, etc

while($row = mysql_fetch_array($result))
{
    // ... HTML elements, etc
    echo $row['post_title'];

    // ... HTML elements, etc

    echo $row['count']; // this displays the number of posts (which shows "12").
}

Although, when displaying through the while loop, it displays this:

Notice: Undefined index: post_title in /Applications/MAMP/htdocs/blog/index.php on line 55

If I remove the COUNT(*) AS count, everything will display perfectly... how come it's doing this?

Answer 1

Don't use COUNT(*) to count the number of rows (for a lot of reasons). Write out your full query, and add SQL_CALC_FOUND_ROWS right after SELECT:

SELECT SQL_CALC_FOUND_ROWS id, title FROM foo LIMIT 5;

Then, after that query executed (right after), run:

SELECT FOUND_ROWS();

That will return the number of rows the original SELECT would have returned if you didn't have the LIMIT on the end (accounting for all joins and where clauses).

It's not portable, but it's very efficient (and IMHO the right way of handling this type of problem).