Problem with SQL JOIN and CI query results

[eluser]dandys997[/eluser]
Hi!
I've got a three tables in MySQL database, for example:
- movie,
- user,
- category.
I need to join them, so I use a model's method with this code

Code:

$this->db->order_by("date");
$this->db->join("category", "category.id_category=movie.id_movie");
$this->db->join("user", "user.id_user=movie.id_movie");
$query=$this->db->get("movie", 10);

As a result I get an object: $query. Now I can easily access the query's data, for example:

Code:

$row=$query->row();
$row->field_name;

But there's a one problem: tables "category" and "user" have one the same named field: "name". How could I access field from above query with selecting source table? This code:

Code:

$row->category.name

doesn't work.

Thanks in advance for help!

[eluser]Media Gearhead[/eluser]
You will need to add a select to the query... Like so.

Code:

$this->db->select('movie.name as moviename,user.name as username,category.name as categoryname,movie.*,user.*,category.*);
$this->db->order_by("date");
$this->db->join("category", "category.id_category=movie.id_movie");
$this->db->join("user", "user.id_user=movie.id_movie");
$query=$this->db->get("movie", 10);

The as will allow you to set it to what you need it to be and you would be able to reference them as
$row->moviename
$row->username
$row->categoryname

Hope this helps.

[eluser]dandys997[/eluser]
Thanks a lot! It works.