| View previous topic :: View next topic |
| Author |
Message |
makekake
Voice
Joined: 04 Oct 2007
Posts: 3
|
 Posted: Thu Oct 04, 2007 2:10 pm Post subject: Mysql query help |
 |
|
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 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
}
|
|
|
| Back to top |
|
 |
w00f
Halfop
Joined: 04 Oct 2006
Posts: 49
|
| Posted: Sat Oct 06, 2007 9:23 am Post subject: |
|
|
| Code: |
### 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 |
|
| Back to top |
|
|
makekake
Voice
Joined: 04 Oct 2007
Posts: 3
|
| Posted: Sun Oct 07, 2007 4:53 am Post subject: |
|
|
Thanks!
That's working fine.
owner_name may have more than one same result. How i can show them too? |
|
| Back to top |
|
|
w00f
Halfop
Joined: 04 Oct 2006
Posts: 49
|
| Posted: Sun Oct 07, 2007 8:20 am Post subject: |
|
|
check the code again.
* added db(results) , will limit the rows in the sql query. |
|
| Back to top |
|
|
makekake
Voice
Joined: 04 Oct 2007
Posts: 3
|
| Posted: Sun Oct 07, 2007 11:45 am Post subject: |
|
|
| w00f wrote: | 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" |
|
| Back to top |
|
|
w00f
Halfop
Joined: 04 Oct 2006
Posts: 49
|
| Posted: Sun Oct 07, 2007 1:19 pm Post subject: |
|
|
it's ok now , $sql(query) instead of $query in while >.<
~w00f |
|
| Back to top |
|
|
coke
Voice
Joined: 09 Oct 2010
Posts: 7
|
| Posted: Sat Nov 06, 2010 3:40 pm Post subject: |
|
|
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" |
|
| Back to top |
|
|
caesar
Mint Rubber
Joined: 14 Oct 2001
Posts: 3741
Location: Mint Factory
|
| Posted: Sat Nov 06, 2010 3:56 pm Post subject: |
|
|
I think | Code: | | string map {1 blue 2 red 3 black 4 yellow 5 green 6 white} $text |
should do it.
_________________
Once the game is over, the king and the pawn go back in the same box. |
|
| Back to top |
|
|
coke
Voice
Joined: 09 Oct 2010
Posts: 7
|
| Posted: Sat Nov 06, 2010 4:11 pm Post subject: |
|
|
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 |
|
| Back to top |
|
|
speechles
Revered One
Joined: 26 Aug 2006
Posts: 1398
Location: emerald triangle, california (coastal redwoods)
|
| Posted: Sat Nov 06, 2010 4:17 pm Post subject: |
|
|
| Code: | | set color [string map [list 1 blue 2 red 3 black 4 yellow 5 green 6 white] [lindex $row 2]] |
_________________
speechles' eggdrop tcl archive |
|
| Back to top |
|
|
coke
Voice
Joined: 09 Oct 2010
Posts: 7
|
| Posted: Sat Nov 06, 2010 4:47 pm Post subject: |
|
|
| problem solved, thanks! |
|
| Back to top |
|
|
|
FAQ
Search
Memberlist
Usergroups
Register
Profile
Log in to check your private messages
Log in
