将MySQL结果存储在PHP数组中以进行两个查询 [英] Store MySQL results in PHP array for two queries

问题描述

如何创建，存储和输出使用两个不同的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 />';

这篇关于将MySQL结果存储在PHP数组中以进行两个查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！