Missing column data in join clause

[eluser]nofearinc[/eluser]
I have a select clause with 2 join statements which looks like this:

Code:

$this->CI->db->where('username', $user);
        $this->CI->db->join('user_groups', 'users.user_id = user_groups.user_id', 'left');
        $this->CI->db->join('groups', 'groups.group_id = user_groups.group_id', 'left');
        
        $query = $this->CI->db->get('users');

I am able to extract most of the columns from the result set, but I get empty value for the user_id column. I suspect that the problem could be in ambiguous column names during join. Tried to set aliases to joined columns, but no success. Also compiled the SQL code and ran into the database, it gets all the data.

[eluser]jedd[/eluser]
Use the Profiler / Benchmark class to identify the actual SQL being passed (this is described in the FAQ and the User Guide).

Use the AS feature of MySQL to rename your various table_name.id fields to something unique (this is described in the FAQ and the MySQL manual).

[eluser]nofearinc[/eluser]
I've used the compile_select() method of database class and received a normal SQL statement:

Code:

SELECT * FROM (`users`)
LEFT JOIN `zonesci_user_groups` as z_ug ON `users`.`user_id`= `z_ug`.`user_id`
LEFT JOIN `zonesci_groups` as z_g ON `z_g`.`group_id` = `z_ug`.`group_id`
WHERE `email` = '[email protected]'

Executing this code in my database client extracts all the data including the column I need.

The only suspicious fact is that after the join I have user_id and user_id_1 columns, but reading user_id is unsuccessful.

[eluser]nofearinc[/eluser]
I suppose it's due to ambiguity. I added a select() with required fields above the other join clauses:

Code:

$this->CI->db->select("users.user_id as user_id, username, password, group_name");

I'm able to extract the user_id now. The problem is that I have unpredictable number of fields in both tables so I need the 'select *' statement extracted. Is it possible to do "select users.user_id, *" or something like this in the statement in order to properly retrieve the ID?

[eluser]saidai jagan[/eluser]
use DISTINCT(user_id)

[eluser]nofearinc[/eluser]
DISTINCT doesn't help me to select all columns and get properly auto-generated user_id name...

[eluser]jedd[/eluser]
I don't use AR. (ObDisclaimer)

Those that do will have a better chance of assisting if you can provide the raw SQL that does precisely what you want to do.

Remember that you're not obliged to use AR - you can use the standard ->query functions with some raw SQL and either escape fields directly, or via binding, to give you comparable levels of protection from SQL injection attacks.

I don't know what you mean regarding 'extraction' of a select statement. If you have an unpredictable number of fields in your tables, then a) normalise your schema, or b) start doing funny stuff with the [url="/user_guide/database/fields.html"]meta-database functions[/url]. (My preference would be to normalise / fix the schema.)

[eluser]nofearinc[/eluser]
@jedd,

Thanks for your comment. The SQL from the comment above is the correct SQL that runs flawlessly in my phpMyAdmin or HeidiSQL client. user_id column has values and everything is OK. When I try the active record pattern that generates the same SQL, I just retrieve null for my user_id column. That's what I find weird.

Currently I'm selecting only 4 columns (instead of select *) and user_id is OK, but it is no solution for my problem.

As for the pattern, I'm used to ActiveRecord and Object Relational Mapping and I prefer using them instead of writing plain SQL. When needed, I patch with SQL queries.

Speaking about the 'select extraction' - this is a code snippet from a library that uses different 'users' tables so I have no information about the table structure. User passes table name, username and password - all other fields are hidden (no reflection is applied). That's why I need 'select all' and I can't check all the fields statically without knowing about them.