Checking Duplicate values and merge them php mysql

Question

How can i know duplicate values, and remove that,

Here is my code...

function _get($limit=NULL)
    {
        $results = array();
        $single = array();
        $this->db->select("MAX(schedule.start_at) as max_start, MAX(schedule.end_at) as max_end");
        $this->db->group_by(array("schedule.start_at", "schedule.end_at"));
        $this->db->from("schedule2_tbl as schedule");
        $q = $this->db->get();

        if( $q->num_rows() ) 
        {
            $data = $q->result_array();
            // return $data;

            foreach($data as $key => $row){
                $single[] = $row;
            }
            $results = $single;
            return $results;

        }
        
    }

This is my result,

{
"max_start": "2020-07-02 05:30:00",
"max_end": "2020-07-02 06:30:00"
},
{
"max_start": "2020-07-02 06:30:00",
"max_end": "2020-07-02 07:00:00"
},
{
"max_start": "2020-07-02 06:30:00",
"max_end": "2020-07-02 07:30:00"
},
{
"max_start": "2020-07-02 07:00:00",
"max_end": "2020-07-02 07:30:00"
}

If max_start, max_end is already there, then take greatest value. For example, in case of max_start 06:30:00 and max_end 07:30:00, There is three values,

{
"max_start": "2020-07-02 06:30:00",
"max_end": "2020-07-02 07:00:00"
},
{
"max_start": "2020-07-02 06:30:00",
"max_end": "2020-07-02 07:30:00"
},
{
"max_start": "2020-07-02 07:00:00",
"max_end": "2020-07-02 07:30:00"
}

In this case i have to store only most smallest value of max_start and greatest max_end value, That's 06:30:00 to 07:30:00.

Please help.

UPDATE

When i adding more values to the array,firs array is showing wrongly. It's showing,

2020-07-02 05:30:00 - 2020-07-02 07:30:00 
2020-07-02 07:30:00 - 2020-07-02 08:30:00.....

But,It should show,

2020-07-02 05:30:00 - 2020-07-02 06:30:00 
2020-07-02 06:30:00 - 2020-07-02 07:30:00.....

Here is my code

$initialData = $data = [
                                        [
                                        "max_start": "2020-07-02 05:30:00",
                                        "max_end": "2020-07-02 06:30:00"
                                        ],
                                        [
                                        "max_start": "2020-07-02 06:00:00",
                                        "max_end": "2020-07-02 07:30:00"
                                        ],
                                        [
                                        "max_start": "2020-07-02 06:30:00",
                                        "max_end": "2020-07-02 07:00:00"
                                        ],
                                        [
                                        "max_start": "2020-07-02 06:30:00",
                                        "max_end": "2020-07-02 07:30:00"
                                        ],
                                        [
                                        "max_start": "2020-07-02 07:00:00",
                                        "max_end": "2020-07-02 07:30:00"
                                        ],
                                        [
                                        "max_start": "2020-07-02 07:30:00",
                                        "max_end": "2020-07-02 08:30:00"
                                        ],
                                        [
                                        "max_start": "2020-07-02 08:30:00",
                                        "max_end": "2020-07-02 09:30:00"
                                        ],
                                        [
                                        "max_start": "2020-07-02 09:30:00",
                                        "max_end": "2020-07-02 11:15:00"
                                        ],
                                        [
                                        "max_start": "2020-07-02 11:15:00",
                                        "max_end": "2020-07-02 11:30:00"
                                        ],
                                        [
                                        "max_start": "2020-07-02 11:30:00",
                                        "max_end": "2020-07-02 12:00:00"
                                        ],
                                        [
                                        "max_start": "2020-07-02 11:30:00",
                                        "max_end": "2020-07-02 12:30:00"
                                        ],
                                        [
                                        "max_start": "2020-07-02 12:00:00",
                                        "max_end": "2020-07-02 12:30:00"
                                        ],
                                        [
                                        "max_start": "2020-07-02 12:30:00",
                                        "max_end": "2020-07-02 13:00:00"
                                        ],
                                        [
                                        "max_start": "2020-07-02 12:30:00",
                                        "max_end": "2020-07-02 13:30:00"
                                        ],
                                        [
                                        "max_start": "2020-07-02 13:00:00",
                                        "max_end": "2020-07-02 13:30:00"
                                        ]
                                    ];

            // Order the list chronologically by the "max_start" value, to make comparison easier later
            usort($data, function($a, $b){
                return $a['max_start'] <=> $b['max_start'];
            });


            // Final result will be collected here
            $result = [];

            // Work with the first list value as long there is one
            while ($currentInterval = array_shift($data)) {
                
                // Compare with each other value in the list
                foreach ($data as $index => $interval) {
                    
                    // Check if intervals overlap
                    // Replace "<" with a "<=" if you want to merge intervals that "touch": one interval ends at the same time another one begins
                    if ($interval['max_start'] < $currentInterval['max_end']) {

                        // Merge when needed
                        $currentInterval['max_end'] = max ($currentInterval['max_end'], $interval['max_end']);
                        
                        // Remove the merged interval
                        unset($data[$index]);
                        
                    }
                }
                
                // Add to result
                $result[] = $currentInterval;
            }
            return $result;

Here is my result:

[
[
"max_start": "2020-07-02 05:30:00",
"max_end": "2020-07-02 07:30:00"
],
[
"max_start": "2020-07-02 07:30:00",
"max_end": "2020-07-02 08:30:00"
],
[
"max_start": "2020-07-02 08:30:00",
"max_end": "2020-07-02 09:30:00"
],
[
"max_start": "2020-07-02 09:30:00",
"max_end": "2020-07-02 11:15:00"
],
[
"max_start": "2020-07-02 11:15:00",
"max_end": "2020-07-02 11:30:00"
],
[
"max_start": "2020-07-02 11:30:00",
"max_end": "2020-07-02 12:30:00"
],
[
"max_start": "2020-07-02 12:30:00",
"max_end": "2020-07-02 13:30:00"
]
]

Previous code:

$initialData = $data = $q->result_array();

            // Order the list chronologically by the "max_start" value, to make comparison easier later
            usort($data, function($a, $b){
                return $a['max_start'] <=> $b['max_start'];
            });


            // Final result will be collected here
            $result = [];

            // Work with the first list value as long there is one
            while ($currentInterval = array_shift($data)) {
                
                // Compare with each other value in the list
                foreach ($data as $index => $interval) {
                    
                    // Check if intervals overlap
                    // Replace "<" with a "<=" if you want to merge intervals that "touch": one interval ends at the same time another one begins
                    if ($interval['max_start'] < $currentInterval['max_end']) {

                        // Merge when needed
                        $currentInterval['max_end'] = max ($currentInterval['max_end'], $interval['max_end']);
                        
                        // Remove the merged interval
                        unset($data[$index]);
                        
                    }
                }
                
                // Add to result
                $result[] = $currentInterval;
}
return $result;

Silviu G · Accepted Answer · 2020-10-01T11:52:30.930

Here is a snippet that will work with the data format you posted.

$initialData = $data = [
  [
    'max_start' => '2020-07-02 05:30:00',
    'max_end' => '2020-07-02 06:30:00',
  ],
  [
    'max_start' => '2020-07-02 07:00:00',
    'max_end' => '2020-07-02 07:30:00',
  ],
  [
    'max_start' => '2020-07-02 06:30:00',
    'max_end' => '2020-07-02 07:00:00',
  ],
  [
    'max_start' => '2020-07-02 06:30:00',
    'max_end' => '2020-07-02 07:30:00',
  ]
];

// Order the list chronologically by the "max_start" value, to make comparison easier later
usort($data, function($a, $b){
    return $a['max_start'] <=> $b['max_start'];
});


// Final result will be collected here
$result = [];

// Work with the first list value as long there is one
while ($currentInterval = array_shift($data)) {
    
    // Compare with each other value in the list
    foreach ($data as $index => $interval) {
        
        // Check if intervals start at the same time
        if ($interval['max_start'] == $currentInterval['max_start']) {

            // Merge when needed
            $currentInterval['max_end'] = max ($currentInterval['max_end'], $interval['max_end']);
            
            // Remove the merged interval
            unset($data[$index]);
            
        }
    }
    
    // Add to result
    $result[] = $currentInterval;
}

echo 'Initial list: ', PHP_EOL, print_r($initialData, true);
echo 'Merged list: ', PHP_EOL, print_r($result, true);

This snippet has the following output:

Initial list: 
Array
(
    [0] => Array
        (
            [max_start] => 2020-07-02 05:30:00
            [max_end] => 2020-07-02 06:30:00
        )

    [1] => Array
        (
            [max_start] => 2020-07-02 07:00:00
            [max_end] => 2020-07-02 07:30:00
        )

    [2] => Array
        (
            [max_start] => 2020-07-02 06:30:00
            [max_end] => 2020-07-02 07:00:00
        )

    [3] => Array
        (
            [max_start] => 2020-07-02 06:30:00
            [max_end] => 2020-07-02 07:30:00
        )

)
Merged list: 
Array
(
    [0] => Array
        (
            [max_start] => 2020-07-02 05:30:00
            [max_end] => 2020-07-02 06:30:00
        )

    [1] => Array
        (
            [max_start] => 2020-07-02 06:30:00
            [max_end] => 2020-07-02 07:30:00
        )

    [2] => Array
        (
            [max_start] => 2020-07-02 07:00:00
            [max_end] => 2020-07-02 07:30:00
        )

)

Let me know it if fits your needs or if further tweaking is required.

For PHP versions prior to 7.0, replace the usort code with this one:

usort($data, function($a, $b){

    if ($a['max_start'] == $b['max_start']) {
        return 0;
    }

    return $a['max_start'] > $b['max_start'] ? -1 : 1;
});

Note that PHP 5.6 reached its end of life status on 31 December 2018, it is not recommended to use it anymore.

Hi, firstly a very thanks for your answer. i have updated my question... when i udpating array first values getting wrong, that's showing 2020-07-02 05:30:00 - 2020-07-02 07:30:00 then 2020-07-02 07:30:00 to 2020-07-02 08:30:00..... It should show 2020-07-02 05:30:00 - 2020-07-02 06:30:00 then 2020-07-02 06:30:00 to 2020-07-02 07:30:00..... Please take a look. — Sadique Here, Sep 25 '20 at 04:04
the issue is in this array `[ 'max_start' => '2020-07-02 06:00:00', 'max_end' => '2020-07-02 07:30:00' ],` — Sadique Here, Sep 25 '20 at 04:21
I think I misunderstood your question. I thought you want to merge *any and all* overlapping intervals, but now I see that you only want to merge intervals that have the same starting point. Please let me know if this interpretation is correct and I'll update my code. — Silviu G, Sep 25 '20 at 05:08
Please let me know if it helped you or further adjustments are required. — Silviu G, Sep 26 '20 at 09:55
Hi @SilviuG, `usort($data, function($a, $b){ return $a['max_start'] <=> $b['max_start']; });` not working in php 5.6 it's showing Parse error: syntax error, unexpected '>' . — Sadique Here, Oct 01 '20 at 11:31
That is the [spaceship operator](https://stackoverflow.com/questions/30365346/what-is-the-spaceship-operator-in-php-7), a feature that was introduced in `PHP 7.0`. The [`PHP 5.6` EOL was on 31 Dec 2018](https://www.php.net/eol.php), so you should not use it (at least not in production). I have updated the my answer with a workaround. — Silviu G, Oct 01 '20 at 11:52
thanks for the information. but it's not working in your previous code.... — Sadique Here, Oct 01 '20 at 12:02
i have updated my php version to 7.0 and fixed the issue.... — Sadique Here, Oct 01 '20 at 12:11

Checking Duplicate values and merge them php mysql

1 Answers1