使用重复记录迭代PHP数组并删除值为0的一条记录

Question

I have a MySQL query using Laravel that I convert to a PHP Array.

The rows have values similar to this:

name | override | percentage
Eclipse | 1 | 50%
Eclipse | 0 | 75%

MySQL query

select * from table

Both rows (it's many more than just 2 in reality) have the same name, but one has override set to 0 and one has it set to 1.

How can I get rid of all records in my query result (PHP array) that are duplicates (determined by the name) AND have override set to 0? I want only the records that have been overridden with a new record which I have done, but I need a way to remove the records with override = 0, given that the records are the same but have a different percentage value.

How can this be done?

Thanks.

Answer 1

NOTE: Doing this in SQL will be WAYYYYYYYYY faster and use far less memory. I would only try this PHP approach if you cannot modify the SQL for some reason.

Maybe try out... hit the db twice, first time only get non-overrides, then get the overrides in second pass -- coerce your arrays to be indexed by name and array_merge them. (Uses a fair chunk of memory given the number of arrays and copies - but it's easy to understand and keeps it simple.

$initial = get_non_overridden();
$override = get_overridden();
$init_indexed = index_by_name($initial);
$over_indexed = index_by_name($override);

$desired_result = array_merge($init_indexed, $over_indexed);

Assuming your database gives you a standard rowset (array of rows, where each row is a hash of fields->values). We want something that looks like this instead:

[
  'Eclipse' => [
    'name' => 'Eclipse',
    'override' => '0',
    'percentage' => '75%'
  ],
  'Something' => [
    'name' => 'Something',
    'override' => '0',
    'percentage' => '20%'
  ],
]

So index_by_name would be:

function index_by_name($rowset) {
  $result = array();
  foreach ($rowset as $row) {
    $result[ $row['name'] ] = $row;
  }
  return $result;
}

There are ways to tweak your efficiency either in memory or run time, but that's the gist of what I was thinking.

array_merge then overwrites the initial ones with the overridden ones.

NOTE: this all assumes that there is only one row where Eclipse override is 1. If you have twenty Eclipse|0 and one Eclipse|1, this will work, if you have two Eclipse|1 you'd only see one of them... and there's no way to say which one.