Separate Query for Count

Question

Hello, I am trying to get my query to grab multiple rows while returning the maximum count of that query.

My query:

SELECT *, COUNT(*) as Max FROM tableA LIMIT 0 , 30

However, it is only outputting 1 record.

I would like to return multiple record as it was the following query:

SELECT * FROM tableA LIMIT 0 , 30

Do I have to use separate queries?

Answer 1

Use separate queries.

It's two separate pieces of information with different structures. One is a row set, the other is a single value. Trying to return both these pieces of information in one query, while possible, is not a good idea.

Answer 2

Well, you can use a single query with the SQL_CALC_FOUND_ROWS function in it, with the use of LIMIT as well. So, while you run the first query through mysql_query(), you can then also run another query as: mysql_query("SELECT FOUND_ROWS()");

which will return you the number of total rows found through that query (no matter you use the LIMIT or not, SELECT FOUND_ROWS() will give you the result count without the LIMIT mentioned in your query).

Following is a sample query:

SELECT SQL_CALC_FOUND_ROWS * FROM tbl_abc

Thanks