MYSQL：GROUP BY问题使用ORDER BY

Question

here is query with pagination

$page = (int)(!isset($_GET["page"]) ? 1 : $_GET["page"]);

    if ($page <= 0) $page = 1;

    $per_page = 2; // Set how many records do you want to display per page.

    $startpoint = ($page * $per_page) - $per_page;

    $statement = "`message_box` WHERE reciver = '".dec($_SESSION['user_reporter'])."' AND status='0' GROUP BY `sender` ORDER BY `date_added` DESC";

    $result = mysql_query("SELECT count(*),id,sender,message,status,date_added FROM {$statement} LIMIT {$startpoint} , {$per_page}");

and at the end of the table where my data is displayed, i call my pagination function ( define in my custom-function.php ).

So, with the above query my pagination block is not displaying. But When I remove

GROUP BY `sender`

FROM $statement like this,

$statement = "`message_box` WHERE reciver = '".dec($_SESSION['user_reporter'])."' AND status='0' GROUP BY `sender` ORDER BY `date_added` DESC";

pagination displays and works fine but i want that GROUP BY sender clause.

sorry for my bad English....please...help !

Answer 1

This query:

SELECT count(*), id, sender, message, status, date_added
FROM {$statement}
LIMIT {$startpoint}, {$per_page}

is going to return one row, regardless of the LIMIT clause. It is an aggregation query without a GROUP BY clause. Such a query is an aggregation over the whole table and by definition returns one row.

Remove the count(*) if you want all the rows. Add GROUP BY id, sender, message, status, date_added if you want to count the number of duplicates you have for those columns.