| count rows between entries
|
wheelie34
msg:4224777 | 9:26 am on Nov 1, 2010 (gmt 0) |
Hi guys
I have a mysql db and am calling info with php. What I would like help with is how to do what I want to do, here is the data
39 -- 2010-10-31 12:34:00 -- 1 <---
40 -- 2010-10-31 12:34:00 -- 7
41 -- 2010-10-31 12:34:00 -- 9
42 -- 2010-10-31 12:34:00 -- 1 <---
43 -- 2010-10-31 12:34:00 -- 4
52 -- 2010-10-31 12:34:00 -- 1 <---
53 -- 2010-10-31 12:34:00 -- 7
54 -- 2010-10-31 12:34:00 -- 9
55 -- 2010-10-31 12:34:00 -- 9
56 -- 2010-10-31 12:34:00 -- 1 <---
57 -- 2010-10-31 12:34:00 -- 4
58 -- 2010-10-31 12:34:00 -- 2
Using the number 1 as an example, I would like to work out the average gap between the number 1s' so for the above I would like to have
gaps = 2,1,3 (the number of rows between entries with 1)
Then I would like to grab from that
Average gap 2+1+3/3 = 2
Largest gap = 3
Smallest gap = 1
So the question, how do I get the gaps between the record, the dates wont work neither will using the first REF column as the can be in different chunks of entries.
Any help or pointers appreciated, thanks in advance
|
enigma1
msg:4224789 | 10:19 am on Nov 1, 2010 (gmt 0) |
There is going to be a something that orders the entries in a certain way. In any case you will end up with an array holding all the entries in some order from the queries executed. After that the PHP could do the rest.
// Average array to hold the gaps
$avg_array = array();
// $input = array holding the db rows
// 'index' is the db column holding the identifiers
for($i=$t=0, $j=count($input); $i<$j; $i++, $t++) {
// check for 1
if( $input[$i]['index'] == 1 ) {
$avg_array[] = $t;
$t = -1;
}
}
$avg = array_sum($avg_array)/count($avg_array);
$min = min($avg_array);
$max = max($avg_array);
|
|
Just feed it with some values for testing.
$input = array(
array('index' => 8),
array('index' => 3),
array('index' => 2),
array('index' => 5),
array('index' => 1),
array('index' => 4),
array('index' => 7),
array('index' => 1),
);
|
wheelie34
msg:4224868 | 2:26 pm on Nov 1, 2010 (gmt 0) |
many thanks enigma1 does what I need.
|
wheelie34
msg:4225048 | 7:02 pm on Nov 1, 2010 (gmt 0) |
Hi
Just noticed a problem, I was using 11 as the 'to look for' value and get 3 errors, heres my array
Array (
[0] => 1
[1] => 7
[2] => 9
[3] => 1
[4] => 4
[5] => 2
[6] => 11
[7] => 2
[8] => 11
[9] => 7
[10] => 2
[11] => 1
[12] => 11
[13] => 10
)
so it is being populated
if I look for any single digit number all works fine, if I look for 11 or 10, I get the following errors
Warning: Division by zero in /home/ora/public_html/folder/data.php on line 51
Warning: Wrong parameter count for min() in /home/ora/public_html/folder/data.php on line 52
Warning: Wrong parameter count for max() in /home/ora/public_html/folder/data.php on line 53
Heres my code
for($i=$t=0, $j=count($stack); $i<$j; $i++, $t++) {
// check for
if( $stack[$i]['index'] == $numbertocheck ) {
$avg_array[] = $t;
$t = -1;
}
}
I am not sure about this bit ['index'] with the provided array, what should be in place of index?
Will that fix the issue?
|
enigma1
msg:4225058 | 7:33 pm on Nov 1, 2010 (gmt 0) |
Use $stack[$i] instead of $stack[$i]['index']
I though you would use an md array if it was holding other fields.
|
wheelie34
msg:4225075 | 8:01 pm on Nov 1, 2010 (gmt 0) |
Thanks for that, one last question if I may, how can I find how far back the last entry for a number was?
Array (
[0] => 1
[1] => 7
[2] => 9
[3] => 1
[4] => 4
[5] => 2
[6] => 11
[7] => 2
[8] => 11
[9] => 7
[10] => 2
[11] => 1 <---- I want this to be found
[12] => 11
[13] => 10
)
I want to see how many rows since the last 1, I would like it to say 2 since last entry of the number 1, I tried array_reverse but it seems to give the wrong result? I cant find a way to search the array backwards either
|
enigma1
msg:4225087 | 8:15 pm on Nov 1, 2010 (gmt 0) |
You could replace the
$avg_array[] = $t;
with
$avg_array[$i] = $t;
so the key in the avg_array will hold the entries found with the number you searched for. Then the last entry should be like
$tmp_array = array_keys($avg_array);
$last_distance = count($stack)-array_pop($tmp_array);
echo $last_distance;
|
|
|
|
|
