homepage Welcome to WebmasterWorld Guest from 54.198.42.105
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member

Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

    
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;

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved