Databases
StackOverflow2013
Postsdbo
POCount occourances by hour in PHP
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POCount occourances by hour in PHP
    text
    copied!<p>I have a script that polls a MySQL database (Nagios, specifically) for the date/time of events. I want to count how many events occur in each hour based upon all of the data my script returns.</p> <p>Here is what the results look like: (each on their own line)</p> <pre> <code> 2010-03-01 03:20:26 2010-02-28 19:07:26 2010-02-28 00:50:37 2010-02-27 17:07:35 2010-02-27 17:06:35 </code> </pre> <p>Here is the bash script that I use to get what I'm looking for:</p> <pre><code> cat temp |awk '{print $2 $0}' | cut -d: -f1 | sort | uniq -c |sort -k2 -n </code></pre> <p>Here are some sample results:</p> <pre> <code> 21 00 32 01 31 02 46 03 34 04 12 05 11 06 8 07 107 08 56 09 16 10 55 11 50 12 33 13 23 14 34 15 11 16 18 17 14 18 25 19 9 20 5 21 38 22 20 23 </code> </pre> <p>So, you can see from the above results that the little bash command parses my PHP script' s output and returns how many occurrences in column 1 and what hour of the day they occurred at in column 2. </p> <p>I need to incorporate this into my PHP script.</p> <p>Here is my code right now that retrieves it from the Nagios DB:</p> <pre><code>$query2= "SELECT * FROM nagios_statehistory WHERE ". "object_id='$tosearch' AND output='CRITICAL - Socket timeout " . "after 10 seconds' ORDER BY state_time DESC"; $result2 = mysql_query($query2) or die (mysql_error()); while($row = mysql_fetch_array($result2)) { $outtie = substr( $row['state_time'] , 0 , 10); if ($outtie !== $lastout) { //bolds the first occurrence of each day echo "&lt;b&gt;"; } /* print the actual time. This is what I'm looking * to count the occurrences of */ echo $row['state_time']; $lastout = substr( $row['state_time'] , 0 , 10); //disable bold and insert a line break echo "&lt;/b&gt;&lt;br /&gt;"; } </code></pre>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload