Page 1 of 1

Can't get data from joined column

Posted: Mon Sep 03, 2012 12:14 pm
by ourmaninparis
Hello,

I've the following code that I'm using inside a Joomla extension. The code outputs a list of items used in a selection list.

Code: Select all

<?php

$user =& JFactory::getUser();   
$usr_id = $user->get('id');  

$sql = "SELECT t1.*, t2.*
FROM sgj_noticeboards AS t1 INNER JOIN sgj_contentbuilder_records AS t2 ON (t1.storage_id=t2.reference_id AND t1.id=t2.record_id) 
WHERE published=1 AND user_id=$usr_id";

JFactory::getDBO()->setQuery($sql);
$rows = JFactory::getDBO()->loadObjectList();

$value = '';

foreach($rows As $row )
{
$opt1 = $row->Notice_Board_Name;
$opt2 = $row->id;

$value .= "$opt1;$opt2"."\n";
}
return $value;
?>


The above code works but the id returned comes from the wrong table (t2). I have therefore modified the code to specify the table to use for each of the columns (t1.Notice_Board_Name instead of Notice_Board_Name and t1.id instead of id.) This doesn't work though. Any suggestions?

Re: Can't get data from joined column

Posted: Mon Sep 03, 2012 6:19 pm
by requinix
The table name is not included in the returned array keys. Thus you have two different "id" values and the latter one (t2's) will overwrite the former one (t1's).

Pull just the information you actually need.

Code: Select all

SELECT t1.id, t?.Notice_Board_Name FROM...

Re: Can't get data from joined column

Posted: Tue Sep 04, 2012 5:09 am
by ourmaninparis
Hello,

I'd already tried that but it still wouldn't work. In the end I ended up using aliases:

Code: Select all

SELECT t1.Notice_Board_Name AS NBN, t1.id AS NBID, t2.*
This worked.

Thanks, Shaun