Small problem with CodeIgniter

Hello everyone,

I have 2 tables:

  • contacts: contactID, firstName, lastName,…, cityID
  • city: cityID, cityName

I’ve built my controller, model and view but it’s only fetching 3 records from the table while I have 5.

This is my controller

<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');

class App extends CI_Controller {

	function __construct()
		/* Standard Libraries */
		/* ------------------ */
		$this->load->helper('url'); //Just for the examples, this is not required thought for the library
	function index()
		//$this->db->join('city', 'city.cityID = contacts.cityID');
		$data['query'] = $this->app_model->get_all_posts();
		$this->load->view('app_view', $data);
	function _example_output($output = null)


My Model

<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');

class App_model extends CI_Model {
	function __construct() {
	function get_all_posts() {
		$query = $this->db->query('SELECT * FROM contacts JOIN city ON city.cityID = contacts.contactID');
		return $query->result();	

and my view

<?php if($query) {
				foreach ($query as $contact) { ?>
					<h4><?php echo $contact->firstName;?> (<?php echo $contact->lastName;?>)</h4>
          <?php echo $contact->cityName;?>
			} ?>


Looks like I have a problem with my query

SELECT * FROM contacts JOIN city ON city.cityID = contacts.contactID

It seems that it’s only retrieving 3 records because only 3 records have their ‘cityID’ field match the other ‘cityID’ field in ‘city’ table.
That’s not what I want exactly, I want to show all the records and just link those 2 fields together to be able to assign a ‘cityName’ to every contact.

What you want to do in this case is switch your query to use left join. This should return all records from your contacts table. So, i would adjust your query to be:

SELECT * FROM contacts LEFT JOIN city ON city.cityID = contacts.cityID

You mentioned in your post that both tables had a cityID, but in the example you gave you were joining against contactID which confused me. I changed the join based on the assumption that there is in fact a cityID column on both tables as you stated.

See how that works for you. Hope it helps.