Codeigniter和JOIN查询有问题

Question

All I am trying to do is query a database table for a list of counties in my state. I want to pull 5 cities that correspond to those counties and list them on the page. I am using a JOIN in order to do this because that is what my searching lead me to believe that I need.

So far I am passing all of the information from my Model to my Controller, and then displaying it in my View with no issues. The problem is that the cities list seems to be limiting its self to only 1 city per county. Do you see a problem with my query? I have tried (seemingly) everything, but because I am so new I am sure that there is something silly that I am missing.

<? 
class Index_model extends CI_Model {

    function __construct()
    {
        // Call the Model constructor
        parent::__construct();
        $this->load->database();
    }

    public function countiesWithCities() {
        $this->db->select("*");
        $this->db->from("cities");
        $this->db->join("counties", "cities.county_id = counties.county_id");
        $query = $this->db->get();
        return $query->result();
    }

}

This is the result of that query:

array(5) { 
[0]=> object(stdClass)#17 (5) { 
["city_id"]=> string(1) "0" 
["county_id"]=> string(1) "5" 
["city_name"]=> string(8) "Munising" 
["county_name"]=> string(5) "Alger" 
["number_of_cities"]=> string(1) "1" 
} 
[1]=> object(stdClass)#18 (5) { 
["city_id"]=> string(1) "1" 
["county_id"]=> string(1) "1" 
["city_name"]=> string(7) "Redford" 
["county_name"]=> string(5) "Wayne" 
["number_of_cities"]=> string(1) "2" 
} 
[2]=> object(stdClass)#19 (5) { 
["city_id"]=> string(1) "2" 
["county_id"]=> string(1) "1" 
["city_name"]=> string(7) "Livonia" 
["county_name"]=> string(5) "Wayne" 
["number_of_cities"]=> string(1) "2" 
} 
[3]=> object(stdClass)#20 (5) { 
["city_id"]=> string(1) "3" 
["county_id"]=> string(1) "4" 
["city_name"]=> string(6) "Monroe" 
["county_name"]=> string(6) "Monroe" 
["number_of_cities"]=> string(1) "1" 
} 
[4]=> object(stdClass)#21 (5) { 
["city_id"]=> string(1) "4" 
["county_id"]=> string(1) "2" 
["city_name"]=> string(16) "Farmington Hills" 
["county_name"]=> string(7) "Oakland" 
["number_of_cities"]=> string(1) "1" 
} 
}

It seems fine but for example, array[2] should have more cities listed under it, other than "Livonia." I can't seem to figure out how to complete this? Anyone have any advice or help?

Answer 1

I got it. I ended up getting rid of the JOIN because the array it was giving me was a 1:1 ratio for county to city. I needed it combined. So I queries the counties table and looped through that. As I looped through that I did loop through the cities data and checked to see if the county_id in the cities table matched the id in the counties table.

Model:

public function countiesWithCities() {
    $this->db->select("*");
    $this->db->from("counties");
    $this->db->where('number_of_cities >', 0);
    $this->db->order_by("id", "asc");
    $query = $this->db->get();
    return $query->result();
}

public function getCities() {
    $this->db->select("*");
    $this->db->from("cities");
    $this->db->order_by("county_id", "asc");
    $query = $this->db->get();
    return $query->result();
}

Controller:

    $data['counties'] = $this->index_model->countiesWithCities();
    $data['cities'] = $this->index_model->getCities();


    //The page "home" does exist, so load the views in the following order
    $this->load->view('templates/header', $data);
    $this->load->view('templates/indexmain', $data);
    $this->load->view('templates/footer');

View:

<?php 
foreach ($counties as $county)  {

    echo "<strong>".$county->county_name."</strong> <br>";

    foreach($cities as $city) {
        if($city->county_id == $county->id) {
            echo $city->city_name."<br>";
        }
    }
}
?>

From here I can style it and organize it on the page. Thank you so much for the help everybody. I hope this can help someone else with my problem. Also if anybody has a more efficient way of doing this I'd be happy to hear it!