CODEWITHSUNDEEP
phpmysqlcodeigniter
j0nr
j0nr

Reputation: 301

Codeigniter - Getting Value from a Secondary Table

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

My solution

May not be perfect but it works:

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

Not sure about the bestway to do the last few lines. This is in my model, so I needed to return the array. I am going tobet there is a way to do it better than the last 3 lines.

Upvotes: 1

Views: 70

Answers (1)

M Khalid Junaid
M Khalid Junaid

Reputation: 64476

You can use joins and select title from your exercise_list table

$this->db->select('w.*,el.title')
    ->from('workouts w')
    ->join('exercises e','w.id = e.wo_id')
    ->join('exercise_list el','el.id = e.ex_id')
    ->where('e.wo_id',$yourid)
    ->get()
    ->result();

Upvotes: 1

Related Questions