[Asterisk-Users] Re: Select multiple columns from MYSQL cmd...

[Tony Mountifield]

In article <1907.129.46.90.197.1132596037.squirrel at mail.itsngroup.com>,
Ben Higley <pbx at 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 at softins.co.uk - http://www.softins.co.uk
Play: tony at mountifield.org - http://tony.mountifield.org