# Select data from csv and make associative array

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; ?>  • Any chance to store this data in a database? – Your Common Sense Dec 17 '18 at 11:33 • Yes, that would be possible. But still I would get the same results from database as are stored in variable$a, right? – Café Dec 17 '18 at 11:37
• You would never get the same results from a database as it can filter out and sort the data for you. And a database API can format the resulting array – Your Common Sense Dec 17 '18 at 12:09

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]);
}


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. 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);

• This code is more memory efficient than mine – Your Common Sense Dec 17 '18 at 12:53
• @YourCommonSense I think it will be noticeable only for large files. – Victor Dec 17 '18 at 13:30