php - Two seemingly identical queries returning different results when using count (pagination) -

i running 2 queries database pagination reasons. such, each query identical. count(*) query not returning number of results non-count query is. i'm baffled why case. queries below.

select p.host_id, p.rating_support, p.rating_tech, max(p.rating_overall) rating_overall, p.publish_rating, h.name, prices.price, prices.term_duration plans p inner join hosts h on h.id = p.host_id inner join (select plan_id, price, term_duration prices price > 0 , price < 50 , term_duration = 1) prices on prices.plan_id = p.id p.published = 1 , h.published = 1 group p.host_id order rating_overall desc limit 0, 12  select count(*) count plans p inner join hosts h on h.id = p.host_id inner join (select plan_id, price, term_duration prices price > 0 , price < 50 , term_duration = 1) prices on prices.plan_id = p.id p.published = 1 , h.published = 1 group p.host_id 

i'm not expert @ mysql. besides count not providing correct number of results, non-count query works perfectly.

any light on problem great.

with of dems' comment (hunt down , upvote him somewhere :), created query. notice removed subquery, because seemed unnecessary:

select   count( distinct p.host_id )       plans p inner join hosts h on h.id = p.host_id inner join prices  on prices.plan_id = p.id                   , prices.price > 0                   , prices.price < 50                   , prices.term_duration = 1 p.published = 1   , h.published = 1 

---

my original answer:

to number of total row, have wrap group by query outer select:

select count(*) (   select null -- counting, need no actual data -> bit faster         plans p   inner join hosts h on h.id = p.host_id   inner join prices  on prices.plan_id = p.id                     , prices.price > 0                     , prices.price < 50                     , prices.term_duration = 1   p.published = 1     , h.published = 1   group p.host_id ) all_rows_without_data 

or use sql_calc_found_rows + found_rows()

http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_found-rows

a select statement may include limit clause restrict number of rows server returns client. in cases, desirable know how many rows statement have returned without limit, without running statement again. obtain row count, include sql_calc_found_rows option in select statement, , invoke found_rows() afterward:

first, select required rows, add sql_calc_found_rows:

select sql_calc_found_rows   p.host_id, p.rating_support, p.rating_tech,   max(p.rating_overall) rating_overall,   p.publish_rating, h.name, prices.price, prices.term_duration       plans p inner join hosts h on h.id = p.host_id inner join prices  on prices.plan_id = p.id                   , prices.price > 0                   , prices.price < 50                   , prices.term_duration = 1 p.published = 1 , h.published = 1 group p.host_id order rating_overall desc limit 0, 12; 

second, number of rows have been returned if there weren't limit statement in first query:

select found_rows(); 

update: sql_calc_found_rows + found_rows() doesn't seem reliable, returs 0 unknown reason (not me: found_rows() keeps returning 0 ):

http://sqlfiddle.com/#!2/7304d/8