Categories > TinyButStrong general >

tbsmerge when sql has join of several tables

The forum is closed. Please use Stack Overflow for submitting new questions. Use tags: tinybutstrong , opentbs
By: kle_py
Date: 2008-06-09
Time: 02:23

tbsmerge when sql has join of several tables

Hi,

I have a doubt on how to display retrieved values when the sql used for the tbsmerge includes fields of several tables, using the JOIN command in SQL.
Maybe it is mentioned somewhere in the manual, or in the examples, however i couldn't find it for now..

My example:
$TBS->MergeBlock('ddw_city','tbssql',"select dp.id, dp.name, ci.id, ci.name
from areas as dp left outer join areas as ci ON ci.id_parent =  dp.id
where ci.type = 'city'");

when using in the template:
<option value="[ddw_city.id;block=option;default]">[ddw_city.name]</option>

the displayed strings are the values of: ci.id, ci.name.
In my case this is ok,
- but is there a way to control for example which "name" to display ?
i tried [ddw_city.dp.name] but it gave me an error, and i understand it is not correct, because there is no sub-array "dp".
- Of course i could change my sql to:
$TBS->MergeBlock('ddw_city','tbssql',"select dp.id as dp_id, dp.name as dp_name, ci.id as ci_id, ci.name as ci_name
from areas as dp left outer join areas as ci ON ci.id_parent =  dp.id
where ci.type = 'city'");
but i would prefer it without the "AS" (sql-)command, if possible.

Is there a way to do this ?

thanks
By: Skrol29
Date: 2008-06-09
Time: 09:49

Re: tbsmerge when sql has join of several tables

Hi Kle_Py,

Unfortunately, this is not yet possible. The dot (.) is reserved by TBS to be an item separator.

Another bad solution is to use an "ondata" user function to copy the column with another name. This enables you to not touch the SQL.
By: kle_py
Date: 2008-06-09
Time: 18:58

Re: tbsmerge when sql has join of several tables

Ok, thanks for the feedback.

I think the most simple way is if i stick to "renaming" of the columns using the "AS" in the sql.

Thanks anyway, and i still think TBS is one of the gratest library for PHP, good job