Databases
StackOverflow2013
Postsdbo
POmaking query from different related tables using codeigniter
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POmaking query from different related tables using codeigniter
    primarykey
    data
    text
    <p>I'm using codeigniter as i mentioned this is a part of my view code</p> <pre><code>foreach($projects_query as $row)// $row indicates the projects { ?&gt; &lt;tr&gt;&lt;td&gt;&lt;h3&gt;&lt;button type="submit" class="button red-gradient glossy" name = "project_click" &gt; &lt;?php echo $row-&gt;txtTaskName; ?&gt;&lt;/button&gt;&lt;/h3&gt;&lt;/td&gt;&lt;td&gt;&lt;/td&gt;&lt;td&gt;&lt;/td&gt;&lt;td&gt;&lt;/td&gt;&lt;td&gt;&lt;/td&gt;&lt;td&gt;&lt;/td&gt;&lt;td&gt;&lt;/td&gt;&lt;td&gt;&lt;/td&gt;&lt;td&gt;&lt;/td&gt;&lt;td&gt;&lt;/td&gt;&lt;td&gt;&lt;/td&gt;&lt;td&gt;&lt;/td&gt;&lt;/tr&gt; &lt;?php foreach($tasks_query as $row2) { // if( $row-&gt;txtTaskName == "TestProject") if($row-&gt;intTaskID == $row2-&gt;intInside)// intInside indicades that the current task($row2) is the subset of which task (system , subsystem or project) { if($row2-&gt;intSummary == 0)//if the task(the system) is an executable task &amp; doesn't have any subtask: { $query_team_user_id = $this-&gt;admin_in_out_model-&gt;get_user_team_task_query($row2-&gt;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-&gt;admin_in_out_model-&gt;get_team_name($row_teamid-&gt;intTeamID); $query_fn_ln = $this-&gt;admin_in_out_model-&gt;get_fn_ln_from_userid($row_teamid-&gt;intUserID); foreach($query_teamname as $row_teamname) {?&gt; &lt;tr&gt;&lt;td&gt;&lt;/td&gt;&lt;td&gt;&lt;/td&gt;&lt;td&gt;&lt;h4&gt; &lt;?php echo $row2-&gt;txtTaskName;?&gt;&lt;/h4&gt;&lt;/td&gt; &lt;td&gt;&lt;b&gt;&lt;font color='#F33558'&gt;&lt;?php echo $row_teamname-&gt;txtTeamName;?&gt;&lt;/font&gt;&lt;/b&gt;&lt;/td&gt; &lt;?php } foreach($query_fn_ln as $row_f_l_name) {?&gt; &lt;td&gt; &lt;?php echo $row_f_l_name-&gt;txtFirstname." ".$row_f_l_name-&gt;txtLastname;?&gt;&lt;/td&gt;&lt;td&gt;&lt;/td&gt;&lt;td&gt;&lt;/td&gt;&lt;td&gt;&lt;/td&gt;&lt;td&gt;&lt;/td&gt;&lt;td&gt;&lt;/td&gt;&lt;td&gt;&lt;/td&gt;&lt;td&gt;&lt;/td&gt; &lt;?php }?&gt; &lt;/tr&gt; &lt;?php } } else{ ?&gt; &lt;tr&gt;&lt;td&gt;&lt;/td&gt;&lt;td&gt;&lt;/td&gt;&lt;td&gt;&lt;h4&gt; &lt;?php echo $row2-&gt;txtTaskName;?&gt;&lt;/h4&gt;&lt;/td&gt;&lt;td&gt;&lt;/td&gt;&lt;td&gt;&lt;/td&gt;&lt;td&gt;&lt;/td&gt;&lt;td&gt;&lt;/td&gt;&lt;td&gt;&lt;/td&gt;&lt;td&gt;&lt;/td&gt;&lt;td&gt;&lt;/td&gt;&lt;td&gt;&lt;/td&gt;&lt;td&gt;&lt;/td&gt;&lt;/tr&gt;&lt;?php } foreach($tasks_query as $row_subsystems) { if($row_subsystems-&gt;intInside == $row2-&gt;intTaskID )//if the task is the subtask of a system(it means the task is a subsystem) { if($row_subsystems-&gt;intSummary == 0)//if the task is an executable task &amp; doesn't have any subtask: { $query_team_user_id = $this-&gt;admin_in_out_model-&gt;get_user_team_task_query($row_subsystems-&gt;intTaskID); foreach($query_team_user_id as $row_teamid) {?&gt; &lt;tr&gt;&lt;?php $query_teamname = $this-&gt;admin_in_out_model-&gt;get_team_name($row_teamid-&gt;intTeamID); $query_fn_ln = $this-&gt;admin_in_out_model-&gt;get_fn_ln_from_userid($row_teamid-&gt;intUserID); foreach($query_teamname as $row_teamname) {?&gt; &lt;td&gt;&lt;/td&gt;&lt;td&gt;&lt;/td&gt;&lt;td&gt;&lt;h5&gt;&lt;?php echo $row_subsystems-&gt;txtTaskName?&gt;&lt;/h5&gt;&lt;br/&gt;&lt;/td&gt; &lt;td&gt;&lt;b&gt;&lt;font color='#F33558'&gt;&lt;?php echo $row_teamname-&gt;txtTeamName;?&gt;&lt;/font&gt;&lt;/b&gt;&lt;/td&gt;&lt;?php } foreach($query_fn_ln as $row_f_l_name) {?&gt; &lt;td&gt;&lt;?php echo $row_f_l_name-&gt;txtFirstname." ".$row_f_l_name-&gt;txtLastname;?&gt;&lt;/td&gt;&lt;td&gt;&lt;/td&gt;&lt;td&gt;&lt;/td&gt;&lt;td&gt;&lt;/td&gt;&lt;td&gt;&lt;/td&gt;&lt;td&gt;&lt;/td&gt;&lt;td&gt;&lt;/td&gt;&lt;td&gt;&lt;/td&gt;&lt;?php }?&gt; &lt;/tr&gt;&lt;?php } } else{ ?&gt;&lt;tr&gt;&lt;td&gt;&lt;/td&gt;&lt;td&gt;&lt;/td&gt;&lt;td&gt;&lt;h5&gt;&lt;?php echo $row_subsystems-&gt;txtTaskName?&gt;&lt;/h5&gt;&lt;/td&gt;&lt;td&gt;&lt;/td&gt;&lt;td&gt;&lt;/td&gt;&lt;td&gt;&lt;/td&gt;&lt;td&gt;&lt;/td&gt;&lt;td&gt;&lt;/td&gt;&lt;td&gt;&lt;/td&gt;&lt;td&gt;&lt;/td&gt;&lt;td&gt;&lt;/td&gt;&lt;td&gt;&lt;/td&gt;&lt;/tr&gt;&lt;?php } foreach($tasks_query as $row_tasks) { if($row_tasks-&gt;intInside == $row_subsystems-&gt;intTaskID )//if the task is the subtask of a subsystem { if($row_tasks-&gt;intSummary == 0)//if the task is an executable task &amp; doesn't have any subtask: { $query_team_user_id = $this-&gt;admin_in_out_model-&gt;get_user_team_task_query($row_tasks-&gt;intTaskID); foreach($query_team_user_id as $row_teamid) {?&gt; &lt;tr&gt;&lt;?php $query_teamname = $this-&gt;admin_in_out_model-&gt;get_team_name($row_teamid-&gt;intTeamID); $query_fn_ln = $this-&gt;admin_in_out_model-&gt;get_fn_ln_from_userid($row_teamid-&gt;intUserID); foreach($query_teamname as $row_teamname) {?&gt; &lt;td&gt;&lt;/td&gt;&lt;td&gt;&lt;/td&gt;&lt;td&gt;&lt;b&gt;&lt;?php echo $row_tasks-&gt;txtTaskName;?&gt;&lt;/b&gt;&lt;/td&gt; &lt;td&gt;&lt;b&gt;&lt;font color='#F33558'&gt;&lt;?php echo $row_teamname-&gt;txtTeamName;?&gt;&lt;/font&gt;&lt;/b&gt;&lt;/td&gt;&lt;?php } foreach($query_fn_ln as $row_f_l_name) {?&gt; &lt;td&gt;&lt;?php echo $row_f_l_name-&gt;txtFirstname." ".$row_f_l_name-&gt;txtLastname;?&gt;&lt;/td&gt;&lt;td&gt;&lt;/td&gt;&lt;td&gt;&lt;/td&gt;&lt;td&gt;&lt;/td&gt;&lt;td&gt;&lt;/td&gt;&lt;td&gt;&lt;/td&gt;&lt;td&gt;&lt;/td&gt;&lt;td&gt;&lt;/td&gt;&lt;?php }?&gt; &lt;/tr&gt;&lt;?php } } } } } } } } }?&gt; </code></pre> <p>and in controller i have </p> <pre><code>$projects_query = $this-&gt;admin_in_out_model-&gt;get_projects(); $tasks_query = $this-&gt;admin_in_out_model-&gt;get_systems(); $userteamtask = $this-&gt;admin_in_out_model-&gt;get_user_team_task(); $data['tasks_query'] = $tasks_query; $data['projects_query'] = $projects_query; $this-&gt;load-&gt;view('project_view',$data); </code></pre> <p>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</p> <p>I have to add that, my model function have parameters these are the model functions: </p> <pre><code> function get_projects() { $this -&gt; db -&gt; select('*'); $this -&gt; db -&gt; from('tbl_task'); $this -&gt; db -&gt; where('intInside','0'); $query = $this-&gt;db-&gt;get(); return $query-&gt;result(); } function get_systems() { $this -&gt; db -&gt; select('*'); $this -&gt; db -&gt; from('tbl_task '); $this -&gt; db -&gt; where('intInside &lt;&gt; ','0'); $query = $this-&gt;db-&gt;get(); return $query-&gt;result(); } function get_user_team_task_query($task_id)//gets information from tbl_userteamtask where the field intTaskID is equal to $task_id { $this -&gt; db -&gt; select('*'); $this -&gt; db -&gt; from('tbl_userteamtask'); $this -&gt; db -&gt; where('intTaskID',$task_id); $query_teamid = $this-&gt;db-&gt;get(); return $query_teamid-&gt;result(); } function get_user_team_task()//gets information from tbl_userteamtask where the field intTaskID is equal to $task_id { $this -&gt; db -&gt; select('*'); $this -&gt; db -&gt; from('tbl_userteamtask'); // $this -&gt; db -&gt; where('intTaskID',$task_id); $query_teamid = $this-&gt;db-&gt;get(); return $query_teamid-&gt;result(); } function get_team_name($query_teamid) { $this -&gt; db -&gt; select('*'); $this -&gt; db -&gt; from('tbl_team'); $this -&gt; db -&gt; where('intTeamID',$query_teamid); $query_teamname = $this-&gt;db-&gt;get(); return $query_teamname-&gt;result(); } function get_user_name($query_userid) { $this -&gt; db -&gt; select('*'); $this -&gt; db -&gt; from('tbl_user'); $this -&gt; db -&gt; where('intUserID',$query_userid); $query_username = $this-&gt;db-&gt;get(); return $query_username-&gt;result(); } function get_fn_ln_from_userid($selected_id) { $this -&gt; db -&gt; select('tbl_user.intUserID, tbl_user.intPersonID,tbl_person.intPersonID,tbl_person.txtFirstname, tbl_person.txtLastname'); $this -&gt; db -&gt; from('tbl_user , tbl_person'); $where = "tbl_user.intPersonID = tbl_person.intPersonID "; $this -&gt; db -&gt; where($where); $this -&gt; db -&gt; where('tbl_user.intUserID', $selected_id); $query = $this -&gt; db -&gt; get();//makes query from DB return $query-&gt;result(); } </code></pre> <p>do I have to use subquery ?</p> <p>is this true? i mean can i do this?</p> <pre><code>foreach( $data as $key =&gt; $each ) { $data[$key]['team_id'] = $this-&gt;get_user_team_task_query( $each['intTaskID'] ); foreach($data[$key]['team_id'] as $key_teamname =&gt; $each) { $data[$key_teamname]['team_name'] = $this-&gt;get_team_name( $each['intTeamID'] ); } } </code></pre> <p>the model code:</p> <pre><code>foreach( $data as $key =&gt; $each ) { $data[$key]['intTaskID'] = $each['intTaskID']; $data[$key]['team_id'] = $this-&gt;get_user_team_task_query( $each['intTaskID'] ); foreach($data[$key]['team_id'] as $key =&gt; $each) { $data[$key]['team_name'] = $this-&gt;get_team_name( $each['intTeamID'] ); #fetching of the teamname and saving in the array $data[$key]['user_name'] = $this-&gt;get_fn_ln_from_userid( $each['intUserID'] ); foreach($data[$key]['user_name'] as $key =&gt; $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']; } </code></pre> <p>}</p> <p>a part of view code:</p> <pre><code>foreach($tasks_query as $row_systems) { if($row-&gt;intTaskID == $row_systems['intInside'])// intInside indicades that the current task($row2) is the subset of which task (system , subsystem or project) {?&gt; &lt;tr&gt;&lt;td&gt;&lt;/td&gt;&lt;td&gt;&lt;/td&gt;&lt;td&gt;&lt;h4&gt; &lt;?php echo $row_systems['txtTaskName'];?&gt;&lt;/h4&gt;&lt;/td&gt;&lt;?php foreach($tasks_query as $row_subsystems) { if($row_systems['intTaskID'] == $row_subsystems['intInside']) {?&gt; &lt;tr&gt;&lt;td&gt;&lt;/td&gt;&lt;td&gt;&lt;/td&gt;&lt;td&gt;&lt;h4&gt; &lt;?php echo $row_subsystems['txtTaskName'];?&gt;&lt;/h4&gt;&lt;/td&gt;&lt;?php foreach($tasks_query as $row_tasks) { if($row_subsystems['intTaskID'] == $row_tasks['intInside']) {?&gt; &lt;tr&gt;&lt;td&gt;&lt;/td&gt;&lt;td&gt;&lt;/td&gt;&lt;td&gt;&lt;h4&gt; &lt;?php echo $row_tasks['txtTaskName'];?&gt;&lt;/h4&gt;&lt;/td&gt; &lt;td&gt;&lt;?php echo $row_tasks['team_name'];?&gt; &lt;/td&gt; &lt;td&gt;&lt;?php echo $row_tasks['first_name'];?&gt; &lt;/td&gt; &lt;?php } } } } </code></pre> <p>} ?> </p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USf k
    1. USf k
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POmaking query from different related tables using codeigniter
      1. This table or related slice is empty.
      1. VTDownMod
    2. VO
      singulars
      1. POmaking query from different related tables using codeigniter
      1. This table or related slice is empty.
      1. VTUpMod
    1. COPaste only the relevant code.
      singulars
      1. POmaking query from different related tables using codeigniter
      1. USNil'z
    2. COthis is only the relevant code !!
      singulars
      1. POmaking query from different related tables using codeigniter
      1. USf k
    3. CONot subquery, make a `2d array` of the data you want, and then pass it on to the controller, In view, just loop the data and print.
      singulars
      1. POmaking query from different related tables using codeigniter
      1. USNil'z
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload