Count MySQL records with LIMIT

Question

Since I'm trying to count the number of records in a table, even if the SQL statement has a LIMIT in it, in general it works, however, something strange 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 displayed through a while it displays this:

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

If I delete COUNT(*) AS count , everything will display fine ... how is this done?

Answer 1

Do not use COUNT(*) to count the number of rows (for many reasons). Write your complete query and add SQL_CALC_FOUND_ROWS immediately after SELECT :

 SELECT SQL_CALC_FOUND_ROWS id, title FROM foo LIMIT 5; 

Then, after executing this request (immediately after), run:

 SELECT FOUND_ROWS(); 

This will return the number of rows that the original SELECT would return if you hadn't had a LIMIT at the end (given all joins and where the sentences are).

It is not portable, but very effective (and IMHO is the right way to solve this problem).

Answer 2

LIMIT does nothing here because you select one scalar. The error is shown because you do not select the message header, so it is not in the $row hash.

Answer 3

This is because COUNT() is an aggregated function. You will need to make two separate queries to get both the number of rows in the table and individual records.

Answer 4

What everyone is trying to say is that you should select two separate queries instead of one.

1) get the number of rows in the table

 $sql = "SELECT COUNT(*) AS count FROM posts; 

2) get the last 5 rows of the table

 $sql = "SELECT * FROM posts ORDER BY post_date DESC LIMIT 5"; 

Answer 5

You request a SELECT only COUNT(*) AS count . If you want to return more columns, you must specify them in the SELECT .

Answer 6

By enabling COUNT(*) AS count , you have removed the actual columns from which you want information. Try using

 SELECT *, COUNT(*) AS count FROM posts ORDER BY post_date DESC LIMIT 5 

This query will do both things that you are trying to do in one query, but in fact they should be split into 2 separate queries . What is the general purpose of using COUNT in a query? It has to be done differently.

Answer 7

You mix the aggregate function COUNT() with the standard selection. You cannot get the exact title without a GROUP BY clause in a consolidated query. The simplest thing you need to do is make two requests: one for your account, the other for your message information. In addition, it makes no sense to use LIMIT for an aggregate function without any other columns in your (omitted) GROUP BY - the current query will always return a single row.

Answer 8

I had the same problem and found this article: http://www.mysqldiary.com/limited-select-count/ the best way (especially for large tables) uses it as follows:

 SELECT COUNT(*) AS count FROM (SELECT 1 FROM posts ORDER BY post_date DESC LIMIT 5) t 

now it returns a number from 0 to 5