Fetching values stored as an array

Question

I have two tables namely tbl_toolsand tbl_tool_use. tbl_tool_use table looks like this

id  user_id     type    tool_id     quantity    start_date  end_date
30  27         engineer     5          2        2016-12-22  
31  gdf         team        8          2        2016-12-22  
32  26         engineer     7          2        2016-12-22  
33  26         engineer     7          2        2016-12-23  
34  hamsu       team        6          2        2016-12-22  
35  27        engineer      7,5        2,2      2016-12-22

tbl_tools table looks like this

id  name            quantity    available   type        
5   cutting player     5          5        engineer     
6   reflectors         2          2        team     
7   spanner            8          8        engineer     
8   tester             4          4        team

I want my result to be like this:

id  user_id     type       tool_id             quantity     start_date  end_date
30  27         engineer  cutting player            2        2016-12-22  
31  gdf         team     tester                    2        2016-12-22  
32  26         engineer  spanner                   2        2016-12-22  
33  26         engineer  spanner                   2        2016-12-23  
34  hamsu       team     reflectors                2        2016-12-22  
35  27        engineer   cutting player,spanner    2,2      2016-12-22

but am getting like this

 id user_id     type       tool_id             quantity     start_date  end_date
30  27         engineer  cutting player            2        2016-12-22  
31  gdf         team     tester                    2        2016-12-22  
32  26         engineer  spanner                   2        2016-12-22  
33  26         engineer  spanner                   2        2016-12-23  
34  hamsu       team     reflectors                2        2016-12-22  
35  27        engineer   cutting player            2,2      2016-12-22

if i have selected more tool_ids then also only one value is showing. here is the code i used,my view looks like this,i had shown only the affected parts in the code

<tbody>
    <?php $n=1;

        foreach($all_assign_tool_info as $row) {                              
            $t=explode(',',$row->tool_id);
            foreach($tools as $res) {
                foreach($t as $res1) {
                    if($res1==$res->id) {
                        $tool=$res->name;
                        //var_dump($tool);
                    }
                }
            }
        }                   

    ?>
    <tr>
        <td><?= $tool ?></td>
    </tr>
    <?php
    }?>
</tbody>

this is my controller

public function assign_tool($id = NULL)
{

    $data['all_assign_tool_info'] = $this->Tool_model->get_permission('tbl_tool_use');
    $data['tools']=$this->Tool_model->view_tools(null,null);
    $data['subview'] = $this->load->view('admin/tool/assign_tool',$data, TRUE);
    $this->load->view('admin/_layout_main', $data); //page load
}

my model looks like this

public function view_tools($limit,$offset)
{
    $this->db->order_by('id','desc');
    $query=$this->db->get('tbl_tools',$limit,$offset);
    return $query->result();
}

please help me to solve my problem

here is the newly included code my tbl_tool_use table looks like this

id  user_id     type       tool_id     quantity    start_date  end_date
136  27         engineer    11,5,7      3,5,2      2016-12-22

that means tool_id 11 is 3,5 is 5 and 7 is 2 respectively but the result am getting like this 5 is 3, 7 is 5 and 11 is 2 the result obtained is like as follows Valid XHTML http://spdc.in/demo/spectra/spectra_tool.PNG.

you shouldn't be doing this http://stackoverflow.com/a/41215681/267540 — e4c5, Dec 23 '16 at 05:46
you have printed your = $tool ?> outside of the foreach loop. so only one value is showing — prakash tank, Dec 23 '16 at 05:47
THREE foreach with three different variables? which one contains table data? print that variable and show us some amount of data — Alive to die - Anant, Dec 23 '16 at 05:54
@ Anant `foreach($all_assign_tool_info as $row)` contains the table data of `tbl_tool_use` and this one `foreach($tools as $res)` contains the table data `tbl_tools` — user_777, Dec 23 '16 at 06:01
the logic is simple nothing but if the `tool_id` of `tbl_tool_use` is same as `id` of `tbl_tools` the output should come that means the name of the tool should be visible its correct if the tool_id contains only one value but not stored as an array — user_777, Dec 23 '16 at 06:06

score 2 · Accepted Answer · answered Dec 23 '16 at 06:53

2

I would do away with iterations in PHP and focus more on writing the correct SQL syntax to obtain your results. Look into FIND_IN_SET and JOINS to make this work. Essentially, what you want is to join the two tables on the comma separated values. I would rewrite your Codeigniter model in this manner:

public function view_tools($limit,$offset)
{
    $this->db->select('tu.*, GROUP_CONCAT(t.name ORDER BY t.id) as tool_id', FALSE);
    $this->db->from('tbl_tools t');
    $this->db->join('tbl_tools_use tu', 'FIND_IN_SET(t.id, tu.tool_id)', 'inner', FALSE);
    $this->db->group_by('tu.id');
    $this->db->order_by('tu.id','desc');
    $this->db->limit($limit);
    $this->db->offset($offset);
    $query = $this->db->get();
    return $query->result();
}

answered Dec 23 '16 at 06:53

CodeGodie

12,116
6
37
66

thanks a lot,its working and i have a doubt can i get result from my previous code written – user_777 Dec 23 '16 at 08:13
what do you mean by "_in the order of table_"? – CodeGodie Dec 28 '16 at 12:29
if the order of `tool_id` is 11,7,5 the result am getting is in order of 5,7,11 – user_777 Dec 29 '16 at 05:05
i had added the detail in question please have a look – user_777 Dec 29 '16 at 05:14
so if the order of `tbl_tool_use -> tool_id`is 11,7,5, then your results should 11,7,5 ? In other words you want the order to follow the order on the column? – CodeGodie Dec 29 '16 at 15:02

Fetching values stored as an array

1 Answers1