php filtering an array by key

Question

I have a csv file in a format resembling the following. There are no column heads in the actual file - They are shown here for clarity.

id|user|date|description
0123456789|115|2011-10-12:14:29|bar rafael
0123456789|110|2012-01-10:01:34|bar rafael
0123456902|120|2011-01-10:14:55|foo fighter
0123456902|152|2012-01-05:07:17|foo fighter
0123456902|131|2011-11-21:19:48|foo fighter

For each ID, I need to keep the most recent record only, and write the results back to the file.

The result should be:

0123456789|110|2012-01-10:01:34|bar rafael
0123456902|152|2012-01-05:07:17|foo fighter

I have looked at the array functions and don't see anything that will do this without some kind of nested loop.

Is there a better way?

Answer 1

const F_ID = 0;
const F_USER = 1;
const F_DATE = 2;
const F_DESCRIPTION = 3;

$array = array();

if (($handle = fopen('test.csv', 'r')) !== FALSE) {
  while (($data = fgetcsv($handle, 1000, '|')) !== FALSE) {

    if (count($data) != 4)
      continue; //skip lines that have a different number of cells

    if (!array_key_exists($data[F_ID], $array)
      || strtotime($data[F_DATE]) > strtotime($array[$data[F_ID]][F_DATE]))
      $array[$data[F_ID]] = $data;
  }
}

You'll have, in $array, what you want. You can write it using fputcsv.

NOTE. I didn't test this code, it's meant to provide a basic idea of how this would work.

The idea is to store the rows you want into $array, using the first value (ID) as the key. This way, on each line you read, you can check if you already have a record with that ID, and only replace it if the date is more recent.

Answer 2

Each time you encounter a new id, put it in your $out array. If the id already exists, overwrite it if the value is newer. Something like:

$in_array = file('myfile.txt');
$out_array = array();
$fields = array('id', 'user', 'date', 'description');

foreach($in_array as $line) {
    $row = array_combine($fields, explode('|', $line) );
    //New id? Just add it.
    if ( !isset($out_array[ $row['id'] ]) ) {
        $out_array[ $row['id'] ] = $row;
    }
    //Existing id? Overwrite if newer.
    else if (strcmp( $row['date'], $out_array[ $row['id'] ]['date'] ) > 0 ) {
        $out_array[ $row['id'] ] = $row;
    }
    //Otherwise ignore
}

//$out_array now has the newest row for each id, keyed by id.