Compare array values and selecting specific one

I need a bit of help with the below concept.

I have an array:

$values = array(
    array(
        'logId'     => 1000,
        'userId'    => 1111,
        'dateTime'  => '2021-01-04 08:10:50',
        'event'     => 'OUT'
    ),
    array(
        'logId'     => 1001,
        'userId'    => 1111,
        'dateTime'  => '2021-01-04 08:11:50',
        'event'     => 'IN'
    ),
    array(
        'logId'     => 1002,
        'userId'    => 1111,
        'dateTime'  => '2021-01-04 08:11:53',
        'event'     => 'IN'
    ),
    array(
        'logId'     => 1003,
        'userId'    => 1111,
        'dateTime'  => '2021-01-04 17:10:50',
        'event'     => 'OUT'
    ),
    array(
        'logId'     => 1004,
        'userId'    => 2222,
        'dateTime'  => '2021-01-04 08:10:50',
        'event'     => 'IN'
    ),
    array(
        'logId'     => 1005,
        'userId'    => 2222,
        'dateTime'  => '2021-01-04 17:00:50',
        'event'     => 'OUT'
    ),
);

I would like to loop through it and select the “first IN” and “last OUT” based on “dateTime”. So I should be getting:

1 | 1001 | 1111 | 2021-01-04 08:11:50 | IN |
3 | 1003 | 1111 | 2021-01-04 17:10:50 | OUT |
4 | 1004 | 2222 | 2021-01-04 08:10:50 | IN |
5 | 1005 | 2222 | 2021-01-04 17:00:50 | OUT |

How is it possible to achieve this? Appreciate any help.

Thanks,

Answer

The way I would approach this is to:

1. Filter the RAW input array to the earliest in and the latest out. We don’t need the rest of the results.

2. Re-format that output in the format expected.

The dateTime strings appear to be properly formatted time stamps (presumably from a mysqli database). Which is good because it means we can compare the timestamps with < and >.

Solution

// Define the filter array
$filterArray = [];

foreach($values as $log){

    // Set a reference to this userId in the new array
    $user = &$filterArray[$log["userId"]];


    // If nothing has been added for this person's IN/OUT yet then add it on here
    if(! ($user[$log["event"]] ?? null)){
        $user[$log["event"]] = $log;
    }

    // If the event is IN then check to see if this IN is earlier than the one already stored
    if($log["event"] == "IN" && $user["IN"]["dateTime"] > $log["dateTime"]){
        $user["IN"] = $log["dateTime"];
    }
    
    // If the event is OUT then check to see if this OUT is later than the one already stored
    if($log["event"] == "OUT" && $user["OUT"]["dateTime"] < $log["dateTime"]){
        $user["OUT"] = $log;
    }
}


// Sort by userId
ksort($filterArray);

// Define the output array
$outArray = [];

// Fill the output array
foreach($filterArray as $log){
    $outArray[] = $log["IN"];
    $outArray[] = $log["OUT"];

}

Output

array (
  0 => 
  array (
    'logId' => 1001,
    'userId' => 1111,
    'dateTime' => '2021-01-04 08:11:50',
    'event' => 'IN',
  ),
  1 => 
  array (
    'logId' => 1003,
    'userId' => 1111,
    'dateTime' => '2021-01-04 17:10:50',
    'event' => 'OUT',
  ),
  2 => 
  array (
    'logId' => 1004,
    'userId' => 2222,
    'dateTime' => '2021-01-04 08:10:50',
    'event' => 'IN',
  ),
  3 => 
  array (
    'logId' => 1005,
    'userId' => 2222,
    'dateTime' => '2021-01-04 17:00:50',
    'event' => 'OUT',
  ),
)

Code without comments

$filterArray = [];
foreach($values as $log){
    $user = &$filterArray[$log["userId"]];
    if(! ($user[$log["event"]] ?? null)){
        $user[$log["event"]] = $log;
    }
    if($log["event"] == "IN" && $user["IN"]["dateTime"] > $log["dateTime"]){
        $user["IN"] = $log["dateTime"];
    }
    if($log["event"] == "OUT" && $user["OUT"]["dateTime"] < $log["dateTime"]){
        $user["OUT"] = $log;
    }
}
ksort($filterArray);
$outArray = [];
foreach($filterArray as $log){
    $outArray[] = $log["IN"];
    $outArray[] = $log["OUT"];
}