formating a fetchall by date from datetime with mysql / php ( making a simple stat counter )_问答_开发者

hello im a bit confused here. lets say we are making a simple page stat. heres the data that we get.

lets say we dont have any guest uid, host uid host pid for now. below is just a example.

  0 => 
    array
      'guest_UID' => string '0' (length=1)
      'host_UID' => string '24' (length=2)
      'host_PID' => null
      'IP' => string '127.0.0.1' (length=9)
      'referrer' => string '' (length=0)
      'timecreate' => string '2011-05-13 13:53:20' (length=19)
  1 => 
    array
      'guest_UID' => string '0' (length=1)
      'host_UID' => string '24' (length=2)
      'host_PID' => null
      'IP' => string '127.0.0.1' (length=9)
      'referrer' => string '' (length=0)
      'timecreate' => string '2011-05-13 13:54:29' (length=19)
  2 => 
    array
      'guest_UID' => string '0' (length=1)
      'host_UID' => string '24' (length=2)
      'host_PID' => null
      'IP' => string '127.0.0.1' (length=9)
      'referrer' => string '' (length=0)
      'timecreate' => string '2011-05-13 13:57:39' (length=19)
  3 => 
    array
      'guest_UID' => string '0' (length=1)
      'host_UID' => string '24' (length=2)
      'host_PID' => null
      'IP' => string '127.0.0.1' (length=9)
      'referrer' => string '' (length=0)
      'timecreate' => string '2011-05-13 13:59:10' (length=19)
  4 => 
    array
      'guest_UID' => string '0' (length=1)
      'host_UID' => string '24' (length=2)
      'host_PID' => null
      'IP' => string '127.0.0.1' (length=9)
      'referrer' => string '' (length=0)
      'timecreate' => string '2011-05-13 13:59:49' (length=19)
  5 => 
    array
      'guest_UID' => string '0' (length=1)
      'host_UID' => string '24' (length=2)
      'host_PID' => null
      'IP' => string '127.0.0.1' (length=9)
      'referrer' => string '' (length=0)
      'timecreate' => string '2011-05-13 14:00:02' (length=19)
  6 => 
    array
      'guest_UID' => string '0开发者_运维知识库' (length=1)
      'host_UID' => string '24' (length=2)
      'host_PID' => null
      'IP' => string '127.0.0.1' (length=9)
      'referrer' => string '' (length=0)
      'timecreate' => string '2011-05-13 14:00:09' (length=19)
  7 => 
    array
      'guest_UID' => string '0' (length=1)
      'host_UID' => string '24' (length=2)
      'host_PID' => null
      'IP' => string '127.0.0.1' (length=9)
      'referrer' => string '' (length=0)
      'timecreate' => string '2011-05-13 14:01:36' (length=19)
  8 => 
    array
      'guest_UID' => string '0' (length=1)
      'host_UID' => string '24' (length=2)
      'host_PID' => null
      'IP' => string '127.0.0.1' (length=9)
      'referrer' => string '' (length=0)
      'timecreate' => string '2011-05-13 14:01:49' (length=19)
  9 => 
    array
      'guest_UID' => string '0' (length=1)
      'host_UID' => string '24' (length=2)
      'host_PID' => null
      'IP' => string '127.0.0.1' (length=9)
      'referrer' => string '' (length=0)
      'timecreate' => string '2011-05-13 14:03:25' (length=19)
  10 => 
    array
      'guest_UID' => string '0' (length=1)
      'host_UID' => string '24' (length=2)
      'host_PID' => null
      'IP' => string '127.0.0.1' (length=9)
      'referrer' => string '' (length=0)
      'timecreate' => string '2011-05-13 14:05:54' (length=19)
  11 => 
    array
      'guest_UID' => string '0' (length=1)
      'host_UID' => string '24' (length=2)
      'host_PID' => null
      'IP' => string '127.0.0.1' (length=9)
      'referrer' => string '' (length=0)
      'timecreate' => string '2011-05-13 14:06:06' (length=19)
  12 => 
    array
      'guest_UID' => string '0' (length=1)
      'host_UID' => string '24' (length=2)
      'host_PID' => null
      'IP' => string '127.0.0.1' (length=9)
      'referrer' => string '' (length=0)
      'timecreate' => string '2011-05-13 14:06:45' (length=19)
  13 => 
    array
      'guest_UID' => string '0' (length=1)
      'host_UID' => string '24' (length=2)
      'host_PID' => null
      'IP' => string '127.0.0.1' (length=9)
      'referrer' => string '' (length=0)
      'timecreate' => string '2011-05-13 14:07:00' (length=19)
  14 => 
    array
      'guest_UID' => string '0' (length=1)
      'host_UID' => string '24' (length=2)
      'host_PID' => null
      'IP' => string '127.0.0.1' (length=9)
      'referrer' => string '' (length=0)
      'timecreate' => string '2011-05-13 14:07:52' (length=19)
  15 => 
    array
      'guest_UID' => string '0' (length=1)
      'host_UID' => string '24' (length=2)
      'host_PID' => null
      'IP' => string '127.0.0.1' (length=9)
      'referrer' => string '' (length=0)
      'timecreate' => string '2011-05-13 14:09:34' (length=19)
  16 => 
    array
      'guest_UID' => string '0' (length=1)
      'host_UID' => string '24' (length=2)
      'host_PID' => null
      'IP' => string '127.0.0.1' (length=9)
      'referrer' => string '' (length=0)
      'timecreate' => string '2011-05-13 14:09:57' (length=19)
  17 => 
    array
      'guest_UID' => string '0' (length=1)
      'host_UID' => string '24' (length=2)
      'host_PID' => null
      'IP' => string '127.0.0.1' (length=9)
      'referrer' => string '' (length=0)
      'timecreate' => string '2011-05-13 14:10:55' (length=19)
  18 => 
    array
      'guest_UID' => string '0' (length=1)
      'host_UID' => string '24' (length=2)
      'host_PID' => null
      'IP' => string '127.0.0.1' (length=9)
      'referrer' => string '' (length=0)
      'timecreate' => string '2011-05-13 14:11:02' (length=19)
  19 => 
    array
      'guest_UID' => string '0' (length=1)
      'host_UID' => string '24' (length=2)
      'host_PID' => string '47' (length=2)
      'IP' => string '127.0.0.1' (length=9)
      'referrer' => string 'http://localhost/netcoid' (length=24)
      'timecreate' => string '2011-05-13 15:32:05' (length=19)
  20 => 
    array
      'guest_UID' => string '0' (length=1)
      'host_UID' => string '24' (length=2)
      'host_PID' => null
      'IP' => string '127.0.0.1' (length=9)
      'referrer' => string 'http://localhost/product?id=47' (length=30)
      'timecreate' => string '2011-05-14 15:32:47' (length=19)
  21 => 
    array
      'guest_UID' => string '0' (length=1)
      'host_UID' => string '24' (length=2)
      'host_PID' => string '22' (length=2)
      'IP' => string '127.0.0.1' (length=9)
      'referrer' => string 'http://localhost/netcoid' (length=24)
      'timecreate' => string '2011-05-14 15:32:50' (length=19)

how can we turn it into in the mysql or with php?

  0 => 
    array
      'totalview' => 19 ( from 0 - 19 array at date 2011-05-13 )
      'timecreate' => string '2011-05-13' (length=19)
      'UNIQview' => 1 ( using mysql distinct(IP) or something )
   1 => 
    array
      'totalview' => 2 ( from 20 - 21 array at date 2011-05-14 )
      'timecreate' => string '2011-05-14' (length=19)
      'UNIQview' => 1 ( using mysql distinct(IP) or something )

any suggestion for performance ( lets say we have 1000 of data. is it ok ? )

last question, how do you just fetch one week data?

*edit ive manage to get something like

    function getPageViews($date_start = '0000-00-00' ,$date_end = '9999-99-99'){
    $data = $this->fetchAll ( "SELECT COUNT(AID) AS views, LEFT(timecreate, 10) AS date FROM analytics WHERE 
    LEFT(timecreate, 10) BETWEEN $date_start and $date_end GROUP BY(LEFT(timecreate, 10))" );
    return $data;
}

i still have some problem on selecting a distinct IP for uniqpage view..

Thankyou very much for looking in.

Adam Ramadhan

SELECT DATE_FORMAT(timecreate,"%Y-%m-%d") as roundDate, 
              COUNT(1) as totalViews, COUNT(DISTINCT IP) as uniqueIPs
         FROM analytics
         GROUP BY roundDate;

See MySQL DATE_FORMAT for more information about formatting date values, there's really no need in using string manipulations in such cases.