Running a Query with in a foreach loop

[eluser]Phil Sturgeon[/eluser]
Look at the code a little closer.

Firstly, he needs a list of all projects.

Then he needs a list of all projects a user is involved with.

Finally he needs a list of all tasks for that project. You COULD join the two but it makes the looping a P.I.T.A and think about it, its not going to be that much data. If a user is involved with 10,000 projects then the slow performance will be an acceptable punishment for being such an idiot.

the only performance issue here would be the full list of projects for each user, but that is why the cache was invented. :-)

[eluser]jshultz[/eluser]
Actually, I don't need a list of all projects. Only projects that the user has created with and without tasks.

[eluser]kgill[/eluser]
[quote author="Phil Sturgeon" date="1249960433"]

You COULD join the two but it makes the looping a P.I.T.A and think about it, its not going to be that much data. If a user is involved with 10,000 projects then the slow performance will be an acceptable punishment for being such an idiot.

the only performance issue here would be the full list of projects for each user, but that is why the cache was invented. :-)[/quote]

Which is why I prefaced it with in this case it's fine and explained why Mostly I was speaking in general terms not about projects. You and I know when to use which method but if you don't tell the new folk that this is a bad idea when they encounter large datasets they write inefficient code thinking that's the way it's supposed to be done.

[eluser]jshultz[/eluser]
Ok, here's an update on what I've been doing. I decided to rework my code just a bit and I have this block now:

Code:

<div><table>
        <tr><th>Project</th><th>Due Date</th><th>Date Added</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 foreach($tasks->result() as $row) : ?&gt;
        
            &lt;?php
                if ($tasks->projectid === $projects->id) {
                    echo "<tr><td><p><strong>" . $row->task . "</strong></p></td><td>" . $row->due_date . "</td></tr>";
                }
                else {
                    echo "<tr><td><p><strong>There is no task</strong></p></td><td><p><strong>No Due Date</strong></p></td></tr>";
                }
                ?&gt;
                
        &lt;?php endforeach; ?&gt;
        
    
    &lt;?php endforeach; ?&gt;
    </table></div>

My problem is that it's echoing at every task (there's 3 each with a different projectid). My concern is that if I change

Code:

($tasks->projectid === $projects->id)

to ($tasks->projectid != $projects->id) then I get the else response. Why is that? I checked the id of the projects and they are 26 through 30 and the projectids of the tasks are 26, 28 and 30. Obviously, at least to me, they are not all equal to each other. What am I missing?

[eluser]Phil Sturgeon[/eluser]
$tasks and $projects are your arrays, not your single object variables containing the data.

Let's play spot the difference.

Code:

<div><table>
        <tr><th>Project</th><th>Due Date</th><th>Date Added</th></tr>
    &lt;?php foreach($projects->result() as $project): ?&gt;
    
        <tr>
            <td><p>&lt;?=$project->project_name?&gt;</p></td>
            <td><p>&lt;?=$project->due_date?&gt;</p></td>
            <td></td>
        </tr>
        
        &lt;?php foreach($tasks->result() as $task) : ?&gt;
        
            &lt;?php
                if ($task->projectid == $project->id) {
                    echo "<tr><td><p><strong>" . $task->task . "</strong></p></td><td>" . $task->due_date . "</td></tr>";
                }
                else {
                    echo "<tr><td><p><strong>There is no task</strong></p></td><td><p><strong>No Due Date</strong></p></td></tr>";
                }
                ?&gt;
                
        &lt;?php endforeach; ?&gt;
        
    
    &lt;?php endforeach; ?&gt;
    </table></div>

That make sense? You want to name your variables something more meaningful than row, after all by the time they are in the view this should be nothing to do with the database so something more general would apply.

Did you work out how to get tasks into the projects array in the end? It doesn't look like it from this code.

$projects and $tasks should not be set at the same level, tasks should be part of $projects, right? Post your controller method up here.

[eluser]Johan André[/eluser]
[quote author="Phil Sturgeon" date="1249960433"]If a user is involved with 10,000 projects then the slow performance will be an acceptable punishment for being such an idiot.[/quote]

Haha! Funniest comment today!

[eluser]jshultz[/eluser]
I tried it with the way you had suggested but I was getting the same results. Here is what I have right now:

Site controller:

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();
            
            foreach( $data['projects']->result_array() as $project)
            {
                $project['tasks'] = $this->Project_model->getTasks( $project['ID'] );
            }
            
            $data['tasks'] = $this->Project_model->getTasks();

            $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]

Code:

foreach( $data['projects']->result_array() as &$project)
            {
                $project['tasks'] = $this->Project_model->getTasks( $project['ID'] );
            }

You are missing the & before $project in foreach. this probably wont work if you are still foreach'ing through $result_array();

STOP RETURNING $query! :-p

[eluser]jshultz[/eluser]
I really appreciate your time in helping me. so often i'm on the other side of the coin helping friends/family with viruses (why???) or css so to be the newbie starting out again is really interesting for me.

What does the & do?

[eluser]Phil Sturgeon[/eluser]
http://www.phpbuilder.com/manual/en/lang...s.pass.php

If you pass by reference instead of making a copy you are modifying the original variable.

So:

Code:

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

is effectively the same as:

Code:

$projects = array();
foreach( $data['projects'] as $project)
{
    $project['tasks'] = $this->Project_model->getTasks( $project['ID'] );
    $projects[] = $project;
}

$data['projects'] = $projects;
unset($projects);

See what im getting at?

Instead of creating a new array to hold them all then assigning it to the same value and unsetting the temporary array, you just modify the original variable on the fly.

Basically stop returning $query and return $query->result_array() and use the code with the & in, it will work. :-)