I need Help Creating an Array from DB - Count Visitors based on time visited

[BrazilMac]

Hello again,

 

I have looked into how to do arrays but Im a bit confused on this one. I have a table in the following format:

 

visitor_ID  | visitor_IP    | visitor_date (DATETIME FIELD)

 

1                192.168.1.1    2008-09-02 01:12:19

2                192.168.1.2    2008-09-02 05:26:22

3                192.168.1.3    2008-09-02 06:45:54

4                192.168.1.4    2008-09-02 14:32:03

5                192.168.1.5    2008-09-02 17:02:12

 

 

I am using the Google Chart API to generate a chart and the GphpChart Class ( http://www.malaiac.com/GphpChart/ ) to make things easier. What I cant get done is to be able to generate an array that will count the number of visitors by the hour. I want to feed the class with an array that contains a count of hits by the hour from the visitor_date datetime field, for a 24h period from todays date.

 

Here is the format the array needs to be to be fed into GphpChart based on the table above:

 

 

$data = array('01:00AM' => 1,'02:00AM' => 0,'03:00AM' => 0,'04:00AM' => 0, '05:00AM' => 1, '06:00AM' => 1,'07:00AM' => 0,'08:00AM' => 0,'09:00AM' => 0,'10:00AM' => 0,'11:00AM' => 0,'12:00PM' => 0,'13:00PM' => 0,'14:00PM' => 1,'15:00PM' => 0,'16:00PM' => 0,'17:00PM => 1'02:00AM' => 0, '18:00PM' => 0,'19:00PM' => 0, '20:00PM' => 0,'21:00PM' => 0,'22:00PM' => 0,'23:00PM' => 0, '00:00AM' => 0,);

 

How do I generate such an array????

 

Thanks in advance!!!

 

 

 

 

 

[steveclondon]

personally I would store the date info as follows yyyymmddhhmmss in the database as a 14 num int, then query the database. Substitute dates below with the dates you want.

 

$query="SELECT * FROM database WHERE visitor_date>'yyyymmddhhmmss' && vistor_date<'yyyymmddhhmmss';

$result=mysql_query($query);

$rows=mysql_num_rows($result);

 

$rows will give the number between those times, you can also pull them out then and loop through them.

 

 

[BrazilMac]

But then, how to get to generate an array with the count returned for each hour??