tcl - mysql - qry / output formated to channel

[Cidem]

hello there,

i am in need of help,
i need a script that outputs the result of an mysql qry:

Code: Select all

SELECT g.galaxy,g.system,g.planet,g.moon,a.allyname,p.playername,a.members FROM galaxy g LEFT JOIN players p ON (g.player_id=p.id) LEFT JOIN alliances a ON (p.alliance_id=a.id) LEFT JOIN notices n ON (g.player_id = n.player_id) LEFT JOIN reports r ON (g.galaxy = r.galaxy AND g.system = r.system AND g.planet = r.planet AND r.moon='false')  WHERE p.playername LIKE 'ilovecc'  AND (g.player_id > 0)   ORDER BY galaxy,system,planet ASC  LIMIT 0,20

result:

Code: Select all

galaxy 	system 	planet 	moon 	allyname 	playername 	members
1 	353 	8 	true 	FN 	ILoveCC 	25
2 	474 	11 	true 	FN 	ILoveCC 	25
3 	111 	4 	true 	FN 	ILoveCC 	25
4 	474 	4 	true 	FN 	ILoveCC 	25
5 	200 	4 	true 	FN 	ILoveCC 	25
6 	279 	4 	true 	FN 	ILoveCC 	25
7 	234 	4 	true 	FN 	ILoveCC 	25
8 	234 	4 	true 	FN 	ILoveCC 	25
9 	377 	7 	false 	FN 	ILoveCC 	25

to something like this on irc:

Coords of Player: ILoveCC - Ally: FN (25) : 1:353:8 [M] | 2:474:11 [M] | 3:111:4 [M]... and so on ..

"!pcoords IloveCC" should be the way one can request this ..from the channel

i hope someone is willing & capable to help me out with this cause i got no clue how to format that mysql output and bring it to irc ...


if you need additional infos ...pls ask ..
also pls excuse my bad written english

thx...

Cidem

[w00f]

check this topic.

change the cmd, db info, sql query, and use lappend to set the output inside the "while".

~w00f

[Cidem]

woot ...nice thx for the link...

ok i did the first parts you told me but if it comes to the lappend part i just dont know how to change this in the while part

[w00f]

Code: Select all

## CONF ##
# trigger to search
set srch(cmd) "!pcoords"

# SQL info
set sql(host) "IP"
set sql(user) "USER"
set sql(pass) "PASS-BLA"
set sql(db) "DATABASE-NAME"
set sql(port) "PORT"

## END CONF ##

package require mysqltcl
bind pub - $srch(cmd) pcoords

proc pcoords {nick host hand chan arg} {
   global sql
   if {[lindex $arg 0] == ""} { putquick "PRIVMSG $chan :\002Syntax\002: $srch(cmd) <user> " 
      return
   }

   set search [lindex $arg 0]
   set sql(handle) [mysqlconnect -host $sql(host) -user $sql(user) -password $sql(pass) -db $sql(db) -port $sql(port)]
   set query [::mysql::query $sql(handle) "SELECT g.galaxy,g.system,g.planet,g.moon,a.allyname,p.playername,a.members FROM galaxy g LEFT JOIN players p ON (g.player_id=p.id) LEFT JOIN alliances a ON (p.alliance_id=a.id) LEFT JOIN notices n ON (g.player_id = n.player_id) LEFT JOIN reports r ON (g.galaxy = r.galaxy AND g.system = r.system AND g.planet = r.planet AND r.moon='false') WHERE p.playername LIKE '$search'  AND (g.player_id > 0) ORDER BY galaxy,system,planet ASC  LIMIT 0,20"]

   if {[::mysql::result $query rows] < 1} {
       set output "User \002\00307$search\017 does not exist.."
   } else {
       set count 1
       while {[set row [::mysql::fetch $query]] != ""} {
       set galaxy [lindex $row 0]
       set system [lindex $row 1]
       set planet [lindex $row 2]
       set gotmoon [lindex $row 3]
       if {[string match -nocase $gotmoon "true"]} { set moon "\[M]" 
       } else { set moon "\[NO]" }
       set allyn [lindex $row 4]
       set playern [lindex $row 5]
       set members [lindex $row 6]
       
       if {$count == 1} { set output "\002Coords of Player\017: $playern \00314~\017\002 Ally\017: $allyn ($members) : $galaxy:$system:$planet $moon "
       incr count
       } else { [lappend output "\| $galaxy:$system:$planet $moon "] }
       }
   }

putquick "PRIVMSG $chan:$output "
::mysql::endquery $query
mysqlclose $sql(handle)

}


putlog "pcoords.tcl loaded" 

something like this.
untested ofc.

~w00f