CodeIgniter Forums
need to display data from three tables - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forum-20.html)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forum-23.html)
+--- Thread: need to display data from three tables (/thread-36156.html)



need to display data from three tables - El Forum - 11-23-2010

[eluser]Brad K Morse[/eluser]
Structure:

Code:
Goal 1
Objective
   Step
   Step

Objective
  Step
  Step

Goal 2
Objective
   Step
   Step

Objective
  Step
  Step

So each goal can have 1 or more objectives, each objective can have 1 or more steps.

The three tables: goals, objectives, steps

goals.id = objectives.goal_id

objectives.id = steps.objective_id

What I want to do is display that structure (shown above) in a list and am unsure on how I'll compose the appropriate join


need to display data from three tables - El Forum - 11-23-2010

[eluser]WanWizard[/eluser]
Query them in one select, joining goal to objective to step, other by goals.id, objectives.id, steps.id.

Then fetch the result into an array, and process it through a foreach:
Code:
$goal = $objective = NULL;
foreach ( $result as $row )
{
    if ( $goal != $row['goal_id'] )
    {
        // new goal, do something here
        $goal = $row['goal_id'];
    }

    if ( $objective != $row['objective_id'] )
    {
        // new objective, do something here
        $objective = $row['objective_id'];
    }

    // do something with the step
}