Database results returning same id

[eluser]Snaver[/eluser]
Hi there, having a problem returning results from one of my tables. I can retreive what looks like a pretty complete set of data, however all the ids in the array are the same.. which is odd! Example code can be seen bellow:

SQL

Code:

SELECT *
FROM (`files`)
JOIN `files_entry` ON `files_entry`.`files_id` = `files`.`id`
JOIN `users` ON `users`.`id` = `files_entry`.`users_id`
WHERE `games_id` = 2

Code

Code:

$this->db->select('*');
        $this->db->from('files');
        $this->db->where('games_id', $type);
        $this->db->join('files_entry', 'files_entry.files_id = files.id');
        $this->db->join('users', 'users.id = files_entry.users_id');

        $query = $this->db->get();

        if ($query->num_rows() > 0)
        {
            foreach ($query->result() as $row)
            {
                // Testing
                print_r($row);
            }
        }

print_r results (some information ommited):

Code:

stdClass Object
(
    [id] => 1
    [title] => --
    [author] => --
    [description] => blahsdfh
    [release_date] => 2010-04-04
    [download_count] => 0
    [hm_download_count] => 0
    [type_id] => 1
    [games_id] => 2
    [added_date] => 2010-04-04 17:15:14
    [ip_address] => --
    [user_agent] => --
    [files_id] => 3
    [users_id] => 1
    [username] => Snaver
    [email] => --
    [password] => a58243a1a39f27b33ba7e6a492c233d8ed1846ee
    [group_id] => 1
    [token] => F8CD55E028E6628DEC4D
    [identifier] => eb260c3bae08cbfa4ca9d2da41e2b476ef12f88e
)
stdClass Object
(
    [id] => 1
    [title] => Another Test
    [author] => authorzz
    [description] => desccccc
    [release_date] => 0000-00-00
    [download_count] => 0
    [hm_download_count] => 0
    [type_id] => 1
    [games_id] => 2
    [added_date] => 2010-04-06 16:46:06
    [ip_address] => --
    [user_agent] => --
    [files_id] => 5
    [users_id] => 1
    [username] => --
    [email] => --
    [password] => a58243a1a39f27b33ba7e6a492c233d8ed1846ee
    [group_id] => 1
    [token] => F8CD55E028E6628DEC4D
    [identifier] => eb260c3bae08cbfa4ca9d2da41e2b476ef12f88e
)

[eluser]danmontgomery[/eluser]
When you SELECT * and join tables, it selects all columns from all tables. Assuming all of the tables have an ID column, when that result set is translated to PHP, each ID column will overwrite the previous one. If you only need the data from table, you should specify that table:

Code:

SELECT files.*

If you need the data from other tables as well, you can just alias those ID fields

Code:

SELECT *, files.id AS file_id, users.id AS user_id

[eluser]JoostV[/eluser]
Judging from your query, youre probably looking at users.id here.

[eluser]Snaver[/eluser]
Spot on guys, I had the primary key named 'id' in all of my tables so there's a lesson in database design!

Thanks again guys!