Select data from csv and make associative array

Question

I've got a .csv file with ZIP codes containing the city and its sub-districts

67401,City 1,district_a
67401,City 2,district_b
67401,City 3,district_c
67401,City 3,district_d
67401,City 3,district_e
67401,City 3,district_f
67401,City 3,district_g
67401,City 3,district_h
67401,City 3,district_i
67401,City 3,district_j
67401,City 3,district_k
67401,City 3,district_l
67401,City 3,district_m
67401,City 3,district_n
67401,City 3,district_o
67401,City 3,district_p
67401,City 3,district_q
67401,City 3,district_r
67401,City 3,district_s
67401,City 4,district_t
67401,City 5,district_u

67501,City 6,district_v
67501,City 7,district_w
67501,City 8,district_x
67501,City 8,district_y
67501,City 8,district_z

And I need to put it in an array, where I can select/search by the zip code (67401, 67501, etc.) and return these results as an associative array in this format, so I can populate a select control.

For example I want to look-up all the Cities and its districts with zip code 67401 I need this result:

"City 1" => [
    district_a => district_a
],
"City 2" => [
    district_b => district_b
],
"City 3" => [
    district_c => district_c
    district_d => district_d
    district_e => district_e
    district_f => district_f
    district_g => district_g
    district_h => district_h
    district_i => district_i
    district_j => district_j
    district_k => district_k
    district_l => district_l
    district_m => district_m
    district_n => district_n
    district_o => district_o
    district_p => district_p
    district_q => district_q
    district_r => district_r
    district_s => district_s
],
"City 4" => [
    district_t => district_t
],
"City 5" = [
    district_u => district_u
]

Right now I've got this working code, but I'm sure there has to be a simpler way to achieve it.

<?php 
$zipcode = '67401';

$a = file("zip.csv", FILE_IGNORE_NEW_LINES | FILE_SKIP_EMPTY_LINES);
$a = array_map(function ($i) {return explode(',', $i); }, $a);
$a = array_filter($a, function ($i) use ($zipcode) { return $i[0] == $zipcode; });

$b = [];
$i = 0;
foreach ($a as $key => $value) {
    $b[$i++] = [
        'city' => $value[1],
        'district'  => $value[2],
    ];
}

$c = [];

foreach($b as $key => $item) {
   $c[$item['city']][$key] = $item;
}

ksort($c, SORT_REGULAR);

$d = [];
foreach($c as $key => $value) {
    $subarray = [];
    foreach($value as $s_key => $s_val) {
        foreach($s_val as $ss_key => $ss_val) {
            $subarray[$ss_val] = $ss_val;
        }
    }
    $d[$key] = $subarray;
}

$myresult = $d;
?>

Answer 1

Well, first of all this data must be stored in a database, not a file. Using PDO you'll get your array in a few lines (assuming a database connection is already established):

$sql = "SELECT city, district FROM zip WHERE zipcode=? ORDER BY city, district";
$stmt = $pdo->prepare($sql);
$stmt->execute([$zipcode]);
$data = $stmt->fetchAll(PDO::FETCH_GROUP|PDO::FETCH_COLUMN); 

As of the code present, there are way too much loops to my taste. I believe everything could be done in one loop, like

$zipcode = '67401';
$data = [];
foreach (file("zip.csv", FILE_IGNORE_NEW_LINES | FILE_SKIP_EMPTY_LINES) as $row) {
    list($zip, $city, $district) = explode(",",$row);
    if ($zip == $zipcode) {
        if (!isset($data[$city])) {
            $data[$city] = [];
        }
        $data[$city][] = $district;
    }
}

well if you need to sort your arrays, a couple extra loops are still needed

ksort($data);
foreach ($data as $city => $array) {
    sort($data[$city]);
}

Answer 2

If this were my project, I'd certainly entertain the idea of using regex. Now, it is common knowledge that regular expressions typically offer flexibility with a cost of drag on efficiency. However, because it avoids reading line by line and so many iterated explosions, there may be a chance that it performs well on your large data size. (no guarantees, just a suggestion to benchmark)

Code:

$zip = '67401';
if (preg_match_all("~^{$zip},([^,]+),([^,]+)$~m", file_get_contents('zip.csv'), $matches)) {
    foreach ($matches[1] as $index => $city) {
        $result[$city][$matches[2][$index]] = $matches[2][$index];
    }
}
var_export($result);  // see the correct/desired result

The simple above pattern, matches one whole line at a time by using anchors ^ and $. The $zip variable is wrapped in curly braces as a matter of personal preference / readability, but it is not captured, because the value is known and it is not used in the output. By using negated characters classes containing commas, the regex engine can move with maximum efficiency (in a greedy fashion).

---

Alternatively, it seems a good idea to break early (assuming your csv file is already sorted by zipcode). Of course, a function like fgetcsv() is a very sensible/reliable tool for processing rows of csv data. The following snippet largely resembles @Victor's answer.

$zipcode = '67401';
$result = [];
$handle = fopen('zip.csv', 'r');
while (($row = fgetcsv($handle)) !== false) {
    if ($zipcode == $row[0]) {
        $result[$row[1]][$row[2]] = $row[2];
    } elseif ($result) {  // the current row doesn't match $zipcode; and $result is not empty
        break;
    }
}
fclose($handle);
var_export($result);

and of course, if you prefer to write meaningful variables names, you can make iterated declarations of $zip, $city, and $district if you like, by way of array destructuring.

p.s. If your csv data is not already pre-sorted and you require the output to be sorted in a particular way, please express these facts in your question.

Answer 3

Some extra inspirational stuff ;)

// Load your CSV into array
$zipcode = '67401';
$options = [];
$fh = fopen('zip.csv', 'r');
while (($data = fgetcsv($fh)) !== false) {
    if ($zipcode == $data[0]) {
        list($zip, $city, $district) = $data;
        $options[$city][$district] = $district;
    }
}
fclose($fh);

// Sort cities
ksort($options);

// Sort districts
array_walk($options, function(&$districts) {
    ksort($districts);
});

// Enjoy!
print_r($options);