View previous topic :: View next topic |
Author |
Message |
V Smarty Rookie
Joined: 02 May 2003 Posts: 9 Location: Barcelona (Spain)
|
Posted: Fri May 02, 2003 12:02 pm Post subject: html_options from SELECT query |
|
|
I've found myself several times in this situation:
I do a Code: | SELECT id, name FROM rooms |
... and want this result to be the base for a <select>
So far I've had to do this by assigning the id's column to $ids and the names' column to $names and then using html_options like this:
Code: | {html_options values=$ids output=$names} |
Wouldn't it be easier if html_options had support for result queries where the 1st column was the "values" and the 2nd one the "outputs"?
Correct me if I'm wrong but the way I use now is the only way possible to achieve this.
Greets,
Víctor |
|
Back to top |
|
katana Smarty Rookie
Joined: 17 Apr 2003 Posts: 26 Location: France
|
Posted: Sat May 03, 2003 8:11 am Post subject: |
|
|
Nah, it's not what Smarty was written for. If you want such a feature, write a plugin
Maybe you should try PEAR:B or PEAR::MDB, they provide methods that interface perfectly with Smarty:
Code: | $tpl->assign('OPTIONS', $db->getAssoc('SELECT id, name FROM rooms')); |
then in the template
Code: | <select name="foo">{html_options options=$OPTIONS}</select> |
|
|
Back to top |
|
V Smarty Rookie
Joined: 02 May 2003 Posts: 9 Location: Barcelona (Spain)
|
Posted: Sat May 03, 2003 9:06 am Post subject: |
|
|
Amf, actually I'm using ez_sql which is pretty much the same you're using, and there's something similar: Code: | $rooms = $db->get_results('SELECT id, name FROM rooms', ARRAY_A); |
However results in:
Code: | $rooms[0] = array ('id' => '1', 'name' => 'room1');
$rooms[1] = array ('id' => '2', 'name' => 'room2');
|
Which is not the associative array I need
I'll try mailing the author.
Thanks |
|
Back to top |
|
jv22 Smarty n00b
Joined: 03 May 2003 Posts: 1
|
Posted: Sat May 03, 2003 12:26 pm Post subject: |
|
|
Hi,
I think this will do what you are looking for in ezsql.
Code: | $ids = $db->get_col('select id, name from rooms'); // <-- populate result cache, get col 0 as an array
$names = $db->get_col(null,1); // <-- get column 1 from cache |
Let me know if it works.
Note. It still only uses one query.
Cheers,
Justin - http://ezsql.justinvincent.com |
|
Back to top |
|
V Smarty Rookie
Joined: 02 May 2003 Posts: 9 Location: Barcelona (Spain)
|
Posted: Sat May 03, 2003 1:21 pm Post subject: |
|
|
Hi,
Yes, that's the way I'm doing it so far and it works, but I was just wondering if it was possible to do it just in one line, right like katana said it's possible with PEAR:DB. Imho I think it looks more clear that way.
The resulting array should be something like:
Code: | array
{
'1'=>'room one',
'2'=>'room two'
} |
Thanks for your reply,
Cheers |
|
Back to top |
|
mohrt Administrator
Joined: 16 Apr 2003 Posts: 7368 Location: Lincoln Nebraska, USA
|
Posted: Sat May 03, 2003 3:59 pm Post subject: |
|
|
You can pass an associative array to html_options for values and output, check out the "options" attribute. |
|
Back to top |
|
V Smarty Rookie
Joined: 02 May 2003 Posts: 9 Location: Barcelona (Spain)
|
Posted: Sat May 03, 2003 4:38 pm Post subject: |
|
|
I know, but using ezsql the resulting associative array is 'different' to what the options attribute expects:
Code: | $rooms = array
{
'1'=>'room one',
'2'=>'room two'
} |
With ezsql you get this:
Code: | $rooms[0] = array ('id' => '1', 'name' => 'room1');
$rooms[1] = array ('id' => '2', 'name' => 'room2');
|
So I guess it's either a matter of adding a new attribute to html_options or another kind of array returned by ezsql... |
|
Back to top |
|
messju Administrator
Joined: 16 Apr 2003 Posts: 3336 Location: Oldenburg, Germany
|
Posted: Sat May 03, 2003 4:51 pm Post subject: |
|
|
V wrote: | I know, but using ezsql the resulting associative array is 'different' to what the options attribute expects:
Code: | $rooms = array
{
'1'=>'room one',
'2'=>'room two'
} |
With ezsql you get this:
Code: | $rooms[0] = array ('id' => '1', 'name' => 'room1');
$rooms[1] = array ('id' => '2', 'name' => 'room2');
|
So I guess it's either a matter of adding a new attribute to html_options or another kind of array returned by ezsql... |
remap your array to suite html_option's needs:
Code: |
$options = array();
foreach ($rooms as $room) {$options[$room['id']]=$room['name']; }
|
html_options wasn't done with ezsql (of which i never heard before) in mind.
have fun
messju |
|
Back to top |
|
V Smarty Rookie
Joined: 02 May 2003 Posts: 9 Location: Barcelona (Spain)
|
Posted: Sat May 03, 2003 5:06 pm Post subject: |
|
|
messju wrote: | html_options wasn't done with ezsql (of which i never heard before) in mind. |
I've been using both of them together with fairly good results
And I already managed to get the options attribute working, I was only asking for a built-in way to do it without having to 'hard-code' or adding another line.
Cheers, |
|
Back to top |
|
boots Administrator
Joined: 16 Apr 2003 Posts: 5611 Location: Toronto, Canada
|
Posted: Sat May 03, 2003 11:04 pm Post subject: |
|
|
messju's code will work, but it is kinda unfortunate since you end up mangling your returned data into the right format. That means loops and re-writing only to have it re-looped in HTML_OPTIONS. Also, your eZ library probably went to the trouble of looping and writing the precious array you are now trying to re-write. Ouch.
Without knowing anything about eZ, I'd say your best choice is to get eZ to output a more "standard" format . If that is not possible (if its open-source, anything is possible) then your next best bet is to rewrite HTML_OPTIONS. Perhaps a new parameter (data_style?) that reacts to eZ, reading the incoming array appropriately while still allowing for other formats to be used.
Like I said, I know nothing of eZ, but that returned array looks very wasteful repeating key names on each record.
Good Luck! |
|
Back to top |
|
V Smarty Rookie
Joined: 02 May 2003 Posts: 9 Location: Barcelona (Spain)
|
Posted: Sat May 03, 2003 11:32 pm Post subject: |
|
|
boots wrote: | Like I said, I know nothing of eZ, but that returned array looks very wasteful repeating key names on each record. |
Not too loud ... ezSQL's coder is up here (jv22)! Just kidding
The returned array may be useful in some cases but certainly not in this one, so I guess the best solution would be adding a new ARRAY_X feature to the get_results function in ezSQL which returned an associative array like expected by 'options'. So now this is ezSQL related, not Smarty anymore. If you haven't tried ezSQL I encourage you to do so, it's a pretty good timesaver.
Thank you all for replying. |
|
Back to top |
|
boots Administrator
Joined: 16 Apr 2003 Posts: 5611 Location: Toronto, Canada
|
Posted: Sun May 04, 2003 12:42 am Post subject: |
|
|
Quote: | Not too loud ... ezSQL's coder is up here (jv22)! |
ha ha! I didn't mean that as a slight--I figured eZ had more condensed formats available and there are certainly classes of programmers that will find that structure intuitive. I WAS trying to point out that sometimes you have to examine the entire data chain to understand where the "fix" is best made.
Post back the update when it is made, or the work-around that you decided on. It will likely be useful to others.
I would try eZ but I am happy with the dbx module for now. Nothing fancy--I doubt it can compete feature-wise with other wrappers like Pear::DB, ADODB or eZ, but as a binary module, it is hard to beat performance wise!
For what its worth, the author of ADODB benchmarked some alternatives here. His ADOdbext is an experimental extension module that he is working on (similar to dbx) and shouldn't be used in production yet. His tests are also fairly contrived and only minimally indicative of true performance, if you ask me. Its plain that dbx is currently the fastest available option and as your return sets grow in rows (or fields) you will likely see a wider gap.
One last point: most people don't need abstraction layers for their db work because in reality, the db never changes during the lifetime of their application and the size of their application and/or the structuring of the data access modules likely means that a change will have minimal effect on PHP code. The biggest challenge with an abstraction wrapper isn't talking to the drivers--its making sure the SQL is compatable between db types and virtually all solutions don't do this, at least fully. That means that you're going to end up rewriting SQL queries and DDL no matter which abstraction layer you choose. Metabase promises to improve that somewhat, but it is so slow (kinda crude, too) compared to the alternatives, that it really defeats itself. ADODB does do this for various common items, but still leaves a lot undone.
Pear::DB, despite all the hype, is too slow and too bloaty to be seriously considered. I want someone to take me to task on that statement because I am really scratching my head over the whole Pear thing right now. Its basically in the same state it has been in for a year and a half with focus not on code, but some mystical framework which still hasn't been decided. A module / code / class library is a great idea, I just feel that Pear is ill-conceived, at least from my current exposure. I know a lot of people are using it and are happy with it. I'd like to hear your success stories (failures too!).
The only reason I use an abstraction layer at all is so that I DON'T have to remember how to use each native driver--my mind is already littered with too many syntaxes and frameworks as it is!
2c |
|
Back to top |
|
|