Paginate问题与group by的查询中的count

Question

Ok I usually use a pagination code that counts the total pages, like this

$query = "SELECT COUNT(*) as num FROM $tableName WHERE `ganador2` = '1'";
    $total_pages = mysql_fetch_array(mysql_query($query));
    $total_pages = $total_pages[num];

then i call the data with this query for echo page contents.

// Get page data
    $query1 = "SELECT id,name,lastname,email,codigo, media, phone, Pcode, birth FROM $tableName  WHERE `ganador2` = '1' LIMIT $start, $limit ";

this last part is the Working code, ok,

Know im trying to use it with this querys, this count how many times the user has entered his email.

$query = "SELECT COUNT(*) AS num,id,name,lastname,email,codigo, media, phone, Pcode, birth FROM usuarios GROUP BY email";
    $total_pages = mysql_fetch_array(mysql_query($query));
    $total_pages = $total_pages[num];

and

// Get page data
    $query1 = "SELECT COUNT(*) AS top,id,name,lastname,email,codigo, media, phone, Pcode, birth FROM usuarios GROUP BY email ORDER BY top DESC LIMIT $start, $limit";

The problem is for pagination, its telling me the query just found (2 users) but thats not true, so my query its wrong.. How can i calculate the total pages in the first query, as the second query is working just fine.

thanks

Answer 1

with the group by you are getting a count per email. you are getting the number of records that have the first email... I think this is what you want.

SELECT COUNT(DISTINCT email) AS num FROM usuarios;