getting data from db give wrong result when echo $row->id....

[eluser]digitaldivaradio[/eluser]
I have 2 questions... this is a user modification page that I am working on. I have a model that gets the user information with a couple joins.

1. I am trying to display their user id, the primary key on the users table. I get '1' in output on every record. It doesn't happen on the pages where I don't need a join. all tables use 'id' to name the primary key. How do I specify that I am talking about the users.id and not some other id?

2. Dropdowns - I have a couple dropdowns that are being populated by their own database model. How do I get the dropdown to default to the users record? Right now they load perfectly, but they are always on option 1. I want to have it be on option 3 or 4 if that's what is stored in the database.

MODEL:

Code:

function get_user_detail() {
        $userid = $this->uri->segment(3);
        $this->db->select('*');
        $this->db->from('users');
        $this->db->where('users.id', $userid);
        $this->db->join('user_occupation', 'users.occupation = user_occupation.id', 'inner');
        $this->db->join('location_country', 'users.country = location_country.id', 'inner');
        $this->db->join('user_types', 'users.user_type_id = user_types.id', 'inner');
        $this->db->join('sales_reps', 'users.sales_rep_id = sales_reps.id', 'inner');
        
        $q = $this->db->get();
        
        if($q->num_rows() > 0) {
            foreach($q->result() as $row) {
                $data[] = $row;
            }
        return $data;
        }
    }

CONTROLLER:

Code:

function user_mod() // page
    {
        $this->load->model('admin_users_model');
        $data['records'] = $this->admin_users_model->get_user_detail();
        
        // get the dropdown data from the database
        $this->load->model('dropdowns_model');
        $data['country_data'] = $this->dropdowns_model->get_country_dropdown();
        $data['occupation_data'] = $this->dropdowns_model->get_occupations_dropdown();
        $data['sales_rep_data'] = $this->dropdowns_model->get_sales_rep_dropdown();
        $data['states_data'] = $this->dropdowns_model->get_state_dropdown();
        $data['user_type_data'] = $this->dropdowns_model->get_user_types_dropdown();
        
        $data['page_title'] = 'Admin User Modification, Design Center - Artistic Paver Mfg. ';
        $data['main_content'] = 'admin_area/user_mod';
        $this->load->view('inc/admin/template', $data);
    }

VIEW:

Code:

<?php if(isset($records)) : foreach($records as $row) : ?>

<h1>User Account Information - &lt;?php echo $row->first_name; ?&gt; &lt;?php echo $row->last_name; ?&gt;</h1>
<p> &lt;?php echo anchor('admin/modify_user/'.$row->id, 'edit user information');?&gt;</p>
<div class="dc_user_detail">
    <table>
        <tr>
            <td>User ID:</td>
            <td>&lt;?php echo $row->id; ?&gt;</td>
        </tr>
        <tr>
            <td>&lt;?php echo form_label('Occupation:', 'occupation', ''); ?&gt;</td>
            <td>&lt;?php echo form_dropdown('occupation', $occupation_data, '$row->occupation_title'); ?&gt;</td>
        </tr>
...

[eluser]WanWizard[/eluser]
You do a SELECT * in your query. All your tables contain a field called 'id'. Which of these 'id' fields makes it to the result (where 'id' has to be unique)?

[eluser]digitaldivaradio[/eluser]
Yes, that is exactly what's happening. I believe its the sales_rep.id that making it through. How do I work around this beside renaming all my tables id's to something else?

I tried row->users.id. No go...

I tried earlier pulling out only some data like sooooo

Code:

$this->db->select('users.id', 'users.first_name', 'users.last_name', 'users.occupation' ');

and then I get an error when I try to echo out the $row->occupation_title (occupation title is from the occupation table, not users) with the above select.

[eluser]InsiteFX[/eluser]

Code:

$data[$row->id]['id'] = $row->id;

InsiteFX

[eluser]digitaldivaradio[/eluser]
I tried using $data[$row->id]['id'] = $row->id; That gives me the same id as before, the id of a table I joined. not of the table I am selecting, users.

i want the users id (which is unique on each user, its the key).

$row->id is coming out as the id of the sales_rep table. so assigning another variable equal to $row->id gives me the same result.

what am I doing wrong?

[eluser]InsiteFX[/eluser]

Code:

$data[$row->id]['userid'] = $row->user_id;

InsiteFX

[eluser]WanWizard[/eluser]
[quote author="digitaldivaradio" date="1282106876"]

Code:

$this->db->select('users.id', 'users.first_name', 'users.last_name', 'users.occupation' ');

and then I get an error when I try to echo out the $row->occupation_title (occupation title is from the occupation table, not users) with the above select.[/quote]

Which is quite logical, you haven't included a field called 'occupation_title' in your select.

You can use

Code:

$this->db->select('users.id AS row_id');

which will create $row->row_id.

[eluser]mddd[/eluser]
Selecting * from multiple tables gives the risk of duplicate fields. I think that in such a case the last field of that name 'makes it'.
You need to find out which fields you REALLY need. If you need all fields of one table, and only some (like an id) of the others, it is better to do something like

Code:

SELECT users.*, user_occupation.name

This gives you all the fields of 'users' and only the 'name' field of user_occupation. If 'users' also has a 'name' field, use an alias:

Code:

SELECT users.*, user_occupation.name AS occupation_name

The same goes for id's, of course.