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

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

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;

You are grouping by email, so the second COUNT(*) as num does not give you the total number of entries, but rather each row's num field contains the total number of users with the corresponding email.

SELECT count(*) AS total
FROM
    (SELECT COUNT(DISTINCT wp_users.ID)
    FROM wp_vgr_order
    INNER JOIN wp_users ON wp_vgr_order.author_id = wp_users.ID
    GROUP BY wp_users.ID) AS ROW_COUNT.

You can change your query in start (and end )

Just use 1 query to acquire both your limited data for pagination and the total number of records that would be found without the limit: https://dev.mysql.com/doc/refman/5.7/en/information-functions.html#function_found-rows

The first query will give the data for the current page.

Then then the second query will simply be: SELECT FOUND_ROWS(); and that gives the unlimited total found by the first query.