How to Create this complicated Array? Help

[bananahead]

Hello PHP Pros!

I need some help with this complicated problem!

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/ ) for easy generation of a chart. 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 today's 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!!!

[coolsti]

Well, if you had two arrays, $dates and $values, let us say, and you want to create an associative array with $dates as the keys and $values as the values, then you could do it like this:

$data = new array();
for ($i=0;$i<count($dates);$i++) 
{
    $data[$dates[$i]] = $values[$i];
}
 

This assumes that your $dates array is unique, and that your $dates and $values arrays are ordered correctly so that their values "line up" so to speak. If you have two $dates that are the same, you will overwrite the first occurance in your associative array with the second occurrance.

In your case, the two arrays $dates and $values in my above example, have to be taken from your overall data, and how you do this is up to you. I am only here showing how to create the associative array in case this is where your question lies.

I cannot guarantee for the syntax above being error free, I am always confusing Javascript with PHP when it comes to creating a new array :)

[bananahead]

How would I query the mySQL? Im not really sure how to put all together so that the code will spit 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,); 

The above is based on the table as it was shown on the example, but of course, the fields will be updated daily. I dont know how to get the count by hour from the datetime field.

Can you help me put it all together?

[bnashenas1984]

Hi there
I think the better option is to use "date(U)" instead of using complete date and time

What it does is that this function returns seconds past since (January 1 1970 00:00:00 GMT). It's a 10 digit number and its counting

What you can do is to put the visitors ID and IP and the DATE(U) that he/she has visited the page.

Here is what you have to do to find out how many people have visited your page in last

MINUTE : SELECT * FROM yourtable WHERE phptime > currentdateU - 60
HOUR : SELECT * FROM yourtable WHERE phptime > currentdateU - 3600
DAY : SELECT * FROM yourtable WHERE phptime > currentdateU - 216000


I'm not sure if this is what you need

But let me know if there is anything else I can help with :)

Good luck

[bananahead]

Thanks for the reply!

to explain it a bit better, this is what I need:

I need to be able to count the number of visits by each hour, so if you look at the sample table:

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:1

I want to be able to get a count of the total number of visitors today at each hour of the day. I need the array to be output 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,);

So that It is usable by the Chart class, since it expects it as such to be able to generate the chart. I just dont know how to get PHP to output my table data as it is above

Sorry if I confused anyone and thanks already!

[bnashenas1984]

Hi again
Now I know what you wanted.

I simulated a database in my computer to figure out how to get the query you're looking for

Here is the PHP code:

    $data=array();
    for ($i=0; $i<=23; $i++) {
        $j=$i+1;
        if ($i<12) {$ampm ="AM";} else {$ampm ="PM";}
        $query= "SELECT visitor_ID FROM tablename WHERE datetime >= '2008-09-09 $i:00:00' AND datetime < '2008-09-09 $j:00:00'";
        mysql_query($query);
        $data["$i:00".$ampm] = mysql_affected_rows();
    }
 

Once this code is finished you will have the array ($data) as you wanted

if you want to test the code use this one to see the result

 
    for ($j=0; $j<=23; $j++) {
        if ($j<12) {$ampm ="AM";} else {$ampm ="PM";}
        print "$j:00".$ampm." => ".$data["$j:00".$ampm]."<br>";
    }
 

Here is the result I got after testing the code

0:00AM => 3
1:00AM => 2
2:00AM => 0
3:00AM => 1
4:00AM => 2
5:00AM => 1
6:00AM => 0
7:00AM => 3
8:00AM => 5
9:00AM => 1
10:00AM => 0
11:00AM => 0
12:00PM => 4
13:00PM => 2
14:00PM => 1
15:00PM => 3
16:00PM => 0
17:00PM => 1
18:00PM => 0
19:00PM => 2
20:00PM => 2
21:00PM => 3
22:00PM => 0
23:00PM => 1


Hope it's what you'v been looking for

[bnashenas1984]

By the way
Don't forget to add database connection codes before these scripts.
Otherwise they wont work :)

[phpNerd01]

Hi bananahead, here is a simple query that could do the trick for you.

 
SELECT DATE(date_to_evaluate), 
           date_format(date_to_evaluate, "%h:00 %p")  as 'hour', 
           count(*)
FROM `store_shoppertrack` 
GROUP by DATE(date_to_evaluate), hour(date_to_evaluate);
 
 

DATE_FORMAT parameters:

// %M = MONTH full text
// %e = Numeric DAY in the month, no leading 0
// %Y = 4 digit YEAR
// %h = 12 HOUR clock with leading 0
// %i = MINUTES, numeric with leading 0
// %p = AM/PM
As shown below, you will have 3 fields in output, the date, the hour and the count.

DATE(date_to_evaluate hour count(*)
2008-08-01 06:00 PM 2
2008-08-01 07:00 PM 1
2008-08-04 08:00 PM 4
2008-08-07 09:00 AM 2
2008-08-14 04:00 PM 6
2008-08-15 02:00 PM 4
2008-08-17 10:00 PM 1
2008-09-04 08:00 PM 4
2008-09-05 12:00 AM 1
2008-09-07 06:00 PM 2

If you want a specific day, you could add a where statement for a specific date and remove the date from the field output list

which could give you a resultset like this:

06:00 PM 2
07:00 PM 1

two hits for 6pm and 1 hit for 7 pm on 2008-08-01.

At this moment you can load the results from the query to your array.



// %M = MONTH full text
// %e = Numeric DAY in the month, no leading 0
// %Y = 4 digit YEAR
// %h = 12 HOUR clock with leading 0
// %i = MINUTES, numeric with leading 0
// %p = AM/PM


Hope it helps ;),

phpNerd01

[phpNerd01]

Here is the code you need ... I tested it woks

enjoy !!

<?php
 
$ampm_hour=0;
$data=array();
 
// intial load of the hours of the day , count will be set to 0
 
for ($i=0; $i<=23; $i++) {
   if ($i<12) {
      $ampm ="AM";
      $ampm_hour = $i;
    }
    else     {
       $ampm ="PM";
       $ampm_hour = $i - 12;
    }
 
     $ampm_hour = sprintf("%02d", $ampm_hour);
     $data[$ampm_hour.":00 ".$ampm] = 0;
 }
 
 
//connect to server and select database
$mysqli = mysqli_connect("localhost", "youruser", "yourpass", "testDB"); // REPLACE WITH YOUR VALUES
 
 //retrieve the count use your field and table name
$sql = 'SELECT date_format(date_added, "%h:00 %p") as \'hour\', '
        . ' count(*) as \'mycount\''
        . ' FROM `store_shoppertrack` '
        . ' where date(date_added) = 20080801'
        . ' GROUP by DATE(date_added), hour(date_added);';
 
$result = mysqli_query($mysqli, $sql) or die(mysqli_error($mysqli));
 
// update the count for the hour found in the querey
if (mysqli_num_rows($result) >= 1) {
    while ($info = mysqli_fetch_array($result)) {
       $data[$info['hour']] =  $info['mycount'];
    }
}
 
// print the array
 
 print "<p>";
 foreach ($data as $hour => $count ) {
    print "$hour => $count<br />";
 
 }
 print "</p>";
 //free results
 mysqli_free_result($result);
 
 //close connection to MySQL
 
mysqli_close($mysqli);
?>
 

the output looks like this:

00:00 AM => 0
01:00 AM => 0
02:00 AM => 0
03:00 AM => 0
04:00 AM => 0
05:00 AM => 0
06:00 AM => 0
07:00 AM => 0
08:00 AM => 0
09:00 AM => 0
10:00 AM => 0
11:00 AM => 0
00:00 PM => 0
01:00 PM => 0
02:00 PM => 0
03:00 PM => 0
04:00 PM => 0
05:00 PM => 0
06:00 PM => 2
07:00 PM => 1
08:00 PM => 0
09:00 PM => 0
10:00 PM => 0
11:00 PM => 0

as mentioned in previous reply, i have 2 hits at 6pm and 1 at 7 pm for date 2008-08-01


phpNerd01

[bananahead]

My God guys, you saved me from a really hard headache!!! Im gonna try this now and report back!!!

THANKS SO MUCH!!! Love ya guys :-)

[bnashenas1984]

I'm glad we could help.
I'm sure there are people out there who can give you easier scripts to do the same thing as you can see my script works fine but it could be more inteligent by using more complicated queries like the one phpNerd01 showed us.

let us know if there is anything else we can help you with
Good luck

[phpNerd01]

I'm glad we could help.
I'm sure there are people out there who can give you easier scripts to do the same thing as you can see my script works fine but it could be more inteligent by using more complicated queries like the one phpNerd01 showed us.

let us know if there is anything else we can help you with
Good luck

Hi bnashenas1984,

I proposed a new solution that addresses 3 issues I found in your script:

NOTATION:

Issue 1. you based your solution on the premise that 13:00PM to 23:00PM is fine, which is not; 01:00PM to 11:00 PM is the proper notation.

PERFORMANCE:

Issue 2. Running 24 individual queries to retrieve the results for one day. Unnecessary overhead and could be significant depending on the size of the table. my solution 1 query..

Issue 3. Listing the values and using the record count from the MySQL variable which tells you the number of records generated (As opposed to computing your own count from the query). Unnecessary overhead and could be significant depending on the size of the table. My solution, 1 query with a maximum of 24 records in the result set.

Kind regards,
phpNerd01

[bnashenas1984]

Hi again
Thanks phpNerd01 for correcting my mistakes.
I might be a php programmer but i'v never been good at MYSQL queries. I need to work harder :P

Thanks again

[phpNerd01]

Hi again
Thanks phpNerd01 for correcting my mistakes.
I might be a php programmer but i'v never been good at MYSQL queries. I need to work harder :P

Thanks again

We found the solution and we learn in the process, that's the objectve. ... BTW I had a bug see correction below (one line) .

 
for ($i=0; $i<=23; $i++) {
   if ($i<12) {
      $ampm ="AM";
      $ampm_hour = $i;
    }
    else     {
       $ampm ="PM";
//       $ampm_hour = $i - 12; // my mystake IT GIVES 00:00PM FOR 12:00 pm
        $ampm_hour = ( $i == 12) ? 12 : $i - 12; // This should work
    }
 
     $ampm_hour = sprintf("%02d", $ampm_hour);
     $data[$ampm_hour.":00 ".$ampm] = 0;       
 }
 

before:
11:00 AM 0
00:00 PM 0

now
11:00 AM 0
12:00 PM 0

Regards, ;)

phpNerd01

[bananahead]

You guys are awesome! It is working as it should!

Again, thanks so much, love you guys!

I need some help with a new issue though :-) I will post soon!

[bnashenas1984]

Hi bananahead
I'm glad to hear your problem is solved. Forums are to help people . If it wasn't because of forums I couldn't accomplish that much.

Good luck