将MySQL结果存储在PHP数组中以进行两个查询
问题描述:
如何创建,存储和输出使用两个不同的mysql查询的数组?
How do I create, store and output an array that uses two different mysql queries?
我试图举一个简单的例子。
I tried to make a simple example.
$select1 = "SELECT country_id, country_name FROM countries ...";
while ($select1) {
...store country results in array...
$select2 = "SELECT city_id, city_name FROM cities where '" . $select1['country_id'] . "'..."); // depends on select1
while ($select2) {
...store city results in array...
}
}
**output something like this:**
country_id = 1
country_name = United States
city_id = 1
city_name = New York
city_id = 2
city_name = Las Vegas
country_id = 2
country_name = Canada
city_id = 3
city_name = Ottawa
答
我不知道你是否检查错误,准备或转义查询,但请这样做。
I do not know if you are checking for errors, preparing or escaping your queries but please do so.
要生成数组,可以使用以下方法:
To generate your array you can do it with this:
$list = [];
$countries = $link->query("SELECT country_id, country_name FROM countries ...");
while ($country_row /*fetch from $countries*/) {
$country_id = $country_row['country_id'];
$country_info = [
'country_id' => $country_id,
'country_name' => $country_row['country_name'],
'country_cities' => []
];
$cities_stmt = "SELECT city_id, city_name FROM cities where $country_id...";
$cities = $link->query($cities_stmt);
while ($city_row /*fetch from $cities*/) {
$city_id = $city_row['city_id'];
$country_info['country_cities'][$city_id] = [
'city_id' => $city_id,
'city_name' => $city_row['city_name']
];
}
$list[$country_id] = $country_info;
}
要显示数组,您可以执行以下操作:
To display your array you can do:
foreach ( $list as $country_id => $country_info ) {
echo "Country ID: $country_id<br />";
echo 'Country Name: ' . $country_info['country_name'] . '<br />';
echo 'Country Cities:<br />';
$cities = $country_info['country_cities'];
foreach ( $cities as $city_id => $city_info ) {
echo " City ID: $city_id<br />";
echo ' City Name: ' . $city_info['city_name'] . '<br />';
}
echo '<br />';
}
此外,如果您知道国家(地区)ID或城市ID,也可以执行以下操作:
Also, if you know the country id or city id you can do:
echo 'City Name: ' . $list[$country_id]['country_cities'][$city_id]['city_name'] . '<br />';