Unexpected results in print_r

[eluser]jshultz[/eluser]
I have a join I'm doing between two tables but i'm getting unexpected results from the query. I know it's returning something, though.

here's my model:

Code:

function tasksByProjectId() {
    /* $sql = "SELECT `projectid` \n"
    . "FROM `projects` \n"
    . "LEFT JOIN `tasks` ON tasks.project_id = projects.projectid LIMIT 0, 30 ";
     */
    
    $this->db->select('projectid');
    $this->db->from('projects');
    $this->db->join('tasks', 'tasks.project_id = projects.projectid', 'full');
    return $this->db->get();
}

and then in the controller it's just a simple:

Code:

$data['tasksByProject'] = $this->Project_model->tasksByProjectId();

and in the view I put a:

Code:

<pre>
&lt;?php
    foreach($tasksByProject as $row)
    {print_r($row);
    }
    
?&gt;
</pre>

just so i could see what was coming out. Now, here's the odd thing. Depending on whether or not I do a left or full join I'll get various amounts of empty rows from this code:

Code:

<table>
        <tr><th>Project ID</th><th>Project Name</th><th>Task ID</th><th>Task Name</th></tr>
&lt;?php foreach($projectPlusTasks->result() as $row): ?&gt;
    
    <tr>
        <td>&lt;?=$row->projectid?&gt;</td>
        <td></td>
        <td></td>
        <td></td>
    </tr>
    
&lt;?php endforeach; ?&gt;
</table>

What's going on?

Here's what I get from the print_r:

Code:

Resource id #29Resource id #44Array
(
)
Array
(
)
03

[eluser]JoostV[/eluser]
Your model should retrieve the query result before returning anything.

Code:

$query = $this->db->get();
return $query->result();

If you use a left join query there is no need for a join query at all, is there? You could just as well do this:

Code:

$this->db->select('projectid');
$this->db->from('projects');

[eluser]jshultz[/eluser]
when I added these two line:

Code:

$query = $this->db->get();
    return $query->result();

the print_r changed to this:

Code:

stdClass Object
(
    [projectid] => 26
)
stdClass Object
(
    [projectid] => 30
)
stdClass Object
(
    [projectid] => 28
)

and none of the table code rendered and everything after it disappeared.

according to the user guide I need something like this:

Code:

$this->db->select('*');
$this->db->from('blogs');
$this->db->join('comments', 'comments.id = blogs.id');

$query = $this->db->get();

[eluser]JoostV[/eluser]
>according to the user guide I need something like this:
Yes, but you need to generate the query result as well. Look here: http://ellislab.com/codeigniter/user-gui...sults.html

In your view, loop through the result like this:

Code:

if (count($tasksByProject) > 0) {
    foreach($tasksByProject as $row){
        echo $row-> projectid;
    }
}

[eluser]JoostV[/eluser]
In short.

Model:

Code:

function tasksByProjectId() {
    $this->db->select('projectid');
    $this->db->get('projects');
    return $query->result();
}

Controller

Code:

$data['tasksByProject'] = $this->Project_model->tasksByProjectId();

View:

Code:

<table>
    <tr>
        <th>Project ID</th><th>Project Name</th><th>Task ID</th><th>Task Name</th>
    </tr>
&lt;?php foreach($tasksByProject->result() as $row): ?&gt;
  
    <tr>
        <td>&lt;?=$row->projectid?&gt;</td>
        <td></td>
        <td></td>
        <td></td>
    </tr>
    
&lt;?php endforeach; ?&gt;
</table>

[eluser]jshultz[/eluser]
it's working!

I have another question?

How do i get the other columns to appear in the query result?

[eluser]jshultz[/eluser]
so i figured out what print_r was outputting. that was cool.

I edited my query up some to retrieve additional columns:

function tasksByProjectId() {
/* $sql = "SELECT projectid, project_name, taskid, task\n"
. "FROM projects\n"
. "LEFT JOIN tasks ON tasks.project_id = projects.projectid LIMIT 0, 30 "; ";

$query - $this->db->query($sql);
return $query->result_array(); */

$this->db->select('projectid', 'project_name', 'taskid', 'task');
$this->db->from('projects');
$this->db->join('tasks', 'tasks.project_id = projects.projectid', 'left');
$query = $this->db->get();
return $query->result();
}

but i'm still only receiving the projectid from the database when i updated my code to do this:

Code:

<pre>
&lt;?php
if (count($projectPlusTasks) > 0) {
    foreach($projectPlusTasks as $row){
        echo $row-> projectid;
        echo $row-> project_name;
        echo $row-> taskid;
        echo $row-> task;
    }
}
    
?&gt;
</pre>

I tested the sql code in phpmyadmin and it's returning all the columns and rows, though. it's just not showing up on the page.

[eluser]JoostV[/eluser]
You have a space after the arrows on every row call

Code:

$row-> projectid

Should be

Code:

$row->projectid

[eluser]jshultz[/eluser]
It didn't make any difference, it's still only echoing out the projectid:

Code:

<pre>
&lt;?php
if (count($projectPlusTasks) > 0) {
    foreach($projectPlusTasks as $row){
        echo $row->projectid;
        echo $row->project_name;
        echo $row->taskid;
        echo $row->task;
    }
}
    
?&gt;
</pre>
<table>
        <tr><th>Project ID</th><th>Project Name</th><th>Task ID</th><th>Task Name</th></tr>
&lt;?php foreach($projectPlusTasks as $row): ?&gt;
    
    <tr>
        <td>&lt;?=$row->projectid?&gt;</td>
        <td>&lt;?=$row->project_name?&gt;</td>
        <td>&lt;?=$row->taskid?&gt;</td>
        <td>&lt;?=$row->task?&gt;</td>
    </tr>
    
&lt;?php endforeach; ?&gt;
</table>

[eluser]jshultz[/eluser]
Ok, it's all working now. Thanks for your help. Here's what I ended up doing that worked:

Code:

function tasksByProjectId() {
    /* $sql = "SELECT projectid, project_name, taskid, task\n"
    . "FROM projects\n"
    . "LEFT JOIN tasks ON tasks.project_id = projects.projectid LIMIT 0, 30 "; ";
    
    $query - $this->db->query($sql);
    return $query->result_array(); */
    
    $this->db->select('*');
    $this->db->from('projects');
    $this->db->join('tasks', 'tasks.project_id = projects.projectid', 'left');
    $query = $this->db->get();
    return $query->result();
}

and here's the view:

Code:

<table>
        <tr><th>Project ID</th><th>Project Name</th><th>Task ID</th><th>Task Name</th></tr>
&lt;?php foreach($projectPlusTasks as $row): ?&gt;
    
    <tr>
        <td>&lt;?=$row->projectid?&gt;</td>
        <td>&lt;?=$row->project_name?&gt;</td>
        <td>&lt;?=$row->p_due_date?&gt;</td>
        <td></td>
    </tr>
    <tr>
        <td>Task:</td>
        <td>&lt;?=$row->taskid?&gt;</td>
        <td>&lt;?=$row->task?&gt;</td>
        <td>&lt;?=$row->t_due_date?&gt;</td>
    </tr>
    
&lt;?php endforeach; ?&gt;
</table>