[mv] [mv4] here's a tip for selecting from multiple SQL tables with MV4

[Mark Stosberg <mark@summersault.com>]

******    message to minivend-users from Mark Stosberg <mark@summersault.com>     ******


Hello! 

  Recently while developing with Minivend and SQL, I ran into a problem.
The usual way to display data in the SQL  tag is like this:

[sql-param field_name]

 but what if I select two tables and I want the same field name from
each? One solution is to get field with the array style call like:
[sql-param 0], but that's not as clear. 

I found a good solution to this issue while reading "Programming the
Perl DBI" this morning. The solution is actually rooted in SQL itself
rather than in Minivend. 

Here's an example selecting from two arbitrary databases:

[sql type=list sql="select user_auth.id AS user_auth_id, seller.id AS
seller_id 
	from user_auth, seller where user_auth.username = 'markjugg'
AND user_auth.id = seller.id"]

user_auth.id:	  [sql-param user_auth_id]<BR>
seller.id:      [sql-param seller_id]

[/sql] 


The trick is where I make an alias of the conflicting columns with:
"user_auth.id AS user_auth_id, seller.id AS seller_id"

This works in PostgreSQL. Other databases don't require the "AS" word,
and probably some others don't support aliasing at all. 

Perhaps a mention of this feature could make into the minivend docs as
well. :) 

Good luck and have fun,

   -mark

  <<-------------------------------------------------------------->>
personal website                <    Summersault Website Design
   http://mark.stosberg.com/     >       http://www.summersault.com/
-
To unsubscribe from the list, DO NOT REPLY to this message.  Instead, send
email with 'UNSUBSCRIBE minivend-users' in the body to Majordomo@minivend.com.
Archive of past messages: http://www.minivend.com/minivend/minivend-list