[eluser]rubberglover[/eluser]
I'm new to active record and codeigniter so please bare with me!
I have two tables; videos and annotation_sets, with the following columns:
VIDEOS:
id
title
file
ANNOTATION_SETS
id
vid_id
title
content
Each annotation_set entry is linked to an entry in the videos table via the 'vid_id' field.
I want to list the annotation_set entries and also extract the linked video title. I am using a JOIN function in an active record statement to join the two tables like so:
Code:
$this->db->order_by('annotation_sets.title', 'asc');
$this->db->select('annotation_sets.id,
annotation_sets.vid_id,
annotation_sets.title');
$this->db->select('videos.title');
$this->db->join('videos', 'videos.id = annotation_sets.vid_id');
Problem is they share the same column name 'title' so, when the two tables are joined, the 'annotation_sets' title field is overwritten by the 'videos' title field. I didn't write the DB structure, but if I did I would have prefixed the column names which would solve the prob, hence I've never ran into this issue before. I'm pretty new to active record, so is there a way to join the title field from the videos table but to rename it within the query, to say 'v_title'? Here's the array results I am getting, and what I would want the resulting array to look like:
Code:
// array structure that am getting from my query...
[0] => Array
(
[id] => ...
[vid_id] => ...
[title] => ... //this should be the 'annotation_sets.title' field, but it's being overwritten by the joined 'videos.title' field. I want both titles...
)
// array structure that I would like...
[0] => Array
(
[id] => ...
[vid_id] => ...
[title] => ... //extracted from 'annotation_sets.title'
[v_title] => ... //extracted from 'videos.title'
)
I could do it via separate queries of course but I'd just like to know if this is possible.
Many thanks!
