Running a Query with in a foreach loop

[eluser]jshultz[/eluser]
I feel like I'm on the right track with this but I'm missing something.

I have a query that runs through a list of projects by the user id:

Code:

function getByUserId() {
    $user_id = $this->tank_auth->get_user_id();
    return $this->db->get_where('projects', array('user_id'=>$user_id));
}

On the view page I have a foreach loop that cycles through the code:

Code:

<table>
        <tr><th>Project</th><th>Due Date</th></tr>
    &lt;?php foreach($projects->result() as $row): ?&gt;
    
        <tr>
            <td><p>&lt;?=$row->project_name?&gt;</p></td>
            <td><p>&lt;?=$row->due_date?&gt;</p></td>
            <td></td>
        </tr>
    
    &lt;?php endforeach; ?&gt;
    </table>

Now, I'm wanting to insert before the end the following code:

Code:

<?php foreach($tasks->result() as $row): ?>
        <tr>
            <td><p>&lt;?=$row->tasks?&gt;</p></td>
            <td><p>&lt;?=$row->due_date?&gt;</p></td>
            <td></td>
        </tr>
    &lt;?php endforeach; ?&gt;

As you might imagine, it's going to get a list of tasks associated with the project that it just cycled through. I'm sure it's simple, I just want to pass a variable to this function:

Code:

function getTasksByProjectId($ID) {
    return $this->db->get_where('tasks', array('projectid'=>$projectID));
}

My idea is that when it goes through the loop it has the ID of the project in the array and somehow magical elves will get that id to the function and tell it what tasks it should be spitting out. Unfortunately for me, I haven't found the elves that are going to be relaying that information.

Any suggestions?

[eluser]verynewtothis[/eluser]
Try this in your first loop

Code:

<table>
        <tr><th>Project</th><th>Due Date</th></tr>
    &lt;?php foreach($projects->result() as $row): ?&gt;
    
        <tr>
            <td><p>&lt;?= anchor("ControllerName/getTaskByProjectID/$row->ID", "$row->project_name")?&gt;</p></td>
            <td><p>&lt;?=$row->due_date?&gt;</p></td>
            <td></td>
        </tr>
    
    &lt;?php endforeach; ?&gt;
    </table>

This will pass the IDs to your getTaskByProjectID function.

[eluser]Phil Sturgeon[/eluser]
Not really what he meant.

Dude, I would do this in your controller.

Code:

$data['projects'] = $this->projects_model->getByUserId( $user_id )->results();

foreach($data['projects'] as &$project)
{
    $project['tasks'] = $this->tasks_model->getTasksByProjectId( $project->ID )->results();
}

That will add 'tasks' key into each project item (by reference, that is what the & is doing) and mean you can add a foreach within your main foreach.

Although I wouldn't make a habit of returning the $query object, you should pick a type of result or row to return and just send that back as an array or object.

[eluser]jshultz[/eluser]
Then what would be the right way to do it?

Here's what I have in the site controller currently:

Code:

function project_list()
    {
          if (!$this->tank_auth->is_logged_in()) {
            redirect('/auth/login/');
          } else {
            $this->load->model('Project_model');
            /*$data['query'] = $this->Project_model->getAll();*/
            $data['projectList'] = $this->Project_model->selectFromProjects();
            $data['projects'] = $this->Project_model->getByUserId();
            $data['tasks'] = $this->Project_model->getTasksByProjectId();
            $data['user_id']    = $this->tank_auth->get_user_id();
            $data['username']    = $this->tank_auth->get_username();
            $data['page_title'] = 'OSM Projects';
            $data['page'] = 'project-list-view'; // pass the actual view to use as a parameter
            $this->load->view('container',$data);
        
        }

    }

[eluser]Phil Sturgeon[/eluser]
I just showed you! You want some help chewing your food too?!

Code:

function project_list()
    {
          if (!$this->tank_auth->is_logged_in()) {
            redirect('/auth/login/');
          } else {
            $this->load->model('Project_model');
            
            $data['projectList'] = $this->Project_model->selectFromProjects();
          
            $data['user_id']    = $this->tank_auth->get_user_id();
            $data['username']    = $this->tank_auth->get_username();

            $data['projects'] = $this->Project_model->getByUserId( $data['user_id'] );
            
            foreach( $data['projects']->result_array() as &$project)
            {
                $project['tasks'] = $this->Project_model->getTasksByProjectId( $project['ID'] );
            }
            
            $data['page_title'] = 'OSM Projects';
            $data['page'] = 'project-list-view'; // pass the actual view to use as a parameter
            $this->load->view('container',$data);
        
        }

    }

That will add a key 'tasks' into each project entry. I cannot make it any more obvious than that! :lol:

[eluser]jshultz[/eluser]
Sorry, I misunderstood your original post. I thought you were saying this was how I could do it, but you would suggest doing it a different way:

"Although I wouldn’t make a habit of returning the $query object, you should pick a type of result or row to return and just send that back as an array or object."

I see what your saying now. Thanks.

[eluser]kgill[/eluser]
I cringe every time I see someone suggest queries in loops, in this case it's fine because I don't think your project list is going to get very big but unless you know it's always going to be a very short list use a join! Using a loop is about as inefficient as it gets, especially when you get into large data sets.

Do the math... suppose you've got 1000 projects that's 1001 hits on the DB for every user that is connecting, if you get 10 users in the space of a second and you just hammered your DB with 10,000 queries in under a second. You can see how that gets bad the larger the data set and the more users connected. If you used a join you'd hit the DB once for each user, the trade off is that you're returning a larger result set and transferring a little more data than running the queries separately.

[eluser]jshultz[/eluser]
How would I accomplish the same goal with a join? I'm sorry, but i've never used it.

[eluser]kgill[/eluser]
Ok quick illustrative example

Code:

// suppose we have 2 queries with the following results

query 1:
proj_ID proj_title
------- -----------
1       foo
2       bar
3       baz
4       qux

query 2:
task_ID proj_id task_desc
------- ------- ----------
1       1       do something
2       1       then do something else
3       1       finish it
1       2       catch fish
2       2       eat fish
1       4       jump
2       4       hop
3       4       skip
4       4       rest

//Using a left join (to make sure you picked up projects with no tasks) you'd get the following:

proj_id proj_name task_no task_desc
------- --------- ------- ----------
1       foo       1       do something
1       foo       2       then do something else
1       foo       3       finish it
2       bar       1       catch fish
2       bar       2       eat fish
3       baz       NULL    NULL
4       qux       1       jump
4       qux       2       hop
4       qux       3       skip
4       qux       4       rest

As you can see the project name & id are going to repeat because each has multiple tasks assigned to it. Once you know that you can just loop through testing for the change.

Code:

//psuedo-code follows
$proj_id = -1;
foreach ($project as $row) {
   if $row->proj_id <> $proj_id { // if the project id changes print something out
     echo $row->proj_name;
     $proj_id = $row->proj_id;
   }
   // print out details for that project name
   echo 'task '. $row->task_no . ' ' . $row->task_desc;
}

Which would result in:

foo
task 1 do something
task 2 then do something else
task 3 finish it
bar
task 1 catch fish
task 2 eat fish
baz
qux
task 1 jump
etc...

[eluser]jshultz[/eluser]
Oh that's cool! I didn't understand how it worked before. I read the documentation on http://ellislab.com/codeigniter/user-gui...ecord.html but wasn't clear on how it would apply but now I do. Thanks!