Displaying a table of results

[eluser]mdcode[/eluser]
Which itself is working fine, however the Customer field just has the customer id in it instead of the customer name. I am trying to query two tables to get the name to display but I cannot make AR spit the correct query out. Here's the query in my Model:

Code:

function get_last_ten_entries()
{
$this->db->select('projects.id, projects.customer, projects.date_required, projects.job_status, customers.id, customers.name');
$this->db->from('projects, customers');
$this->db->where('projects.customer', 'customers.id');
$this->db->order_by('projects.id', 'DESC');
$this->db->limit(10);
        
$query = $this->db->get();
return $query->result();
}

Which I thought would produce a query like this:

Code:

SELECT `lip_projects`.`id`, `lip_projects`.`customer`, `lip_projects`.`date_required`, `lip_projects`.`job_status`, `lip_customers`.`id`, `lip_customers`.`name` FROM (`lip_projects`, `lip_customers`) WHERE `lip_projects`.`customer` = `lip_customers`.`id` ORDER BY `lip_projects`.`id` DESC LIMIT 10

Which works in phpmyadmin by the way, however the customers.id part is being passed "as is", so the query becomes

Code:

SELECT `lip_projects`.`id`, `lip_projects`.`customer`, `lip_projects`.`date_required`, `lip_projects`.`job_status`, `lip_customers`.`id`, `lip_customers`.`name` FROM (`lip_projects`, `lip_customers`) WHERE `lip_projects`.`customer` = 'customers.id' ORDER BY `lip_projects`.`id` DESC LIMIT 10

And so not displaying any results. Do I have to resort to using plain MySQL queries yet again to pull this off??

Thanks.

[eluser]kgill[/eluser]
If you want to use that style of joining tables and AR you'll need to pass the full where clause in your db->where.

Code:

$this->db->where("`lip_projects`.`customer` = `lip_customers`.`id`");

Alternatively you could use the db->join() method, of course you might want a 2nd opinion on that - I personally can't stand AR and don't use it.

[eluser]Thorpe Obazee[/eluser]
If I understand the problem right, you should use db->join()

Code:

$this->db->select("lip_projects.id, lip_projects.customer, lip_projects.date_required, lip_projects.job_status, lip_customers.id, lip_customers.name");
$this->db->from('lip_projects');
$this->db->join('lip_customers', 'lip_customers.id = lip_projects.customer', 'LEFT');
$this->db->order_by('lip_projects.id', 'DESC');
$this->db->limit(10);

untested of course.

[eluser]mdcode[/eluser]
Thanks for the answers guys, I have been trying joins to no avail and if I use this code:

Code:

function get_last_ten_entries()
{
$this->db->select('projects.id, projects.customer, projects.date_required, projects.job_status, customers.id, customers.name');
$this->db->from('projects, customers');
$this->db->join('customers', 'projects.customer = customers.id', 'inner');
$this->db->order_by('projects.id', 'DESC');
$this->db->limit(10);
        
$query = $this->db->get();
}

I get the error:

Code:

A PHP Error was encountered
Severity: Warning
Message: Invalid argument supplied for foreach()
Filename: pages/projects_reports.php
Line Number: 35

The foreach code on my view is like this (which could be wrong, though work when only the customer.id is used):

Code:

foreach($query as $row){
echo "<tr>";
echo "<td>". $row->id ."</td>";
echo "<td>". $row->name ."</td>";
echo "<td>". $row->date_required ."</td>";
echo "<td>". $row->job_status ."</td>";
echo "</tr>";
}

[eluser]Thorpe Obazee[/eluser]
see my code above.

[eluser]mdcode[/eluser]
bargainph, I have tested both yours and my join, both yield the same result...

Code:

A PHP Error was encountered
Severity: Warning
Message: Invalid argument supplied for foreach()
Filename: pages/projects_reports.php
Line Number: 35

[eluser]Thorpe Obazee[/eluser]
Maybe you should post 'new' your code again. and probably test the sql statement produced

[eluser]mdcode[/eluser]

Code:

function get_last_ten_entries()
{
$this->db->select('projects.id, projects.customer, projects.date_required, projects.job_status, customers.id, customers.name');
$this->db->from('projects');
$this->db->join('customers', 'projects.customer = customers.id', 'left');
$this->db->order_by('projects.id', 'DESC');
$this->db->limit(10);
        
$query = $this->db->get();
}

However I tried a direct copy and paste of yours and still got the same error... both yours and my query works in phpmyadmin so I don't think the query itself is at fault but the foreach loop.

[eluser]Thorpe Obazee[/eluser]
can you should me the foreach loop?

[eluser]mdcode[/eluser]
As above

Code:

foreach($query as $row){
echo "<tr>";
echo "<td>". $row->id ."</td>";
echo "<td>". $row->name ."</td>";
echo "<td>". $row->date_required ."</td>";
echo "<td>". $row->job_status ."</td>";
echo "</tr>";
}