Merging arrays or virtualizing an array

[rofl90]

I'm trying to track the last 7 minutes of logs, but when theres 0 to report that minute no row is created as it's all real-time logging. I need to replace the 'no-rows' with a row but with a 1, as otherwise a percentage evaluation in relation to 0 is always going to be infinite. The Problem comes when I'm retrieving an array so far I check to see how many there is with a count:

if(count($new) !== 7) {

}

Now, the date is set up in MySQL Datetime and is accurate to the second. So the datetime would be "2008-06-25 01:01:00", that would be at 1:01AM. So I need to check 1:01 through 1:08AM. But they're may be some missing for instance lets say our array (from the sql) is missing 1:05AM and 1:07AM, we'd need to identify those and insert "dummy" rows into the array, note that I do NOT want to change the actual data, hence "virtualizing" the array. But my array must look like it is real for the sake of loops. I then loop through to find the min() and max() of each and then use a series of comparisons.

 

What would be the best way of accomplishing this?

[rofl90]

Can anybody shed some light?

[rofl90]

When Barand doesn't reply, somethings gone wrong..

[Barand]

Throwing down the gauntlet? 

 

Rather than having to insert dummy rows, seed the array with the required keys first. IE generate the array first

 

$data = array (
     '1:01' => 0,
     '1:02' => 0, 
     '1:03' => 0, 
     '1:04' => 0, 
     '1:05' => 0, 
     '1:06' => 0, 
     '1:07' => 0
     ); 

 

When you process the query, allocate the results to their slots.

 

I can't be too specific as I haven't a clue what your data is like or what you are doing with the results, hence my reluctance to post.     

 

 

[rofl90]

Hehe. My data is in MySQL's DATETIME. ie a general table would be

 

minutes

________________________________________________________________________

id|||||||||||||||||||||||||||||||| line |||||||||||||||||||||| count |||||||||| date |||||||||||

1 | server monitor-dm1-|average|mysql-Open_files  94  2008-07-08 01:01:00

 

and there will be many of the same line, but the date is a key, so they're all different by a minute, for instance there is this the same, with a different count but 01:01:01 etc.  My SQL query look like this

 

<?php

select `count` from ".LOGGING_DB.".`activityMinute` where `line` = '".$spikeMinuteQL['monitor']."' and `stamp` between '".$this->getStamp('minute')."' and '".$this->getStamp('seven_minutes_ago')."'"
?>

 

LOGGING_DB is self explanatory, monitor is the line name, getStamp is a function that gets a MySQL DATETIME.

[Barand]

IF using BETWEEN, it needs to be

 

... BETWEEN lowerBound AND upperBound

 

and not the other way round.

 

[pre]

mysql> SELECT * FROM ACTIVITY WHERE actID BETWEEN 1 AND 5;

+-------+-----------+

| actID | activity  |

+-------+-----------+

|    1 | Strategy  |

|    2 | Project  |

|    3 | Policy    |

|    4 | Process  |

|    5 | Procedure |

+-------+-----------+

5 rows in set (0.00 sec)

 

 

mysql> SELECT * FROM ACTIVITY WHERE actID BETWEEN 5 AND 1;

Empty set (0.00 sec)

[/pre]

 

 

[Barand]

<?php
$start = mktime(1,1,0);
$end = strtotime ('+6 minutes', $start);

$starttime = date('Y-m-d H:i:s', $start);
$endtime = date('Y-m-d H:i:s', $end);

$data = array();
for ($i=0; $i<7; $i++)
{
    $data[date('H:i', strtotime("+$i minutes", $start))] = 0;         // create empty array for time period
}

$sql = "SELECT DATE_FORMAT(stamp, '%H:%i') , `count` 
	FROM ".LOGGING_DB.".`activityMinute` 
	WHERE `line` = '{$spikeMinuteQL['monitor']}' 
    		AND `stamp` BETWEEN '$starttime' AND '$endtime' ";
$res = mysql_query($sql) or die (mysql_error());
while (list($k, $count) = mysql_fetch_row($res))
{
$data[$k] += $count;
}
echo '<pre>', print_r($data), '</pre>';
?>