Store MySQL results in PHP array for two queries

Question

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

You want to get all cities of all countries right?

itzmukeshy7
– itzmukeshy7

2016-03-30 18:07:13 +00:00
Commented Mar 30, 2016 at 18:07 — itzmukeshy7
– itzmukeshy7, Commented Mar 30, 2016 at 18:07

Victor Tello · Accepted Answer · 2016-03-30 18:38:37Z

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 />';
    }

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 />';

Imperialized · Accepted Answer · 2016-03-30 18:00:53Z

0

Use your country_id as the key for your array.. So basically,

$select1 = mysqli_query("SELECT country_id, country_name FROM countries");
while ($country_row = mysqli_fetch_array($select1, MYSQLI_ASSOC)) {
   $array[$country_id]['country_id'] = $country_row ['country_id'];
   $array[$country_id]['country'] = $country_row['country_name'];

  $select2 = mysqli_query("SELECT city_id, city_name FROM cities where '" . $select1['country_id'] ."' "); // depends on select1
  while ($city_row = mysqli_fetch_array($select2, MYSQLI_ASSOC)) {
   $array[$country_id]['cities'][]['city_id'] = $city_row['city_id'];
   $array[$country_id]['cities'][]['city_name'] = $city_row['city_name']
  }
}

In order to print these back out using foreach (as you asked in comments), you must loop through both levels of the array

foreach($array as $country){
   //Loop through the countries that we queried and stored 
   echo 'Country Name: '.$country['country_name'].'<br/>';
   echo 'Country ID: '.$country['country_id'].'<br/>';
   echo 'Cities in country: <br/>';
   //Loop through the cities within this country
   foreach($country['cities'] as $city){
      echo 'City ID: '.$city['city_id'].'<br/>';
      echo 'City Name: '.$city['city_name'].'<br/>';
   }

}

edited Mar 30, 2016 at 18:00

answered Mar 30, 2016 at 14:40

Imperialized

5432 gold badges5 silver badges15 bronze badges

4 Comments

petsk Over a year ago

And how do I output the array with foreach()?

Imperialized Over a year ago

foreach($arr as $a){      $a['country']; //Country      $a['country_id']; //Country id      foreach($a['cities'] as $city){                } }

petsk Over a year ago

If you are in a hurry, why did you answer? Your answer is not very helpful unless you already know the basics. And if you know the basics you won't ask this question. So this won't help anyone. It will only annoy anyone seeking the same answer as I do.

Imperialized Over a year ago

I was heading into a meeting. I will update my answer to further elaborate, but in a comment I cannot exactly give a lot of details.

symcbean · Accepted Answer · 2016-03-30 19:30:44Z

0

Taking the output of one query and mailmerging it into another query in a nested loop is an anti-pattern. Use a join:

select country_ID, country_name,
  City_Id, city_name
From countries cn
Inner join cities ct
On CT.country_id = cn.country_ID

Then....

$cities=array();
$country_names=array();

While($r=mysqli_fetch_array($handle)) {
     $country_names[$r['country_id']]=$r['country_name'];
     If (!is_array($cities[$r['country_id'])) $cities[$r['country_id']]=array();
     $cities[$r['country_id']][$r['city_id']]=$r['city_name'];
}

answered Mar 30, 2016 at 19:30

symcbean

48.4k6 gold badges64 silver badges99 bronze badges

Collectives™ on Stack Overflow

Store MySQL results in PHP array for two queries

3 Answers 3

Comments

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related