egghelp.org community Forum Index
[ egghelp.org home | forum home ]		 egghelp.org community
Discussion of eggdrop bots, shell accounts and tcl scripts.
 
 FAQFAQ   SearchSearch   MemberlistMemberlist   UsergroupsUsergroups   RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 

tcl - mysql - qry / output formated to channel

 
Post new topic   Reply to topic    egghelp.org community Forum Index -> Script Requests
View previous topic :: View next topic  
Author Message
Cidem
Voice


Joined: 07 Jun 2007
Posts: 2
PostPosted: Thu Jun 07, 2007 4:58 pm    Post subject: tcl - mysql - qry / output formated to channel Reply with quote
hello there,

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

Code:
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:
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
Back to top
View user's profile Send private message
w00f
Halfop


Joined: 04 Oct 2006
Posts: 49
PostPosted: Fri Jun 08, 2007 1:51 pm    Post subject: Reply with quote
check this topic.

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

~w00f
Back to top
View user's profile Send private message
Cidem
Voice


Joined: 07 Jun 2007
Posts: 2
PostPosted: Fri Jun 08, 2007 5:39 pm    Post subject: Reply with quote
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 Sad
Back to top
View user's profile Send private message
w00f
Halfop


Joined: 04 Oct 2006
Posts: 49
PostPosted: Fri Jun 08, 2007 6:46 pm    Post subject: Reply with quote
Code:

## 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
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    egghelp.org community Forum Index -> Script Requests All times are GMT - 4 Hours
Page 1 of 1

 
Jump to:  
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


Forum hosting provided by Reverse.net

Powered by phpBB © 2001, 2005 phpBB Group
subGreen style by ktauber