Can't get data from joined column

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
ourmaninparis
Forum Newbie
Posts: 21
Joined: Wed Apr 18, 2012 8:09 am

Can't get data from joined column

Post 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?
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: Can't get data from joined column

Post 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...
ourmaninparis
Forum Newbie
Posts: 21
Joined: Wed Apr 18, 2012 8:09 am

Re: Can't get data from joined column

Post 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
Post Reply