| View previous topic :: View next topic |
| Author |
Message |
flyingbaobab
Voice
Joined: 22 Sep 2006
Posts: 8
Location: Europe
|
 Posted: Sun Sep 24, 2006 6:32 pm Post subject: stuck with tcl update query for mysql DB |
 |
|
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 -> int
city -> varchar
timestamp |
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 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: | 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 |
|
| Back to top |
|
 |
FossilAtrum
Voice
Joined: 27 Sep 2006
Posts: 2
|
| Posted: Wed Sep 27, 2006 2:46 am Post subject: |
|
|
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 |
|
| Back to top |
|
|
|
|
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
|
|
FAQ
Search
Memberlist
Usergroups
Register
Profile
Log in to check your private messages
Log in
