stuck with tcl update query for mysql DB

2006-09-27T02:46:26-04:00

flyingboabab, at first glance, it appears that your problem is with your sql-query, and not so much the rest of the code.

Rather try something like the following:

Code:

  set text [mysqlescape $text]  set sql "REPLACE DELAYED prices SET price='$text' WHERE city ='NY' "

I hope this helps.
FossilAtrum

Statistics: Posted by FossilAtrum — Wed Sep 27, 2006 2:46 am

stuck with tcl update query for mysql DB

2006-09-24T18:32:04-04:00

I'm working on a script that listens to 8 different commands (!pa int_valueA !pb int_valueB !pc int_valueC ...) that users put in a channel. The only thing that my scripts needs to do is add it to a mysql DB. The front-end is a php script that does some fancy stuff with the data.

My DB looks like

Code:

TABLE `prices`id -> int -> auto increment - key price -> intcity -> varchartimestamp

What works is adding each new entry in the #channel into mysql with following code

Code:

proc pa { nick host handle channel text } {some basic checkingset sql "INSERT INTO prices VALUES(null, "  set text [mysqlescape $text]  append sql "'$text', "  append sql "'therightcity', "  append sql "'[clock seconds]')"  putloglev d * "Busy: executing $sql"  set result [mysqlexec $db_handle $sql]

In fact I have 8 fixed values/records for the city-field. And since the price-field should be changed every 30m by user input, I was thinking its better to stick to my 8 records (instead of letting people add new database entries) and set a cronjob to reset the price field. (works fine, no problem this far).

What I want to achieve now, is that my tcl script updates the proper price for each city (based on the command input from a user) instead of adding a new DB entry.

Example:
3h00, prices are being reset, db record for city New York looks like 1 - 0 - NY - 0
User types !pny 1234
Script should Update the proper record in the mysql db and it should look like 1 - 1234 - NY - unixtimestamp.

And this is where i have troubles: I've changed the above 'insert statement' to and update statement, but without any luck.
The modified code I tried to use was:

Code:

set sql "UPDATE prices SET (null, "  set text [mysqlescape $text]  append sql "'$text', "  append sql "'NY', "  append sql "'[clock seconds]') WHERE city ='NY'"

This fails and I don't have the tcl skills to solve this one myself. Any advice would be welcome, since my research on the web didn't result in similar examples.

Thx in advance
FB

Statistics: Posted by flyingbaobab — Sun Sep 24, 2006 6:32 pm

egghelp/eggheads community

stuck with tcl update query for mysql DB

stuck with tcl update query for mysql DB