Alex Rivera | Logout

How to count and limit record in a single query in MYSQL?

Asked 2009-04-29T13:31:33.533
19

I am searching for records in a table as follows:

SELECT Id, Name FROM my_table WHERE Name LIKE '%prashant%' LIMIT 0, 10;

Now, I am adding LIMIT to maintain my paging. But when user searches for word 'prashant' then total records I have is 124 for 'prashant'. But as the limit applied to the query so it only fetches 10 records in my PHP script and when I am count the mysql variable in PHP code it returns total records found is 10.

So basically I want to count and Limit using a single query, by making some modification in the above query, I want the total count (124) of records. I don't want to run a separate count(*) query for just counting the total result found by the query.

Thanks.

Edit
Report

1 Answer

20
SELECT SQL_CALC_FOUND_ROWS
  Id, Name
FROM my_table
WHERE
  Name LIKE '%prashant%'
LIMIT 0, 10;

# Find total rows
SELECT FOUND_ROWS();

more info

answered 2009-04-29T13:35:37.720

Your Answer