###############################################################
###
##
## 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 parameters
set 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_search
bind pub "m|ov" !db_version fl_version
set help_search "!db_search"
# a user with this flag(s) can't use the script
set 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_1> [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_1> \[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
}
### CONF ###
# search command
set db(cmd) "!blabla"
# channel flag
set db(flag) "flagbla"
# max results
set db(results) "3"
# DB INFO
set db(host) "localhost"
set db(user) "user"
set db(pass) "pass"
set db(name) "dbname"
set db(table) "tablename"
# END CONF
bind pub -|- $db(cmd) db_search
setudef 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) <name>"
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
Last edited by w00f on Sun Oct 07, 2007 1:17 pm, edited 2 times in total.
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?
