Sql function count and sum with Php-CodePudding

I need to get data from my table. I want only the data of last 24 h , counting the occurrence, with max 60 last infos.

TABLE super_table

id username date_post
---------------------
11  james   111105487
10  luke    110105474
9   james   110105400
8   john    111105486
7   james   111100487
6   luke    110105174
5   john    110205474

I want something like this

james(3)
luke(2)
john(2)

Code:

<?php
$delay_search=strtotime(" 1 day");
$max_user_get=60;
$sql_total = "
SELECT username,COUNT(*) as count 
FROM super_table where date_post <'$delay_search' 
GROUP BY username 
ORDER BY id DESC LIMIT 0,$max_user_get;
";
$temp='';

$result_total = $conn->query($sql_total);
$nb_total=$result_total->num_rows;
while($row = $result_total->fetch_assoc()) 
{
    $username=$row["username"];
    $total_post=$row['count'];  
  
  /*Edit*/
   $temp.='User :'.$username.'('.$total_post.')';

}   
 echo $temp;
?>

CodePudding user response：

I want only the data of last 24 h

This can be done by from_unixtime to convert the int to date

FROM_UNIXTIME(date_post) > NOW() - INTERVAL 1 DAY

counting the occurrence, with max 60 last infos

limit 60

Final query:

select username,
       count(*)
from super_table
where FROM_UNIXTIME(date_post) > NOW() - INTERVAL 1 DAY
group by username
order by count(*) desc
limit 60;

No need for

$delay_search=strtotime(" 1 day");
$max_user_get=60;