output of data from mysqltcl ??

[slashem]

I've finally gotten the mysqltcl to work.. but I'm not so good at using it..
I can make a query, but can't really get the output of it..

the dbase contains two tables.. (see tablenames with example input..)

Code: Select all

table: listing
sid shortname longname    propertie1 propertie2
1   sth       something   long       heavy

table: goods
sid    goodname
1          pb
1          ag

the kind of output I wanna be able to make is something like:
1st line> item: sth (something) properties: long, heavy
2nd line>goods: pb ag

The BOLD things, are the ones that have to be retrieved from the dbase..

The query I now use is:

Code: Select all

set db [mysqlconnect -host localhost]
mysqluse $db slashem
set outp [mysqlsel $db "select shortname, longname, propertie1, propertie2, goodname from listing, goods where shortname = 'sth' 
and where listing.sid == goods.sid"]

I executed the query in the mysql shell and the kind of table you get from it is:

Code: Select all

+-----------+-----------------------+---------+--------+-----------+
| shortname | longname              | prop1   | prop2  | gname     |
+-----------+-----------------------+---------+--------+-----------+
| sth       | something             | long    |  heavy | PB        |
| sth       | something             | long    |  heavy | AG        |
| sth       | something             | long    |  heavy | PRL       |

so, the query works, and gets me my info.
All I have a problem with is extracting the info out of the $outp var
..
I've been messing around with stuff like
mysqlmap $db {shortname longname ... gname} {lappend result $shortname $longname .. $gname}
putserv "PRIVMSG $chan :$shortname $longname ... $gname"
but I can't build up the correct output..

Is there anyone who would be willing to make me understand how the retrieving of the data works ? plz ?

thx in advance

[ppslim]

The query you are using is not suitable for the information you require.

In a production environment, this would yeild more than twice the amount of data actualy required.

For this, you should use 2 queries, one you extract the information fromt he first table, and a second to extract the required lines form the second table based on the id returned by the first query.

[slashem]

hmm, true
Using two queries now..

set info [join [mysqlsel $db "select sid, shortname, prop1, prop2, prop3, longname from listing where shortname = '$blub'" -list]]

for the first..
I can access each of the different things through [lindex $info 5]
I tried it without that join and then I couldn't access them.. I'm now using join, but don't really know why.. I thought join was to go from list->string.. but I can access them afterwards with lindex, which is a listcommand if I'm not mistaken..

for the second I use:
set goods [join [mysqlsel $db "select goodname from goods where sid = '[lindex $siteinfo 0]'"]]

This one gives me a problem though..
when I output $goods I get "6" .. <- number of rows..

my question now is, how do I make a while loop or something that gives me the name of each good ?