This is the new home of the egghelp.org community forum.
All data has been migrated (including user logins/passwords) to a new phpBB version.


For more information, see this announcement post. Click the X in the top right-corner of this box to dismiss this message.

Mysql query help

Support & discussion of released scripts, and announcements of new releases.
Post Reply
m
makekake
Voice
Posts: 3
Joined: Thu Oct 04, 2007 2:00 pm

Mysql query help

Post by makekake »

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: Select all

###############################################################
###
##
## 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
}
w
w00f
Halfop
Posts: 49
Joined: Wed Oct 04, 2006 6:50 pm

Post by w00f »

Code: Select all

### 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.
m
makekake
Voice
Posts: 3
Joined: Thu Oct 04, 2007 2:00 pm

Post by makekake »

Thanks!
That's working fine.
owner_name may have more than one same result. How i can show them too?
w
w00f
Halfop
Posts: 49
Joined: Wed Oct 04, 2006 6:50 pm

Post by w00f »

check the code again.

* added db(results) , will limit the rows in the sql query.
m
makekake
Voice
Posts: 3
Joined: Thu Oct 04, 2007 2:00 pm

Post by makekake »

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"
w
w00f
Halfop
Posts: 49
Joined: Wed Oct 04, 2006 6:50 pm

Post by w00f »

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

~w00f
c
coke
Voice
Posts: 7
Joined: Sat Oct 09, 2010 1:10 pm

Post by coke »

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"
User avatar
caesar
Mint Rubber
Posts: 3778
Joined: Sun Oct 14, 2001 8:00 pm
Location: Mint Factory

Post by caesar »

I think

Code: Select all

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.
c
coke
Voice
Posts: 7
Joined: Sat Oct 09, 2010 1:10 pm

Post by coke »

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: Select all

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
User avatar
speechles
Revered One
Posts: 1398
Joined: Sat Aug 26, 2006 10:19 pm
Location: emerald triangle, california (coastal redwoods)

Post by speechles »

Code: Select all

set color [string map [list 1 blue 2 red 3 black 4 yellow 5 green 6 white] [lindex $row 2]]
c
coke
Voice
Posts: 7
Joined: Sat Oct 09, 2010 1:10 pm

Post by coke »

problem solved, thanks!
Post Reply