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

[isoripper]

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 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?Huh

Thanks in advance!

[edited by: dreamcatcher at 7:14 am (utc) on Sep. 8, 2008]
[edit reason] No urls please! [/edit]

[cameraman]

I think the easiest way is to make an array of the 24 hours, then count up the visitors by their hour:
$target_date = date('Y-m-d'); // This gives a target date of today
$hours = array_fill(0,24,0); // Generate an empty array

$qry = mysql_query("SELECT visitor_date FROM yourtablename WHERE visitor_date='$target_date'");
while($vdate = mysql_fetch_row($qry)) {
$vhour = date('G',$vdate[0]); // Get the hour of the visit
$hours[$vhour]++; // Increment this hour's visit count
}// EndWhile getting today's visitors

// Prettify the AM times
for($i = 0; $i < 12; $i++) {
$index = sprintf("%02d:00AM",$i); // This gives you the 01:00AM etc keys
$output[$index] = $hours[$i]; // set the value
}

$output['12:00PM'] = $hours[12]; // This one stands alone

// Prettify the PM times
for($i = 13; $i < 24; $i++) {
$index = sprintf("%02d:00PM",$i-12);
$output[$index] = $hours[$i];
}

[isoripper]

Wow, Im gonna try this one out now and report back! THANKS

[isoripper]

Hi!

I tried the code above but the query was returning empty records. The datetime field data is being stored as:

2008-09-07 14:32:19

So I changed the variable $target_date to:

$range1 = $target_date . " 00:00:00";
$range2 = $target_date . " 23:59:59"

and then the query is now:

$qry = mysql_query("SELECT * FROM visitor_log WHERE visitor_date between '$range1' and '$range2'");

It is now returning records. But using your code above and running the php code is jsut displaying a blank page. How can I get the array to print as:

$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,);

with your code above? Im a bit lost here!

Thanks again!

[isoripper]

Edit::

I have just tried running the code with a

print_r($hours); (Im not sure if Im doing this right)

and this is what the output is:

Array ( [0] => 0 [1] => 0 [2] => 0 [3] => 0 [4] => 0 [5] => 0 [6] => 0 [7] => 0 [8] => 0 [9] => 0 [10] => 0 [11] => 0 [12] => 0 [13] => 0 [14] => 0 [15] => 0 [16] => 0 [17] => 0 [18] => 6 [19] => 0 [20] => 0 [21] => 0 [22] => 0 [23] => 0 )

It is getting a count of 6 for [18], but the table contains 6 records with these timestamps:

2008-09-07 14:32:19
2008-09-07 13:58:42
2008-09-07 13:57:47
2008-09-07 13:56:49
2008-09-07 13:56:02
2008-09-07 13:55:25

So there are 5 hits at 13:00PM and one at 14:00PM.....

what am I missing?

[cameraman]

Oh good grief, I knew this before I started to type but then managed to forget it. Change this line:
$vhour = date('G',strtotime($vdate[0])); // Get the hour of the visit

[isoripper]

I have changed the line as suggested and print_r($hours); Is now printing as:

Array ( [0] => 0 [1] => 0 [2] => 0 [3] => 0 [4] => 0 [5] => 0 [6] => 0 [7] => 0 [8] => 0 [9] => 0 [10] => 0 [11] => 0 [12] => 0 [13] => 0 [14] => 0 [15] => 0 [16] => 0 [17] => 0 [18] => 0 [19] => 0 [20] => 0 [21] => 0 [22] => 0 [23] => 0 )

Every hour has now 0 hits! the table currently stores:

2008-09-07 14:32:19
2008-09-07 13:58:42
2008-09-07 13:57:47
2008-09-07 13:56:49
2008-09-07 13:56:02
2008-09-07 13:55:25

Could this be because of my query?

$target_date = date('Y-m-d'); //

$range1 = $target_date . " 00:00:00";
$range2 = $target_date . " 23:59:59";

$qry = mysql_query("SELECT * FROM visitor_log WHERE visitor_date between '$range1' and '$range2'");

Thanks!

[cameraman]

Possibly. On your last run the date hadn't become Sep 8 had it?
$target_date = date('Y-m-d'); uses "today".

According to the mysql manual, "For best results when using BETWEEN with date or time values, you should use CAST() to explicitly convert the values to the desired data type". I think if you cast both the field and the target date to DATE you won't have to use between, though.

Try:
$qry = mysql_query("SELECT * FROM visitor_log WHERE CAST(visitor_date AS DATE) = CAST('$target_date' AS DATE)");

[isoripper]

Thanks for your help! I got it all sorted, The query helped quite a lot!