PHP Fetch Data from MySql Table like Twitter Trend

Question

I have a mysql table as follows-

crId    ccId    ccatId  entryDate
458     186     12      2016-01-01
459     186     37      2016-01-01
460     186     117     2016-01-01
461     187     12      2016-01-04
462     187     117     2016-01-05
463     187     117     2016-01-07
464     187     12      2016-01-07

Now, I want to fetch data from this table like twitter trend part.

• Fetch all Data from table within last 1 week time
• Display top 5 entries from table according to descending order
• Count and display how many ccatId entry each. For example, in table, 117 has 3 entries, 12 has 3 entries etc.

How can I make this? I cannot find out the solution? Can anybody please help me?

Expected Outcomes-

12
3 times

117
3 times

12
2 times

Answer 1

Plese check below query :

SELECT ccatId, count( ccatId ) AS count FROM `tablename` WHERE `entryDate` > DATE_SUB( NOW( ) , INTERVAL 1 WEEK )  GROUP BY ccatId ORDER BY count DESC

It return something like this ,

+--------+-------+
| ccatId |  count|
+--------+-------+
|   12   |    2  |
|  117   |    2  |
+--------+-------+

Answer 2

Try this query :

SELECT ccatId, count(ccatId) AS rank FROM `table_name` GROUP By ccatId ORDER BY rank DESC;

It returns something like this,

+--------+-------+
| ccatId |  rank |
+--------+-------+
|   12   |    3  |
|  117   |    3  |
|   37   |    1  |
+--------+-------+

If you want entries from last week only then just put a WHERE clause.