skip to Main Content

I am fetching data from a mysql database that looks like this:

ID Status
1 Green
2 Red
3 Red
4 Green
5 Green
6 Green
7 Green
8 Grey

And I want to find out, what is the highest consecutive count of Red, Green and Grey.

The outcome should be

Green: 4

Red: 2

Grey: 1

I tried How to count the consecutive duplicate values in an array? and Count Number of Consecutive Occurrence of values in Table but this is not exactly what I need.

I dont want to count every occurance and sum it up. I want to know the highest consecutive count of all of them.

I have tried the following, but it only works for certain scenarios.

$consecResult = array();
$prev_value = array("value" => null, "amount" => null);
while($row = $result->fetch_assoc())
{
    if ($prev_value['value'] != $row['status']) 
    {
       unset($prev_value);
       $prev_value = array('value' => $row['status'], 'amount' => 0);
       $consecResult[$row['status']] =& $prev_value;
    }

    $prev_value['amount']++;
}

Hope that makes sense!

Thanks!

5

Answers


  1. Chosen as BEST ANSWER

    The answer is already in my question. The provided code does work if I loop through a nice array. I hope I did not make the array fit the solution ;-)

    Anyway. This is how you can reproduce it!

    <?php
    
    $status[0]['status'] = "Green";
    $status[1]['status'] = "Red";
    $status[2]['status'] = "Red";
    $status[3]['status'] = "Green";
    $status[4]['status'] = "Green";
    $status[5]['status'] = "Green";
    $status[6]['status'] = "Green";
    $status[7]['status'] = "Grey";
    
    $consecResult = array();
    $prev_value = array("value" => null, "amount" => null);
    foreach($status as $stat)
    {
        if ($prev_value['value'] != $stat['status']) 
        {
            unset($prev_value);
            $prev_value = array('value' => $stat['status'], 'amount' => 0);
            $consecResult[$stat['status']] =& $prev_value;
        }
    
        $prev_value['amount']++;
    }
    
    echo "<pre>";
    print_r($consecResult);
    
    ?>
    

    RESULT

    Array
    (
        [Green] => Array
            (
                [value] => Green
                [amount] => 4
            )
    
        [Red] => Array
            (
                [value] => Red
                [amount] => 2
            )
    
        [Grey] => Array
            (
                [value] => Grey
                [amount] => 1
            )
    )
    
    

    UPDATE TO RESULT

    Turns out Answer #1 is not correct and the array was indeed made to fit the solution.

    If you test with

    $status[0]['status'] = "Green";
    $status[1]['status'] = "Green";
    $status[2]['status'] = "Green";
    $status[3]['status'] = "Green";
    $status[4]['status'] = "Red";
    $status[5]['status'] = "Red";
    $status[6]['status'] = "Grey";
    $status[7]['status'] = "Red";
    $status[8]['status'] = "Grey";
    $status[9]['status'] = "Green";
    

    the result will be

    Array
    (
        [Green] => Array
            (
                [value] => Green
                [amount] => 1
            )
    
        [Red] => Array
            (
                [value] => Red
                [amount] => 1
            )
    
        [Grey] => Array
            (
                [value] => Grey
                [amount] => 1
            )
    )
    

    Question is, what is missing? :-)


  2. Since you are using mysql, it might be good idea to leave the dirty work to database and use GROUP BY function on status column and then retrieve the result array.

    SELECT COUNT(ID), Status
    FROM color_table
    GROUP BY Status
    ORDER BY COUNT(ID) DESC; 
    

    You can find example for this on this page

    PHP & MYSQL: using group by for categories

    Login or Signup to reply.
  3. Here’s the algo for your use case. Should work on any other use case:

    <?php
    $status[0]['status'] = "Green";
    $status[1]['status'] = "Green";
    $status[2]['status'] = "Green";
    $status[3]['status'] = "Green";
    $status[4]['status'] = "Red";
    $status[5]['status'] = "Red";
    $status[6]['status'] = "Grey";
    $status[7]['status'] = "Red";
    $status[8]['status'] = "Grey";
    $status[9]['status'] = "Green";
    
    $curr_count = 0;
    $consecResult = array();
    $prev_value = null;
    
    foreach($status as $stat) {
    
        $value = $stat['status'];
        
        # if value doesn't exist in consecResult, add the value with amount = 1 (if already exist, pass)
        if (! array_key_exists($value, $consecResult)) {
            $consecResult[$value] = array('value' => $value, 'amount' => 1);
        }
        
        # if prev_value is same as the value, increment the count variable else set it back to 1
        $curr_count = ($prev_value == $value) ? $curr_count + 1 : 1;
            
        # if amount of the value in consecResult array is lesser than current consecutive count ($curr_count), set new value. Otherwise, pass
        if ($consecResult[$value]['amount'] < $curr_count) {
            $consecResult[$value]['amount'] = $curr_count;
        }
        
        $prev_value = $value;
    
    }
    
    echo "<pre>";
    print_r($consecResult);
    ?>
    

    RESULT

    Array
    (
        [Green] => Array
            (
                [value] => Green
                [amount] => 4
            )
    
        [Red] => Array
            (
                [value] => Red
                [amount] => 2
            )
    
        [Grey] => Array
            (
                [value] => Grey
                [amount] => 1
            )
    
    )
    
    Login or Signup to reply.
  4. $input = [
      [ 'ID' => 1, 'Status' => 'Green' ],
      [ 'ID' => 2, 'Status' => 'Red' ],
      [ 'ID' => 3, 'Status' => 'Red' ],
      [ 'ID' => 4, 'Status' => 'Green' ],
      [ 'ID' => 5, 'Status' => 'Green' ],
      [ 'ID' => 6, 'Status' => 'Green' ],
      [ 'ID' => 7, 'Status' => 'Green' ],
      [ 'ID' => 8, 'Status' => 'Grey' ]
    ];
    
    $result =
      array_map(
        'max',
        array_reduce(
          array_column($input, 'Status'),
          function ($carry, $status) {
            if ($carry[1] === $status) {
              $carry[0][$status][array_key_last($carry[0][$status])]++;
            } else {
              $carry[0][$status][] = 1;
              $carry[1] = $status;
            }
            return $carry;
          },
          [ [], '' ]
        )[0]
      );
    
    print_r($result);
    

    Output:

    Array
    (
        [Green] => 4
        [Red] => 2
        [Grey] => 1
    )
    

    Short explanation:

    array_reduce starts with this initial result array:

    [ 
      [],  // will hold all the counts for each color (not only the highest one)
      ''   // will hold the value of 'status' of the prior item in the array
    ]
    

    And it starts also with this input array:

    array_column($input, 'Status')
    

    The result of it looks like this:

    Array
    (
        [0] => Array
            (
                [Green] => Array
                    (
                        [0] => 1   // first occurence of 'Green'
                        [1] => 4   // second occurence of 'Green'
                                   // etc.
                    )
    
                [Red] => Array
                    (
                        [0] => 2   // first occurence of 'Red'
                                   // etc.
                    )
    
                [Grey] => Array    // first occurence of 'Grey'
                                   // etc.
                    (
                        [0] => 1
                    )
    
            )
    
        [1] => Grey   // this item is then dropped by using [0]
    )
    

    The last step is to apply max to each color item in the result array within array_map.

    Login or Signup to reply.
  5. I have tested both arrays which you have shared i.e.

    $status[0]['status'] = "Green";
    $status[1]['status'] = "Red";
    $status[2]['status'] = "Red";
    $status[3]['status'] = "Green";
    $status[4]['status'] = "Green";
    $status[5]['status'] = "Green";
    $status[6]['status'] = "Green";
    $status[7]['status'] = "Grey";
    

    AND

    $status[0]['status'] = "Green";
    $status[1]['status'] = "Green";
    $status[2]['status'] = "Green";
    $status[3]['status'] = "Green";
    $status[4]['status'] = "Red";
    $status[5]['status'] = "Red";
    $status[6]['status'] = "Grey";
    $status[7]['status'] = "Red";
    $status[8]['status'] = "Grey";
    $status[9]['status'] = "Green";
    

    And my code works as expected.

    Here is my code:

    $status = array();
    $status[0]['status'] = "Green";
    $status[1]['status'] = "Green";
    $status[2]['status'] = "Green";
    $status[3]['status'] = "Green";
    $status[4]['status'] = "Red";
    $status[5]['status'] = "Red";
    $status[6]['status'] = "Grey";
    $status[7]['status'] = "Red";
    $status[8]['status'] = "Grey";
    $status[9]['status'] = "Green";
    
    $highestCounts = array(
        'Green' => 0,
        'Red' => 0,
        'Grey' => 0
    );
    
    $currentCounts = array(
        'Green' => 0,
        'Red' => 0,
        'Grey' => 0
    );
    
    $previousStatus = '';
    
    foreach ($status as $item) {
        $currentStatus = $item['status'];
    
        if ($currentStatus === $previousStatus) {
            $currentCounts[$currentStatus]++;
        } else {
            $currentCounts[$currentStatus] = 1;
        }
    
        $previousStatus = $currentStatus;
    
        // Update the highest count if necessary
        if ($currentCounts[$currentStatus] > $highestCounts[$currentStatus]) {
            $highestCounts[$currentStatus] = $currentCounts[$currentStatus];
        }
    }
    
    // Print the results
    foreach ($highestCounts as $status => $count) {
        echo $status . ': ' . $count . "n";
    }
    
    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search