如何将foreach数组划分为php上的切片

Question

I have the select dropdown list where I echo the list of cities:

$pdo = new PDO(...);
$cities = $pdo->query('SELECT `id`, `city` FROM `cities` ORDER BY `id` ASC');
$cities->execute();
foreach($cities as $row) {
echo '<option value="'.$row['city'].'">'.$row['city'].'</option>';
}

I need to divide all the cities by country with optgroup, for example now I have:

<select>
<option value="Edmonton">Edmonton</option>
<option value="Ottawa">Ottawa</option>
<option value="Winnipeg">Winnipeg</option>
<option value="Chicago">Chicago</option>
<option value="Los Angeles">Los Angeles</option>
<option value="New York">New York</option>
</select>

and I need to get:

<optgroup label="Canada">
<option value="Edmonton">Edmonton</option>
<option value="Ottawa">Ottawa</option>
<option value="Winnipeg">Winnipeg</option>
</optgroup>
<optgroup label="USA">
<option value="Chicago">Chicago</option>
<option value="Los Angeles">Los Angeles</option>
<option value="New York">New York</option>
</optgroup>

The list and ID won't be changed, I demonstrated example only so it's enough to divide by ID

Answer 1

First, you have to modify your db, to have country column. Then, follow this code.

$pdo = new PDO(...);
$cities = $pdo->query('SELECT `id`, `city`, `country` FROM `cities` ORDER BY `id` ASC');
$cities->execute();
$data = array();
foreach($cities as $row) {
$data[$row['country']][] = $row['city'];
}
foreach($data as $country){
echo "<optgroup label='{$country}'>";
foreach($country as $city){
echo "<option value='{$city}'>{$city}</option>";
}
echo "</optgroup>";
}