Count function

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

There is one problem.
Records in the database has no order. So, there are no r1, r2, r3 - they are random. So, no “consecutive occurence”.

Why don’t you tell us the real case? what all these numbers for and for what purpose you need this counting?

If each row has a primary key called Id, you can count the number of consecutive identifiers for your query.

First we’ll create some variables to keep track of things


$number = '1234';
$count = 0;
$maxCount = 0;
$oldId = 0;

Then we’ll iterate through the database results


...
$sql = 'SELECT Id, Number FROM Numbers WHERE Number = ' . $number . ' ORDER BY Id';
$result = mysql_query($sql);
if (mysql_num_rows($result) > 0) {
    while (TRUE) {
        $row = mysql_fetch_assoc($result);
        if ($row === FALSE) {
            break;
        }
        $id = intval($row['Id']);

Now we can check if the identifier is one more than the previous one


...
        if ($id - 1 === $oldId) {
            $count += 1;
        }
        if ($count > $maxCount) {
            $maxCount = $count;
        }

If the Id is not one greater, we need to reset the count


...
        if ($id - 1 <> $oldId) {
            $count = 0;
        }

And finally we store the existing Id, for use the next time around


...
        $oldId = $Id;
    }
}
// $maxCount should be the greatest number of consecutive records

Please note that the above code has not been tested. I ave to head out soon - but the above code should be close to a working solution.

this does not allow for deletions, hence will not include actual runs which include a gap

what you need is a method to detect if row N is 1234 and row M is 1234, that there is no row in between N and M with some other number

Then it becomes a matter of running through all of the table rows.


$sql = 'SELECT Id, Number FROM Numbers ORDER BY Id';

Instead of checking the Id, the Number would be checked instead.


$numberToFind = '1234';
...
$number = intval($row['Number']);
if ($number === $numberToFind && $number === $lastNumber) {
    $count += 1;
    $maxCount = ($count > $maxCount) ? $count : $maxCount;
} else {
    $count = 0;
}
$lastNumber = $number;

Something like that would do the trick.

Hi guys,

Can you please look into this one comments on it : thanks.

DB structure we have :


CREATE TABLE IF NOT EXISTS `draw_doc` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `draw_id` varchar(50) NOT NULL DEFAULT '',
  `time` varchar(50) NOT NULL DEFAULT '',
  `week` varchar(5) NOT NULL DEFAULT '',
  `number` varchar(4) NOT NULL DEFAULT '0',
  `result` varchar(50) NOT NULL DEFAULT '',
  `type` varchar(10) NOT NULL DEFAULT '',
  `type_id` int(250) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=946 ;


Script we are trying to make it work but the count is only showing zero


$sql = mysql_query("SELECT `id`, `number` FROM `draw_doc` ORDER BY `id` ASC");
$row=mysql_fetch_array($sql);
$numberToFind = '1100';
// echo "$numberToFind "."&lt;br&gt;";
$number = intval($row['number']);
if ($number === $numberToFind && $number === $lastNumber) {
$count += 1;
$maxCount = ($count &gt; $maxCount) ? $count : $maxCount;
} else {
$count = 0;
}
$lastNumber = $number;
echo "$lastNumber";
 echo "$count";

Can some guru please advise what need to be done to make the function generate the result required. Thanks

You’re only getting one single row from the results.

What you need to do is to use a while loop so that you go through all of the result rows.

I will use here what is known as a one-and-a-half while loop here, as it runs at least once, but breaks out halfway through when no more rows are found. This helps us to use good coding techniques, and we don’t end up doubling the mysql_fetch_array line.

I have also pulled $numberToFind to outside of the loop, as the loop causes no changes to occur to that variable. I have also renamed $sql because the name is completely misleading. Normally $sql contains a string of SQL commands. You are using it to hold a result set from the database, so calling the variable $result is much more standard and makes more sense.


$numberToFind = '1100';
$result = mysql_query("SELECT `id`, `number` FROM `draw_doc` ORDER BY `id` ASC");
if (mysql_count_rows($result) &gt; 0) {
    while (TRUE) {
        $row = mysql_fetch_array($sql);
        if ($row === FALSE) {
            break;
        }
        ...
        $lastNumber = $number;
    }
}