Connecting Tech Pros Worldwide Forums | Help | Site Map
bytes > topic > php > answers

How to Create this complicated Array? Help

Newbie
  
Join Date: Aug 2008
Posts: 9
#1: Sep 7 '08
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:
Expand|Select|Wrap|Line Numbers
  1. visitor_ID   | visitor_IP    | visitor_date (DATETIME FIELD)
  2.  
  3. 1                 192.168.1.1    2008-09-02 01:12:19
  4. 2                 192.168.1.2    2008-09-02 05:26:22
  5. 3                 192.168.1.3    2008-09-02 06:45:54
  6. 4                 192.168.1.4    2008-09-02 14:32:03
  7. 5                 192.168.1.5    2008-09-02 17:02:12
  8.  
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:

Expand|Select|Wrap|Line Numbers
  1. $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!!!

Needs Regular Fix
  
Join Date: Mar 2008
Posts: 311
#2: Sep 7 '08

re: How to Create this complicated Array? Help

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:

Expand|Select|Wrap|Line Numbers
  1. $data = new array();
  2. for ($i=0;$i<count($dates);$i++) 
  3. {
  4.     $data[$dates[$i]] = $values[$i];
  5. }
  6.  
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 :)
Newbie
  
Join Date: Aug 2008
Posts: 9
#3: Sep 7 '08

re: How to Create this complicated Array? Help

How would I query the mySQL? Im not really sure how to put all together so that the code will spit as:
Expand|Select|Wrap|Line Numbers
  1. $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?
Familiar Sight
  
Join Date: Sep 2007
Posts: 211
#4: Sep 7 '08

re: How to Create this complicated Array? Help

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
Newbie
  
Join Date: Aug 2008
Posts: 9
#5: Sep 7 '08

re: How to Create this complicated Array? Help

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!
Familiar Sight
  
Join Date: Sep 2007
Posts: 211
#6: Sep 8 '08

re: How to Create this complicated Array? Help

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:

Expand|Select|Wrap|Line Numbers
  1.     $data=array();
  2.     for ($i=0; $i<=23; $i++) {
  3.         $j=$i+1;
  4.         if ($i<12) {$ampm ="AM";} else {$ampm ="PM";}
  5.         $query= "SELECT visitor_ID FROM tablename WHERE datetime >= '2008-09-09 $i:00:00' AND datetime < '2008-09-09 $j:00:00'";
  6.         mysql_query($query);
  7.         $data["$i:00".$ampm] = mysql_affected_rows();
  8.     }
  9.  
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

Expand|Select|Wrap|Line Numbers
  1.  
  2.     for ($j=0; $j<=23; $j++) {
  3.         if ($j<12) {$ampm ="AM";} else {$ampm ="PM";}
  4.         print "$j:00".$ampm." => ".$data["$j:00".$ampm]."<br>";
  5.     }
  6.  
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
Familiar Sight
  
Join Date: Sep 2007
Posts: 211
#7: Sep 8 '08

re: How to Create this complicated Array? Help

By the way
Don't forget to add database connection codes before these scripts.
Otherwise they wont work :)
Newbie
  
Join Date: Sep 2008
Posts: 8
#8: Sep 8 '08

re: How to Create this complicated Array? Help

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

Expand|Select|Wrap|Line Numbers
  1.  
  2. SELECT DATE(date_to_evaluate), 
  3.            date_format(date_to_evaluate, "%h:00 %p")  as 'hour', 
  4.            count(*)
  5. FROM `store_shoppertrack` 
  6. GROUP by DATE(date_to_evaluate), hour(date_to_evaluate);
  7.  
  8.  
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
Newbie
  
Join Date: Sep 2008
Posts: 8
#9: Sep 8 '08

re: How to Create this complicated Array? Help

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

enjoy !!

Expand|Select|Wrap|Line Numbers
  1. <?php
  2.  
  3. $ampm_hour=0;
  4. $data=array();
  5.  
  6. // intial load of the hours of the day , count will be set to 0
  7.  
  8. for ($i=0; $i<=23; $i++) {
  9.    if ($i<12) {
  10.       $ampm ="AM";
  11.       $ampm_hour = $i;
  12.     }
  13.     else     {
  14.        $ampm ="PM";
  15.        $ampm_hour = $i - 12;
  16.     }
  17.  
  18.      $ampm_hour = sprintf("%02d", $ampm_hour);
  19.      $data[$ampm_hour.":00 ".$ampm] = 0;
  20.  }
  21.  
  22.  
  23. //connect to server and select database
  24. $mysqli = mysqli_connect("localhost", "youruser", "yourpass", "testDB"); // REPLACE WITH YOUR VALUES
  25.  
  26. //retrieve the count use your field and table name
  27. $sql = 'SELECT date_format(date_added, "%h:00 %p") as \'hour\', '
  28.         . ' count(*) as \'mycount\''
  29.         . ' FROM `store_shoppertrack` '
  30.         . ' where date(date_added) = 20080801'
  31.         . ' GROUP by DATE(date_added), hour(date_added);';
  32.  
  33. $result = mysqli_query($mysqli, $sql) or die(mysqli_error($mysqli));
  34.  
  35. // update the count for the hour found in the querey
  36. if (mysqli_num_rows($result) >= 1) {
  37.     while ($info = mysqli_fetch_array($result)) {
  38.        $data[$info['hour']] =  $info['mycount'];
  39.     }
  40. }
  41.  
  42. // print the array
  43.  
  44.  print "<p>";
  45.  foreach ($data as $hour => $count ) {
  46.     print "$hour => $count<br />";
  47.  
  48.  }
  49.  print "</p>";
  50.  //free results
  51.  mysqli_free_result($result);
  52.  
  53.  //close connection to MySQL
  54.  
  55. mysqli_close($mysqli);
  56. ?>
  57.  

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
Newbie
  
Join Date: Aug 2008
Posts: 9
#10: Sep 8 '08

re: How to Create this complicated Array? Help

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 :-)
Familiar Sight
  
Join Date: Sep 2007
Posts: 211
#11: Sep 8 '08

re: How to Create this complicated Array? Help

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
Newbie
  
Join Date: Sep 2008
Posts: 8
#12: Sep 8 '08

re: How to Create this complicated Array? Help

Quote:

Originally Posted by 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

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
Familiar Sight
  
Join Date: Sep 2007
Posts: 211
#13: Sep 8 '08

re: How to Create this complicated Array? Help

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
Newbie
  
Join Date: Sep 2008
Posts: 8
#14: Sep 8 '08

re: How to Create this complicated Array? Help

Quote:

Originally Posted by 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

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

Expand|Select|Wrap|Line Numbers
  1.  
  2. for ($i=0; $i<=23; $i++) {
  3.    if ($i<12) {
  4.       $ampm ="AM";
  5.       $ampm_hour = $i;
  6.     }
  7.     else     {
  8.        $ampm ="PM";
  9. //       $ampm_hour = $i - 12; // my mystake IT GIVES 00:00PM FOR 12:00 pm
  10.         $ampm_hour = ( $i == 12) ? 12 : $i - 12; // This should work
  11.     }
  12.  
  13.      $ampm_hour = sprintf("%02d", $ampm_hour);
  14.      $data[$ampm_hour.":00 ".$ampm] = 0;       
  15.  }
  16.  

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

now
11:00 AM 0
12:00 PM 0

Regards, ;)

phpNerd01
Newbie
  
Join Date: Aug 2008
Posts: 9
#15: Sep 8 '08

re: How to Create this complicated Array? Help

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!
Familiar Sight
  
Join Date: Sep 2007
Posts: 211
#16: Sep 8 '08

re: How to Create this complicated Array? Help

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
Reply

« previous question | next question »

Similar PHP bytes

/bytes/development

/bytes/it

Forums
Visit our community forums for general discussions and latest on Bytes

Our staff and community areas have moved to bytes.com/forums/

/bytes/about

We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights. Get the best answers to your questions from over 226,419 network members.

dev questions:
algorithms | asp | asp.net | c/c++ | coldfusion | c# | flash | html/css | java | javascript |
mobile dev | .net | perl | php | python | ruby | software dev | vb.net | visual basic | xml
it questions:
access | apache | careers | db2 | iis | macosx | mysql | networking | oracle | postgresql | sql server | unix | windows

About Bytes | Copyright 1995-2009 BYTES. All rights Reserved