View previous topic :: View next topic |
Author |
Message |
garytheprogrammer Smarty Rookie
Joined: 28 Jul 2004 Posts: 5 Location: Eugene, Oregon
|
Posted: Wed Jul 28, 2004 6:35 pm Post subject: Joined SQL Array Syntax |
|
|
This problem has been annoying me for some time:
Say I join two tables and just 'select all' on some query:
Code: |
select * from table1 left join table2 on parameter1 where parameter2
|
then i get an associative php array like:
Code: |
myarray =
array(
'table1.data1',
'table2.data2'
)
|
Now just try to use this in Smarty!
Code: |
{$myarray.table1.data1}
|
this is JUNK in my beloved smarty template...
any work arounds that don't involve php array manipulation or listing out the values with 'as' I want on the query?
Last edited by garytheprogrammer on Wed May 05, 2010 10:47 pm; edited 1 time in total |
|
Back to top |
|
mohrt Administrator
Joined: 16 Apr 2003 Posts: 7368 Location: Lincoln Nebraska, USA
|
Posted: Wed Jul 28, 2004 6:43 pm Post subject: |
|
|
How is that an associative array? I see one array with two values. Now if your array looks something like:
Code: | array(
'table1.data1' => 'my data 1',
'table2.data2' => 'my data 2'
) |
Then you would be better off keeping the dots '.' out of the key names. Smarty isn't meant to be a tool that can take arbitrarily organized data and manipulate it any which way, you need to pass the data to Smarty in a format it can easily work with. Otherwise you can end up with impenetrable templates to say the least. |
|
Back to top |
|
garytheprogrammer Smarty Rookie
Joined: 28 Jul 2004 Posts: 5 Location: Eugene, Oregon
|
Posted: Wed Jul 28, 2004 7:13 pm Post subject: |
|
|
right, this is what i meant ... but you knew that:)
Code: |
array(
'table1.data1' => 'my data 1',
'table2.data2' => 'my data 2'
)
|
mohrt wrote:
Quote: | Then you would be better off keeping the dots '.' out of the key names. |
EEK! Since about 96.875% (approx.) of my arrays are from db's and since I spend the day trying to generalize my queries, this is a little silly. I AM going to join tables, and I DON'T want to have to specify endless specific fields from endless specific tables (I have frameworks for that stuff) I WILL end up with sql results that contain the key format TABLENAME.FIELDNAME... This isn't some 'crazy' format, it's SQL standard format... and I know I'm not the only one to join tables... or the only one to use MYSQL_ASSOC in my result fetching...
For now, I have been pre-parsing arrays like this to substitute the '.' with some other character in array keys. However, why not just tell smarty what the array seperator is, much like I tell it what string to parse template vars with?
I've taken a look at the var regex in Smarty... and, holy cow, it scared the daylights out of me. I thought the mad genius who wrote it might give the old college try into generalizing the array seperator with a DEFAULT value of '.'.
Gary
Last edited by garytheprogrammer on Wed May 05, 2010 10:47 pm; edited 1 time in total |
|
Back to top |
|
boots Administrator
Joined: 16 Apr 2003 Posts: 5611 Location: Toronto, Canada
|
Posted: Wed Jul 28, 2004 7:14 pm Post subject: |
|
|
Quote: | select * from table1 left join table2 on parameter1 where parameter2 |
This is JUNK in my beloved SQL Select * is the root of all (well, some) evil.
There are no workarounds that I know of that don't use AS (at least that are consistent across databases). Typically, as long as field names don't clash between the two tables, the table name is not appended. If you are bent on * then it is often the case that you need * from only one of the tables and specific fields (or none) from the other table:
select table1.*, table2.uniquefield from table1 left join table2 on parameter1 where parameter2
This often gets around field name collisions. Laziness is a coders virtue but sometimes also a bane. |
|
Back to top |
|
garytheprogrammer Smarty Rookie
Joined: 28 Jul 2004 Posts: 5 Location: Eugene, Oregon
|
Posted: Wed Jul 28, 2004 7:35 pm Post subject: |
|
|
boots,
pretend this is my sql syntax:
Code: |
select table1.id as table1id, table2.id as table2id from table1 left join table2 on parameter1 where parameter2
|
Right, then I must come up with a general rule for what to name fields on collisions. I need to generalize because my pages are extraordinarily dynamic and the php and smarty pages tend to be very seperated from the logic of the sql arrays passed into them. In fact, even the array name themselves are specified dynamically.
Believe me I have over 20 DB's on four platforms in three DB formats to deal with... there are PLENTY of namespace collisions between db joins Of course, I have already done the 'as' solution many times on many queries.
However, this morning I was in the throes of porting some derelict pages over to Smarty and didn't want to 'refactor' the programming. just use what the DB's throw at me. Come on, someone wrote that regex and must retain some understanding of their diablolical work !
I suspect this is the culprit
Code: |
$this->_var_bracket_regexp = '\[\$?[\w\.]+\]';
$this->_dvar_guts_regexp = '\w+(?:' . $this->_var_bracket_regexp . ')*(?:\.\$?\w+(?:' . $this->_var_bracket_regexp . ')*)*';
|
it's that darn \. again
Last edited by garytheprogrammer on Wed May 05, 2010 10:47 pm; edited 1 time in total |
|
Back to top |
|
mohrt Administrator
Joined: 16 Apr 2003 Posts: 7368 Location: Lincoln Nebraska, USA
|
Posted: Wed Jul 28, 2004 7:55 pm Post subject: |
|
|
Mucking with the Smarty tokenizer can be hazardous to your health. Maybe a class extention that "smartifies" the array keys upon assignment? |
|
Back to top |
|
boots Administrator
Joined: 16 Apr 2003 Posts: 5611 Location: Toronto, Canada
|
Posted: Wed Jul 28, 2004 7:56 pm Post subject: |
|
|
If I am following this, in your case, table.field is the actual field name and you can't avoid that easily. Meanwhile, Smarty sees table.fieldname as an array key access. So you have been renaming the fields to table_fieldname, which is what is usually suggested. The other common suggestion is to use an intermediate eg: {assign var=temp value="table.field"} {$arr.$temp} or {$arr[$temp]}
Now it occurs to me that if you are engendering so much dynamic effects that your template designer won't know the key names in advance either. So they aren't likely to be using the literal names anyhow. Contrariwise, if you are already using dynamic array names, why not go whole-hog on that idea and map your dynamic fields to unique names? With a map and a bit of code, you can autogenerate the AS portions of the queries.
However, it sounds like you are asking for a config option for the value of the array separator (instead of Smarty's assumed dot separator). I wouldn't count on that one happening |
|
Back to top |
|
garytheprogrammer Smarty Rookie
Joined: 28 Jul 2004 Posts: 5 Location: Eugene, Oregon
|
Posted: Wed Jul 28, 2004 9:00 pm Post subject: |
|
|
Boots,
Quote: | However, it sounds like you are asking for a config option for the value of the array separator (instead of Smarty's assumed dot separator). I wouldn't count on that one happening |
Yep, ah, well I gave it a shot... if I hack up a decent solution I'll pass it along.
BTW - I build some templates dynamically depending on the whim of how my management wants their intranet to look this week
They get to choose parameters for their local department sites... so I can't really know what exactly the little devils are up to... |
|
Back to top |
|
|