I have two tables namely tbl_tools and 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();
}
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
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();
}