stuck with tcl update query for mysql DB

[flyingbaobab]

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: Select all

TABLE `prices`
id -> int -> auto increment - key 
price -> int
city -> varchar
timestamp

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

Code: Select all

proc pa { nick host handle channel text } {
some basic checking
set 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: Select all

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

[FossilAtrum]

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: Select all

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

I hope this helps.
FossilAtrum