making query from different related tables using codeigniter
- by fatemeh karam
I'm using codeigniter as i mentioned
this is a part of my view code
foreach($projects_query as $row)// $row indicates the projects
{ ?>
<tr><td><h3><button type="submit" class="button red-gradient glossy" name = "project_click" >
<?php echo $row->txtTaskName; ?></button></h3></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td></tr>
<?php
foreach($tasks_query as $row2)
{
// if( $row->txtTaskName == "TestProject")
if($row->intTaskID == $row2->intInside)// intInside indicades that the current task($row2) is the subset of which task (system , subsystem or project)
{
if($row2->intSummary == 0)//if the task(the system) is an executable task & doesn't have any subtask:
{
$query_team_user_id = $this->admin_in_out_model->get_user_team_task_query($row2->intTaskID);//runs the function and generates a query from tbl_userteamtask where intTaskID equals to the selected row's intTaskID
foreach($query_team_user_id as $row_teamid)
{
$query_teamname = $this->admin_in_out_model->get_team_name($row_teamid->intTeamID);
$query_fn_ln = $this->admin_in_out_model->get_fn_ln_from_userid($row_teamid->intUserID);
foreach($query_teamname as $row_teamname)
{?>
<tr><td></td><td></td><td><h4> <?php echo $row2->txtTaskName;?></h4></td>
<td><b><font color='#F33558'><?php echo $row_teamname->txtTeamName;?></font></b></td>
<?php }
foreach($query_fn_ln as $row_f_l_name)
{?>
<td> <?php echo $row_f_l_name->txtFirstname." ".$row_f_l_name->txtLastname;?></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td>
<?php }?>
</tr>
<?php }
}
else{ ?> <tr><td></td><td></td><td><h4> <?php echo $row2->txtTaskName;?></h4></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td></tr><?php }
foreach($tasks_query as $row_subsystems)
{
if($row_subsystems->intInside == $row2->intTaskID )//if the task is the subtask of a system(it means the task is a subsystem)
{
if($row_subsystems->intSummary == 0)//if the task is an executable task & doesn't have any subtask:
{
$query_team_user_id = $this->admin_in_out_model->get_user_team_task_query($row_subsystems->intTaskID);
foreach($query_team_user_id as $row_teamid)
{?>
<tr><?php
$query_teamname = $this->admin_in_out_model->get_team_name($row_teamid->intTeamID);
$query_fn_ln = $this->admin_in_out_model->get_fn_ln_from_userid($row_teamid->intUserID);
foreach($query_teamname as $row_teamname)
{?>
<td></td><td></td><td><h5><?php echo $row_subsystems->txtTaskName?></h5><br/></td>
<td><b><font color='#F33558'><?php echo $row_teamname->txtTeamName;?></font></b></td><?php
}
foreach($query_fn_ln as $row_f_l_name)
{?>
<td><?php echo $row_f_l_name->txtFirstname." ".$row_f_l_name->txtLastname;?></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><?php
}?>
</tr><?php
}
}
else{ ?><tr><td></td><td></td><td><h5><?php echo $row_subsystems->txtTaskName?></h5></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td></tr><?php }
foreach($tasks_query as $row_tasks)
{
if($row_tasks->intInside == $row_subsystems->intTaskID )//if the task is the subtask of a subsystem
{
if($row_tasks->intSummary == 0)//if the task is an executable task & doesn't have any subtask:
{
$query_team_user_id = $this->admin_in_out_model->get_user_team_task_query($row_tasks->intTaskID);
foreach($query_team_user_id as $row_teamid)
{?>
<tr><?php
$query_teamname = $this->admin_in_out_model->get_team_name($row_teamid->intTeamID);
$query_fn_ln = $this->admin_in_out_model->get_fn_ln_from_userid($row_teamid->intUserID);
foreach($query_teamname as $row_teamname)
{?>
<td></td><td></td><td><b><?php echo $row_tasks->txtTaskName;?></b></td>
<td><b><font color='#F33558'><?php echo $row_teamname->txtTeamName;?></font></b></td><?php
}
foreach($query_fn_ln as $row_f_l_name)
{?>
<td><?php echo $row_f_l_name->txtFirstname." ".$row_f_l_name->txtLastname;?></td><td></td><td></td><td></td><td></td><td></td><td></td><td></td><?php
}?>
</tr><?php
}
}
}
}
}
}
}
}
}?>
and in controller i have
$projects_query = $this->admin_in_out_model->get_projects();
$tasks_query = $this->admin_in_out_model->get_systems();
$userteamtask = $this->admin_in_out_model->get_user_team_task();
$data['tasks_query'] = $tasks_query;
$data['projects_query'] = $projects_query;
$this->load->view('project_view',$data);
but as you see I'm calling my model functions within the view
how can i do something else to do this i mean not calling my model function in my view
I have to add that, my model function have parameters
these are the model functions:
function get_projects()
{
$this -> db -> select('*');
$this -> db -> from('tbl_task');
$this -> db -> where('intInside','0');
$query = $this->db->get();
return $query->result();
}
function get_systems()
{
$this -> db -> select('*');
$this -> db -> from('tbl_task ');
$this -> db -> where('intInside <> ','0');
$query = $this->db->get();
return $query->result();
}
function get_user_team_task_query($task_id)//gets information from tbl_userteamtask where the field intTaskID is equal to $task_id
{
$this -> db -> select('*');
$this -> db -> from('tbl_userteamtask');
$this -> db -> where('intTaskID',$task_id);
$query_teamid = $this->db->get();
return $query_teamid->result();
}
function get_user_team_task()//gets information from tbl_userteamtask where the field intTaskID is equal to $task_id
{
$this -> db -> select('*');
$this -> db -> from('tbl_userteamtask');
// $this -> db -> where('intTaskID',$task_id);
$query_teamid = $this->db->get();
return $query_teamid->result();
}
function get_team_name($query_teamid)
{
$this -> db -> select('*');
$this -> db -> from('tbl_team');
$this -> db -> where('intTeamID',$query_teamid);
$query_teamname = $this->db->get();
return $query_teamname->result();
}
function get_user_name($query_userid)
{
$this -> db -> select('*');
$this -> db -> from('tbl_user');
$this -> db -> where('intUserID',$query_userid);
$query_username = $this->db->get();
return $query_username->result();
}
function get_fn_ln_from_userid($selected_id)
{
$this -> db -> select('tbl_user.intUserID, tbl_user.intPersonID,tbl_person.intPersonID,tbl_person.txtFirstname, tbl_person.txtLastname');
$this -> db -> from('tbl_user , tbl_person');
$where = "tbl_user.intPersonID = tbl_person.intPersonID ";
$this -> db -> where($where);
$this -> db -> where('tbl_user.intUserID', $selected_id);
$query = $this -> db -> get();//makes query from DB
return $query->result();
}
do I have to use subquery ?
is this true? i mean can i do this?
foreach( $data as $key => $each )
{
$data[$key]['team_id'] = $this->get_user_team_task_query( $each['intTaskID'] );
foreach($data[$key]['team_id'] as $key_teamname => $each)
{
$data[$key_teamname]['team_name'] = $this->get_team_name( $each['intTeamID'] );
}
}
the model code:
foreach( $data as $key => $each )
{
$data[$key]['intTaskID'] = $each['intTaskID'];
$data[$key]['team_id'] = $this->get_user_team_task_query( $each['intTaskID'] );
foreach($data[$key]['team_id'] as $key => $each)
{
$data[$key]['team_name'] = $this->get_team_name( $each['intTeamID'] ); #fetching of the teamname and saving in the array
$data[$key]['user_name'] = $this->get_fn_ln_from_userid( $each['intUserID'] );
foreach($data[$key]['user_name'] as $key => $each)
{
$data[$key]['first_name'] = $each['txtFirstname'] ;
$data[$key]['last_name'] = $each['txtLastname'] ;
}
$data[$key]['first_name'] = $data[$key]['first_name'];
$data[$key]['last_name'] = $data[$key]['last_name'];
}
}
