CODEWITHSUNDEEP
mysqlsqlcount
GeeH
GeeH

Reputation: 563

Returning count of rows with rows

I want to return the total number of rows returned when a query is limited so that I can return the top 10 results, with a "View all 63" link in my PHP. I guess I'm looking for something like:

SELECT `n`.*, COUNT('id') AS `total` 
  FROM `table` 
 WHERE (name like '%search%') 
 LIMIT 10;

But that only returns the first row.

Upvotes: 0

Views: 157

Answers (2)

Pranay Rana
Pranay Rana

Reputation: 176896

    SELECT n.*, 
   COUNT(select id from AStotalFROMtable(name like '%search%') )  as Total
   AStotalFROMtable(name like '%search%') LIMIT 10;

or

Read : MySQL: Get total number of rows when using LIMIT

also try out

FOUND_ROWS() - For a SELECT with a LIMIT clause, the number of rows that would be returned were there no LIMIT clause 

SELECT SQL_CALC_FOUND_ROWS *    FROM `table`    WHERE  name like '%     
          search%'    LIMIT 10;    

SELECT FOUND_ROWS();

The first query will return 10 rows, and the second query will return you the number of rows which would be returned be there no LIMIT clause in the first query.

Upvotes: 0

ejb_guy
ejb_guy

Reputation: 1125

SELECT n.*, total 
FROM table n, (select count(*) total  from table t2 where name like '%search%') 
where name like '%search%'
LIMIT 10

Upvotes: 2

Related Questions