Small problem with CodeIgniter

PHP
1

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()
	{
		parent::__construct();
		
		/* Standard Libraries */
		$this->load->database();
		/* ------------------ */
		$this->load->model('app_model');
		$this->load->helper('url'); //Just for the examples, this is not required thought for the library
		
		
	}
	
	function index()
	{
		//$this->db->select('*');
		//$this->db->from('contacts');
		//$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)

    {
        $this->load->view('app_view.php',$output);
    }
	
}

My Model

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

class App_model extends CI_Model {
	
	function __construct() {
		parent::__construct();
		$this->load->database();	
	}
	
	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;?>
          <?php
				}
			} ?>

Thanks.

2

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.

3

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.