asterisk users - Nov 2005 - Select multiple columns from MYSQL cmd...

I have read on the wiki the many howto's to select data using the MYSQL
command. I would like to select multiple columns from a table using the
MYSQL command, however, it will only fetch one at a time.

I have tried the code to select using the GOTO(3) - (refereneced in the
wiki) - to fetch if more data, however, i would have to keep track of a
counter, and if the counter is now =2, then that column variable needs to
be set with the value that came out of the database.

Does someone have some code that does this process? Or are you all using
an AGI script?

thanks...

In article <1907.129.46.90.197.1132596037.squirrel@mail.itsngroup.com>,
Ben Higley <pbx@itsngroup.com> wrote:
> I have read on the wiki the many howto's to select data using the MYSQL
> command. I would like to select multiple columns from a table using the
> MYSQL command, however, it will only fetch one at a time.
You just need to provide multiple variables in the Fetch command to
receive the columns.
> I have tried the code to select using the GOTO(3) - (refereneced in the
> wiki) - to fetch if more data, however, i would have to keep track of a
> counter, and if the counter is now =2, then that column variable needs to
> be set with the value that came out of the database.
> 
> Does someone have some code that does this process? Or are you all using
> an AGI script?
I'm not familiar with the wiki example, but here is an extract from the
extensions.conf of one of my systems that illustrates the technique, by
fetching each inserted record again to write to a backup file:

exten => h,1,MYSQL(Connect conn localhost username password database)
exten => h,2,MYSQL(Query res ${conn} 'INSERT INTO
calls(callerid,calltime,ddi)
VALUES(\'${CALLERIDNUM}\',NOW(),\'${DDI}\')')
exten => h,3,MYSQL(Query res ${conn} 'SELECT
call_id,callerid,calltime,ddi FROM calls WHERE call_id=LAST_INSERT_ID()')
exten => h,4,MYSQL(Fetch fid ${res} call_id callerid calltime ddi)
exten => h,5,MYSQL(Clear ${res})
exten => h,6,MYSQL(Disconnect ${conn})
exten => h,7,System(/bin/echo
"${call_id}','${callerid}','${calltime}','${ddi}"
>>/tmp/calls.csv)

Hope this helps!

Cheers
Tony
-- 
Tony Mountifield
Work: tony@softins.co.uk - http://www.softins.co.uk
Play: tony@mountifield.org - http://tony.mountifield.org