Getting Value from a Secondary Table

[eluser]Unknown[/eluser]
Hi all,

This is my first foray into CI so please bare with me. I'm not the most experienced PHPer either, but have been dabbling for a while.

i am setting up an app which is a log basically for recording weight lifting workouts.

I have 3 tables in my DB: 'workouts', 'exercises' and 'exercise_list'.

workouts:
| id | datetime | misc1 | misc2 |

exercises:
| id | ex_id | wo_id | weight | reps | wo_order |

exercise_list:
| id | title |

So far I have generated a view which grabs details of a specific workout (myurl.com/workouts/view/<datetime>)
I have built a query that grabs the fields from 'workouts' and also it grabs any 'exercises' entries that correspond to that
workout (by get_where using wo_id).

I build a view which lists the exercises for that workout, but I can only get as far as foreach'ing out the 'id' of the exercise. I need to somehow have a further query that grabs the 'title' of each exercise that is associated with that workout 'id'.

So I currently have a table (html):

| Exercise | Weight | Reps |
| 1 | 50 | 8 |
...

I need '1' to become the title of the exercise in 'exercise_list' with an 'id' of '1'.

Hope that makes sense.

Thanks in advance.

[eluser]joergy[/eluser]
Your question seems to be neither a CI- nor PHP-question but SQL.
Read about joins in SQL. These will give You a solution.

[eluser]www.sblog.in[/eluser]
You can do something like

Code:

SELECT * FROM exercises e, exercise_list el WHERE e.id=el.id

[eluser]Unknown[/eluser]
Hi all,

Thanks for the pointers and yes, JOIN was the answer.

This was how I did it in the end:

Code:

public function get_exercises($wo_id)
  {
    $this->db->select('exercises.wo_id,
                       exercises.weight,
                       exercises.reps,
                       exercise_list.title');
    $this->db->from('exercises');
    $this->db->join('exercise_list','exercises.ex_id= exercise_list.id');
    $this->db->where('exercises.wo_id',$wo_id);
    $q = $this->db->get();
    $query = $q->result_array();
    return $query;
  }