egghelp/eggheads community

Mysql query help

2010-11-06T16:47:41-04:00

problem solved, thanks!

Statistics: Posted by coke — Sat Nov 06, 2010 4:47 pm

Mysql query help

2010-11-06T16:17:49-04:00

Code:

set color [string map [list 1 blue 2 red 3 black 4 yellow 5 green 6 white] [lindex $row 2]]

Statistics: Posted by speechles — Sat Nov 06, 2010 4:17 pm

Mysql query help

2010-11-06T16:11:48-04:00

thanks for such a promt response!

could you be so kind to show me where to put it/how to use it?
i just achieved the result I wanted by creating a little pyramid of

Code:

set color [lindex $row 2]if {$color == "6"} {set color2 "white"} else {     if {$color == "5"}             set color2 ...

but that's not exactly the most elegant solution

Statistics: Posted by coke — Sat Nov 06, 2010 4:11 pm

Mysql query help

2010-11-06T15:56:19-04:00

I think

Code:

string map {1 blue 2 red 3 black 4 yellow 5 green 6 white} $text

should do it.

Statistics: Posted by caesar — Sat Nov 06, 2010 3:56 pm

Mysql query help

2010-11-06T15:40:47-04:00

Alright, I tried this script with my own query etc. and everything's just fine, but i.e. x (i.e. row 2) is stored in the DB as a number 1-6 that is normally processed by php on the server to display its actual meaning (x happens to specify team color, i.e. 3 = black).

How could I modify this script to fetch row 2 (so $x) from the db, then process it replacing a number with a corresponding color and displaying this color (and not the number) in the channel?

numbers and corresponding colors
1 = blue, 2 = red, 3 = black, 4 = yellow, 5 = green, 6 = white

As of now it works, for example, like this (after of course replacing $parameters with their hypothetical meaning):

putquick "PRIVMSG #mychan : pete kingston 3"
and i'd like it to look like
putquick "PRIVMSG #mychan : pete kingston black"

Statistics: Posted by coke — Sat Nov 06, 2010 3:40 pm

Mysql query help

2007-10-07T13:19:48-04:00

it's ok now , $sql(query) instead of $query in while >.<

~w00f

Statistics: Posted by w00f — Sun Oct 07, 2007 1:19 pm

Mysql query help

2007-10-07T11:45:17-04:00

check the code again.

* added db(results) , will limit the rows in the sql query.

i get "Tcl error [db_search]: can't read "query": no such variable"

Statistics: Posted by makekake — Sun Oct 07, 2007 11:45 am

Mysql query help

2007-10-07T08:20:51-04:00

check the code again.

* added db(results) , will limit the rows in the sql query.

Statistics: Posted by w00f — Sun Oct 07, 2007 8:20 am

Mysql query help

2007-10-07T04:53:20-04:00

Thanks!
That's working fine.
owner_name may have more than one same result. How i can show them too?

Statistics: Posted by makekake — Sun Oct 07, 2007 4:53 am

Mysql query help

2007-10-07T13:17:50-04:00

Code:

### CONF #### search commandset db(cmd) "!blabla"# channel flagset db(flag) "flagbla"# max resultsset db(results) "3"# DB INFOset db(host) "localhost"set db(user) "user"set db(pass) "pass"set db(name) "dbname"set db(table) "tablename"# END CONFbind pub -|- $db(cmd) db_searchsetudef flag $db(flag)proc db_search { nick host hand chan text} {  global db  if {![channel get $chan $db(flag)]} {    return 0  }  if {$text == ""} {    putquick "PRIVMSG $chan :\002Syntax:\017 $db(cmd) "    return 0  }  set search [lindex $text 0]  set sql(handle) [mysqlconnect -host $db(host) -user $db(user) -password $db(pass) -db $db(name)]  set sql(query) [::mysql::query $sql(handle) "SELECT owner_name,town_name,x,y,guild_name,population FROM `$db(table)` WHERE `owner_name` LIKE '$search' LIMIT $db(results) "]  if {[::mysql::result $sql(query) rows] < 1} {      putquick "PRIVMSG $chan :Sorry, No such owner \'\002$search\' "  } else {      while {[set row [::mysql::fetch $sql(query)]] != ""} {        set ownername [lindex $row 0]        set townname [lindex $row 1]        set x [lindex $row 2]        set y [lindex $row 3]        set guildname [lindex $row 4]        set population [lindex $row 5]        putquick "PRIVMSG $chan :\002\037Owner\017: $ownername \00314-\017 \002\037Town\017: $townname \00314-\017 \002\037X\017/\002\037Y\017: $x/$y \00314-\017 \002\037Guild\017: $guildname \00314-\017 \002\037Population\017: $population "    }   }  ::mysql::endquery $sql(query)  mysqlclose $sql(handle)}putlog "S4 DB Search by w00f loaded!"

try this , might help

~w00f

Statistics: Posted by w00f — Sat Oct 06, 2007 9:23 am

Mysql query help

2007-10-04T14:10:13-04:00

Hi,

I have database table called S4 and theres 7 fields:
x
y
race
town_name
owner_name
guild_name
populaion

i need script that search owner_name and input results to irc channel with this order:
owner_name town_name x y guild_name population

I use dbsearch.tcl, but i can't get right order.

Code:

###################################################################### dbsearch.tcl - (C) SevenRains (Corcalciuc V. Horia)##                              rain@sevenrains.ro#### A tcl script for eggdrop bots to search a specified## MySQL database by some fields.## Requires libmysqltcl.so!!#### Inspired from QuoteEngine.################################################################## define database parametersset dbhost "localhost"set dbuser ""set dbpass ""set dbname "Travian"# set-up the columns and the# table of the database we will# be searching.set parameter_1 "owner_name"set parameter_2 "town_name"set table_name "S4"# set dbsearch to the flag you want channels# to have that allow searching your database# then use .chanset #channel [+/-] dbsearch to# enable or disable individual channels.set chanflag "dbsearch"# bind commands are by default !db_search# and respectively !db_version in channel# window to search the database. users with# flags m, o and v are allowed to search.# set to "-|-" to allow everybody.bind pub "-|-" !s4 db_searchbind pub "m|ov" !db_version fl_versionset help_search "!db_search"# a user with this flag(s) can't use the scriptset quote_noflags "B|B"# maximum number of matches sent to channel before# sending the rest to the nick via /msg.set quote_chanmax 5### code starts here (no need to edit stuff below currently)set fl_version "0.1"set db_handle [mysqlconnect -host $dbhost -user $dbuser -password $dbpass -db $dbname]setudef flag $chanflag################################################################################# db_search#   Searches the database by two parameters, like this:#   !db_search  [parameter_2] using SQL wildcards.################################################################################proc db_search { nick host handle channel text} {  global db_handle php_page quote_noflags quote_chanmax chanflag parameter_1 parameter_2 table_name  set sub1 ""  set sub2 ""  set sub3 ""  if {![channel get $channel $chanflag]} {    return 0  }  if [matchattr $handle $quote_noflags] { return 0 }  if {$text == ""} {    puthelp "PRIVMSG $nick :Use: !db_search  \[parameter_2\]"    return 0  }  if {$sub1 == ""} {    set sql "SELECT * FROM $table_name WHERE $parameter_1 = '[mysqlescape $text]' ORDER BY RAND()"  } else {    set sql "SELECT * FROM $table_name WHERE $parameter_1 = '[mysqlescape $sub2]' $where_clause ORDER RAND()"  }  putlog "Query: $sql"  putloglev d * "QuoteEngine: executing $sql"  if {[mysqlsel $db_handle $sql] > 0} {    set count 0    mysqlmap $db_handle {owner_name town_name guild_name x y race population} {       if {$count == $quote_chanmax} {        puthelp "PRIVMSG $nick :Rest of matches for your search '$text' follow in private:"      }      if {$count < 5} {        puthelp "PRIVMSG $channel :\ \002X|Y:\002$owner_name $town_name \002Rotu:\002$guild_name \002Kylä:\002$x \002Nimi:\002$y \002Liitto:\002$race \002As$      } else {        puthelp "PRIVMSG $nick :\ \002X|Y:\002$owner_name $town_name \002Rotu:\002$guild_name \002Kylä:\002$x \002Nimi:\002$y \002Liitto:\002$race \002Asuka$      }      incr count    }    set remaining [mysqlresult $db_handle rows?]    if {$remaining > 0} {      regsub "#" $channel "" chan      #puthelp "PRIVMSG $nick :Plus $remaining other matches"    } else {      if {$count == 1} {        #puthelp "PRIVMSG $nick :(All of 1 match)"      } else {        #puthelp "PRIVMSG $nick :(All of $count matches)"      }    }  } else {    #puthelp "PRIVMSG $nick :No matches"  }}################################################################################## fl_version##   Gives the version of the script################################################################################proc fl_version { nick host handle channel text } {  global fl_version quote_noflags  if [matchattr $handle $quote_noflags] { return 0 }  puthelp "PRIVMSG $nick :Database search version $fl_version by SevenRains"  return 0}

Statistics: Posted by makekake — Thu Oct 04, 2007 2:10 pm