count rows between entries - Databases forum at WebmasterWorld - WebmasterWorld

Forum Moderators: open

Message Too Old, No Replies

count rows between entries

wheelie34

9:26 am on Nov 1, 2010 (gmt 0)

WebmasterWorld Senior Member

10+ Year Member

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

10:19 am on Nov 1, 2010 (gmt 0)

WebmasterWorld Senior Member

10+ Year Member

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

2:26 pm on Nov 1, 2010 (gmt 0)

WebmasterWorld Senior Member

10+ Year Member

many thanks enigma1 does what I need.

wheelie34

7:02 pm on Nov 1, 2010 (gmt 0)

WebmasterWorld Senior Member

10+ Year Member

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

7:33 pm on Nov 1, 2010 (gmt 0)

WebmasterWorld Senior Member

10+ Year Member

Use $stack[$i] instead of $stack[$i]['index']

I though you would use an md array if it was holding other fields.

wheelie34

8:01 pm on Nov 1, 2010 (gmt 0)

WebmasterWorld Senior Member

10+ Year Member

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

8:15 pm on Nov 1, 2010 (gmt 0)

WebmasterWorld Senior Member

10+ Year Member

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;