mysql - CI: Querying two tables in the model, explode -

i'm thinking days , don't come grasps (since i'm relativley new mvc , ci). i'm not sure whether issue mvc, mysql or arrays.

situation: 2 mysql tables

1. table data: id, title, list
2. table values: id, name

querying data table results in array following (excerpt):

[4] => array         (             [id] => 3             [title] => foo                             [list] => 1,2,3,4,6,14         )  [5] => array         (             [id] => 4             [title] => bar                             [list] => 2,6,9,12         ) 

the field list contains comma separated values correspond ids of values table like

[3] => array             (                 [id] => 12                 [name] => 'value12'                             ) 

what try each row is:

• take list-values & explode array
• check result set values-table (via in_array() method)
• return name values of ids if

• include somehow main result set (e.g. 2-dimensional array):

  [5] => array ( [id] => 4 [title] => bar [list] => array ( [0] => value6 [1] => value12 ... ) )

my naive approach far

• run query on each of 2 tables
• compare 2 result sets via in_array

my main problem (while trying strictly separate model, controller , view): how can include name field values-table in "main loop" of data table result set?

if($q->num_rows() > 0) {     $data[] = $q->result_array();     foreach ($q->result() $row)     {         $data[] = $row;     }     return $data; } 

if use following (cumbersome) approach naturally new item each time:

foreach ($q->result_array() $row)  {     $data[]['id'] = $row['id'];     $data[]['title'] = $row['title'];     $data[]['list'] = $row['year']; } 

since mysql database see no way explode , comparison in sql (with or else).

any hint, simple link info bit, highly appreciated.

thanks trillion!

fab

there many-to-many relationship between lists , list values. conventional way model in relational database create joining table. i'd structure schema this.

lists : list_id, title values : value_id, name list_values : list_id, value_id 

list_values joining table. links lists values.

to build list have following functions in model

function build_list($list_id) {     $list = $this->get_list($list_id);     $list->values = $this->get_list_values($list_id);     return $list; }  function get_list($list_id) {     $sql = 'select * lists list_id=?';     return $this->db->query($sql, array($list_id))->row(); }  function get_list_values($list_id) {     $sql = 'select v.value_id, v.name         list_values lv         join values v on v.value_id=lv.value_id         lv.list_id=?';     return $this->db->query($sql, array($list_id))->result(); }