SitePoint Sponsor

User Tag List

Results 1 to 3 of 3

Hybrid View

  1. #1
    SitePoint Member
    Join Date
    Oct 2012
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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 Code:
    <?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 Code:
    <?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 Code:
    <?php if($query) { 
                    foreach (
    $query as $contact) { ?>
                        <h4><?php echo $contact->firstName;?> (<?php echo $contact->lastName;?>)</h4>
              <?php echo $contact->cityName;?>
              <?php
                    
    }
                } 
    ?>
    Thanks.

  2. #2
    SitePoint Member
    Join Date
    Oct 2012
    Posts
    3
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #3
    SitePoint Member
    Join Date
    Jul 2012
    Location
    Minnesota
    Posts
    11
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

    Quote Originally Posted by UFO Disko View Post
    Looks like I have a problem with my query



    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.


Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •