count function

[Alicia]

Assuming that I have the following records in the database. Number to use in SELECT query is 1234

r1 - 1234

r2 - 3213

r3 - 1234

r4 - 1234

r5 - 1234

r6 - 4432

r7 - 3234

r8 - 1234

 

How can I write a function/query that can count the maximum consecutive occurence of the same number (1234) after the none same number record?

e. g:

r1 is considered 1 occurence since it matches 1234 in SQL query

r2 since it doesn't match the number in r2, we will have to start counting from 0 again

r3 yes, same number so we count 1

r4 same number, count increased from 1 to 2

r5 still the same, add another one and become 3

r6 not the same number, so start count from 0

r7 not the same, count remain 0

r8 same number detected and start count from 1 again.

 

From the records above, we want to display the maximum count of the occurence from the records will be 3.

 

Can some guru give me an idea how can I accomplish a function that can perform the above. Thank you

[taith]

$i=0;
$c=array();
$q=mysql_query("SELECT * FROM `` LIMIT 1");
$r=mysql_fetch_assoc($q);
$q2=mysql_query("SELECT * FROM ``);
while($r2=mysql_fetch_assoc($q2)){
if($r[number]==$r2[number]) $c[$i]++;
else $i++;
}

 

that'll give you an arrray of each consecutive grouping... then just sort down $c to find which is highest

[simshaun]

<?php
$testValues = array(
'r1' => 1234,
'r1' => 1234,
'r2' => 3213,
'r3' => 1234,
'r4' => 1234,
'r5' => 1234,
'r6' => 4432,
'r7' => 3234,
'r8' => 1234,
);

function getMaxConsecOccurrences($of, $in) {
$maxConsecOcc = 0;
$consecOcc = 0;

foreach ($in AS $key => $value) {
	if ($value == $of) {
		$consecOcc++;
		if ($consecOcc > $maxConsecOcc) {
			$maxConsecOcc = $consecOcc;
		}
	}
	else {
		$consecOcc = 0;
	}
}

return $maxConsecOcc;
}

echo "<pre>";
var_dump(getMaxConsecOccurrences('1234', $testValues));
echo "</pre>";