Getting Value from a Secondary Table

Getting Value from a Secondary Table - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: Getting Value from a Secondary Table (/showthread.php?tid=60804)

Getting Value from a Secondary Table - El Forum - 07-02-2014

[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'.

Quote: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):

Quote:| 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.

Getting Value from a Secondary Table - El Forum - 07-02-2014

[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.

Getting Value from a Secondary Table - El Forum - 07-02-2014

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

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

Getting Value from a Secondary Table - El Forum - 07-03-2014

[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;

  }