Mysql: adding to a database

[SmokeyOne]

I did use the search feature, but it looked like there wasn't alot of info on what I'm wanting help with.

I'm building a release annouce script using a mysql db, I have mysqltcl installed and its running fine, but I'm haveing trouble getting the phraseing down.

I have a table with 3 rows in them

name, format, date

using a switch command with the !rl as the main trigger, it used like this
!rl add Matrix Reloaded SVCD
i think i can use currenttime or another command on that field to make it add it with the current date, but the code i was thinking for it was this

proc rls_add {nick uhost handle channel arg} {
global net
set table "$net(release_table)[string range $channel 1 end]"
set net(conn) [mysqlconnect -u $net(sql_name) -p $net(sql_pass) -db $net(sql_db)]
mysqlexec $net(conn) "insert into '$table' ( 'name' , 'format' , 'date')
mysqlcose $net(conn)
putserv "PRIVMSG $channel :\[\002 New Release\002\]: "
}
and this is pretty much where I'm getting stuck at. not sure how i want to phrase this, but i figured this would be the best place to ask. Hope someone can help

[Ofloo]

Here is some code samples hope u find what u need insert is in there table name is test into database "database"

I gues there might be a beter way there always is but this works so good sample i gues

Code: Select all

set mysql(host) "192.168.0.1"
set mysql(port) "3306"
set mysql(user) "somelogin"
set mysql(pass) "somepass"
set mysql(data) "database"

# this is the verison of the mysql tcl lib not the version of the mysql
# server, if u got a newer lib plz adjust this or it won't work ;)
set mysql(vers) "2.30"

# Load the mysqltcl lib you need this lib in order to make it work.
package require mysqltcl $mysql(vers)

# Mysql connection is made this has not been connected to any db it just
# is connected to the server
set mysql(conn) [mysqlconnect -host $mysql(host) -port $mysql(port) -user $mysql(user) -password $mysql(pass)]

# MySQL lib samples to test how it works.

# This item will insert data to table test !insert <title> <data>

bind pub - !insert insert:pub

proc insert:pub {nick uhost hand chan arg} {
  # inserting mysql array
  global mysql
  
  # Set data variables.
  set title [lindex $arg 0]
  set data [join [lrange $arg 1 end]]


  # Check if there is data pressent to insert.
  if {![string match {} [lindex $arg 1]]} {

    # Selecting a valid db.
    mysqluse $mysql(conn) $mysql(data)

    # Inserting data to the db
    mysqlexec $mysql(conn) "INSERT INTO test (test_title,test_data) VALUES ('$title','$data');"
  
    # If a proc is broken when an error occours like inserting data into a 
    # db, then this could be a verification of a succesfull query
    putserv "PRIVMSG $chan :Query ended."
  } else {
    putserv "PRIVMSG $chan :Syntax error."
  }
}

# This item will read data from table test

bind pub - !select select:pub

proc select:pub {nick uhost hand chan arg} {
  # inserting mysql array
  global mysql

  # Selecting a valid db.
  mysqluse $mysql(conn) $mysql(data)

  # reading data from table with a loop & outputting it line by line.
  foreach query [mysqlsel $mysql(conn) "SELECT test_title, test_data FROM test ORDER BY test_title, test_data;" -list] {
    putserv "PRIVMSG $chan :Title: [lindex $query 0] Data: [join [lrange $query 1 end]]"
  }

  # If a proc is broken when an error occours like inserting data into a 
  # db, then this could be a verification of a succesfull query
  putserv "PRIVMSG $chan :Query ended."
}

[SmokeyOne]

thanks, I'll give it a run and see what happends.

[SmokeyOne]

okay a question would be, to if i set a date filed from within mysql, do I have to supply the date or is there a command to make it insert the current date for me?.. also from your example, i would think if i wanted to add into more field i could simply do this right ?

mysqlexec #mysql(conn) "instert into release (release_title,release_number, release_date) values ('$title' , '$data', '$number', '$date');"

[Ofloo]

date u can reformat u get date from clock seconds

then u do clock format .... options check tcl manual for more info

also i do not use date like this i always use clock seconds format insert this into a db cause like this u can read it with almost any langue for example u could read it with mirc or with php and reformat it to the right format or to any format u prefer just use int or decimal or something .. for the field property

yes u have to supply the date that is inserted mysql will only check the format witch is 000-00-00 year-month-day

[SmokeyOne]

okay so using a utime function to insert the unix code into the table, then just convert it... seems to be better than using the DATE filed in mysql like that.

also I was looking through the mysqltcl man pages, and found these set of commands,

mysqlquery, mysqlnext, mysqlmap, mysqlseek, mysqlcol, and mysqlresult.

Now from the examples I see, wouldn't this be a better way to access in and out of the query?, aso for searching and displaying the info back?,

I would seem that some of these commands could be used a little more, I'm pretty much learning the commands and this at the sametime, so the process of which I want to do this is slow.

[Ofloo]

check this

I gues there might be a beter way there always is but this works so good sample i gues

and

the querys basicly its all the same they just got a different perphes
so i suggest u read what they mean what there used for and use what u think is best for the thing that u are about to do .. if u are gone select data you should be able to do this with mysqlexec as well but there is an option called mysqlsel so i gues its beter to use this cause you can create a list or something... its made for select ..

by basicly all the same i mean they don't act the same but they way you use them in ur script is the same once u seen once you should be able to go on and use the others ..

also i was wondering why u would use a timer to insert data into a db ??

[clock seconds] or [unixtime] gives unix time its seconds from somewhere in 1970 , u can reformat this into a date or hour or both like u prefer using clock format check the tcl man for more info on this there is lots of options and its beter to read it from there i can't go explain u every option cause then i have to paste the manual here ...

[SmokeyOne]

ya i know you can't explain every option, I'm just trying to get a few ideas on how things could be done in this case, since i've never seen a tcl like this beore. its better to know what other options are out there. So clock secons would be a better choice in this, but knowing how mirc works, wouldn't it be better to strip the code from like colors and such, maybe using excerpts from moretools?, that should help with taking each section of the text to put them in the right order.

[Ofloo]

if u wana strip code hmm then u got to use string map

example:

Code: Select all

string map {\003?? \000} $arg

i think that should work for color check tcl faq on this forum for bold italic ... and so on

[SmokeyOne]

for the time that you was talking about, i found this in the tclcommands.doc

strftime <formatstring> [time]
Returns: a formatted string of time using standard strftime format.
If time is specified, the value of the specified time is used.
Otherwise, the current time is used.
Module: core

ctime <unixtime>
Returns: a formatted date/time string based on the current locale
settings from the unixtime string given; for example "Fri Aug 3
11:34:55 1973"
Module: core


I'm going to guess one of these is what your talking about. Though strftime looks like it would be a better choice in this case, since if no string is givin for the time, it will use the current time on the box.

[SmokeyOne]

i found this in MC_8's moretools.tcl

proc mirc_strip {{args ""}} {
set switches ""
if {$switches == ""} {set switches all}
set arg [lindex $args 0]
set all [expr {([lsearch -exact $switches all] >= 0) ? 1 : 0}]
set list [list \002 "" \017 "" \026 "" \037 ""]
regsub -all -- "\003(\[0-9\]\[0-9\]?(,\[0-9\]\[0-9\]?)?)?" $arg "" arg
set arg [replace -- $arg [join $list]]
return $arg
}

I'm going to guess. this would work? or would it cause more problems if the code is a bit outdated ?

[Ofloo]

no check clock this also if ur inserting it to a db its beter like this.

check clock format and clock seconds

[Ofloo]

i found this in MC_8's moretools.tcl

proc mirc_strip {{args ""}} {
set switches ""
if {$switches == ""} {set switches all}
set arg [lindex $args 0]
set all [expr {([lsearch -exact $switches all] >= 0) ? 1 : 0}]
set list [list \002 "" \017 "" \026 "" \037 ""]
regsub -all -- "\003(\[0-9\]\[0-9\]?(,\[0-9\]\[0-9\]?)?)?" $arg "" arg
set arg [replace -- $arg [join $list]]
return $arg
}

I'm going to guess. this would work? or would it cause more problems if the code is a bit outdated ?

no what i use is string map this is different from regsub see i prefer to use string map but u could do that above altho string map is faster but i gues it would work just fine tho

this is a complete solution mine is just a sample string

[SmokeyOne]

so what your saying is using the string map to something like this correct ?

string map {\003?? \000 \017 \026} $arg

[SmokeyOne]

well I've build this proc, using mc_8's strip proc and the regsub filter, in hopes this might work, from what I was reading you can set a field in mysql to "datetime", not sure if this is true, i'm going to try it and find out. But this proc might work, if everything else is true

proc add:pub {nick ushost handle channel arg} {
global mysql net
set arg [rls:filter $arg]
set table $mysql(release_table)
set sql(conn) [mysqlconnect -host $mysql(host) -user $mysql(user) -password $mysql(pass)]
mysqluse $sql(conn) $mysql(data)
mysqlexec $sql(conn) "INSERT INTO '$table' ( 'id' , 'release_data' , 'release_date' ) VALUES ( '', $arg' , NOW( ))"
mysqlclose $sql(conn)
pustserv "NOTICE $nick :Release has been added"
putcmdlog "<<$nick>> !$handle! Added a new Release to the database."