how to fetch a mysql table and order it by date

Question

hey guys in order to compelete my statistic script i need to show everyday visits

so if i want to show today and yesterday visits i use this code :

    $timestamp = time();
    $today = date("Y-m-d") ;
    $yesterday = date("Y-m-d", strtotime("-1 day"));
    $yesterday_1 = date("Y-m-d", strtotime("-2 day"));


    list($TotalVisitsToday) = $db->sql_fetchrow($db->sql_query("SELECT COUNT(DISTINCT ip_address) AS TotalVisitsToday FROM table_tracking  WHERE date_time BETWEEN '$yesterday' AND '$today'"));

    list($TotalVisitsYesterday) = $db->sql_fetchrow($db->sql_query("SELECT COUNT(DISTINCT ip_address) AS TotalVisitsYesterday FROM table_tracking  WHERE date_time BETWEEN '$yesterday_1' AND '$yesterday'"));

ok now i have today's vists and yesterday

but i want to list all days saved and counts of ips

i know i should use a while and for sentence but not sure how to accomplish that !

database hint :

ip_addresses are being saved with a date_time value and now i have 10,000 ips visit my page in days

and i want to show the counts of ips for each day that saved in my mysql table

Answer 1

Grouping by date part of the date_time column should solve your issue. Something like this should do:

select date(date_time), count(distinct ip_addresses)
from table_tracking
group by date(date_time)

Answer 2

What about

SELECT DATE(date_time) as date, COUNT(DISTINCT ip_address) AS TotalVisitsToday FROM table_tracking GROUP BY date 

I don't have a DB here right now to test but from Mysql manual:

DATE(expr)

Extracts the date part of the date or datetime expression expr.

mysql> SELECT DATE('2003-12-31 01:02:03');
        -> '2003-12-31'

Hope this helps

Giovanni