PHP Comparing Key/Value in Multidimensional Array Made from CSV

Question

I have two(2) .csv files as such:

CompanySubset.csv

| id | company   | description               |
|----|-----------|---------------------------|
| 1  | Apple     | Description for Apple     |
| 2  | Microsoft | Description for Microsoft |
| 3  | IBM       | Description for IBM       |

ContactSubset.csv

| id | name  | address           | phone       |
|----|-------|-------------------|-------------|
| 1  | Bob   | 1234 Address Lane | 1+234567890 |
| 2  | Sally | 4321 Address Lane | 1+987654321 |
| 3  | Cam   | 2468 Address Lane | 1+468135901 |

Using php, I need to read each file and compare the id column for matches between the two(2). If I find a matching id between the two(2), then I need to merge that specific row together.

Ex: CompanySubset id 1 matches ContactSubset id 1. Therefore, the row with an id of 1 on ContactSubset will be merged with row 1 on CompanySubset. Which will then form the following:

| id | company | description           | name | address           | phone       |
|----|---------|-----------------------|------|-------------------|-------------|
| 1  | Apple   | Description for Apple | Bob  | 1234 Address Lane | 1+234567890 |

I have been able to get each .csv into an array(see below), but thats about it.

$filename = 'CompanySubset.csv';

$company_array = [];

if (($handle = fopen("{$filename}", "r")) !== FALSE) {

    while (($companyData = fgetcsv($handle, 1000, ",")) !== FALSE) {
        $company_array[] = $companyData;
    }

    fclose($handle);
}

Why `"{$filename}"` and not just `$filename`? – Manuel Mannhardt Apr 30 '19 at 15:58 — Manuel Mannhardt, Apr 30 '19 at 15:58

score 1 · Answer 1 · answered Apr 30 '19 at 16:09

You need to use the id element as the keys of the arrays. Then you can merge the related rows.

$filename = 'CompanySubset.csv';

$company_array = [];

if ($handle = fopen($filename, "r")) {
    while ($companyData = fgetcsv($handle, 1000, ",")) {
        $company_array[$companyData[0]] = array_slice($companyData, 1);
    }
    fclose($handle);
}

$filename = 'ContactSubset.csv';

$contact_array = [];

if ($handle = fopen($filename, "r")) {
    while ($contactData = fgetcsv($handle, 1000, ",")) {
        $contact_array[$companyData[0]] = array_slice($contactData, 1);
    }
    fclose($handle);
}

See Merging two PHP arrays with same numeric key for methods to merge the array elements with the same keys.

There's also no need for all those !== FALSE tests, just use the value of the assignment as the conditional expression (there are a few times when this is needed, e.g. testing the result of array_search() because 0 is falsey, but none of your uses require it).

score 0 · Answer 2 · edited Jun 20 '20 at 09:12

Following function has been used:

array_walk() - Apply a user supplied function to every member of an array

array_merge - Merge one or more arrays

Solution:

$companySubset = [
  1 => ['Apple','Description for Apple'],
  2 => ['Microsoft','Description for Microsoft'],
  3 => ['IBM','Description for IBM']
];

$contactSubset = [
 1 => ['Bob','1234 Address Lane','1+234567890 '],
 2 => ['Sally','4321 Address Lane','1+987654321'],
 3 => ['Cam','2468 Address Lane','1+468135901']
];
$res =[];
array_walk($companySubset, function($v,$k) use ($contactSubset, &$res){
  $res[] = array_key_exists($k, $contactSubset) ? array_merge($v,$contactSubset[$k]) : $v;
});

This still leaves open the problem of creating the two arrays correctly from the files. — Barmar, Apr 30 '19 at 16:04

score 0 · Answer 3 · answered Apr 30 '19 at 16:07

Create two two-dimensional arrays with id's as a keys out of your csv's.
Use https://www.php.net/manual/ru/function.array-intersect-key.php to get set of id's that intersect between two.
Iterate through set of id's from step 2 if there is any and compare actual values.

score 0 · Accepted Answer · answered Apr 30 '19 at 16:11

This solves it by first reading the ContactSubset.csv and creating an array indexed by the first field (removing it from the result using array_shift()).

Then the second loop is virtually the same as your code already, but as it reads each line it checks for the contact details and uses array_merge() to add in the extra details...

$filename = 'ContactSubset.csv';
$contact_array = [];

if (($handle = fopen($filename, "r")) !== FALSE) {
    while (($contactData = fgetcsv($handle)) !== FALSE) {
        $id = array_shift($contactData);
        $contact_array[$id] = $contactData;
    }
    fclose($handle);
}

$filename = 'CompanySubset.csv';
$company_array = [];

if (($handle = fopen($filename, "r")) !== FALSE) {
    while (($companyData = fgetcsv($handle)) !== FALSE) {
        if ( isset($contact_array[$companyData[0]]) )   {
            $companyData = array_merge($companyData, $contact_array[$companyData[0]]);
        }
        $company_array[] = $companyData;
    }

    fclose($handle);
}
print_r($company_array);

PHP Comparing Key/Value in Multidimensional Array Made from CSV

4 Answers4