| View previous topic :: View next topic |
| Author |
Message |
Buffy_25
Halfop
Joined: 22 Nov 2003
Posts: 63
|
 Posted: Sat Nov 26, 2005 9:53 am Post subject: Mysql.mod / checking info |
 |
|
Hi,
On my linux computer i'm running mysql + mysql.mod
I have a mysql database that is keeping the users online time in my channel.
So i have :
| Quote: |
id names uptime
1 David 2 days 5 hours 24 minutues 1 second
2 Helen 5 weeks 3 days 29 minutes
3 Bonjo 6 days 4 hours 2 minutes
|
Now i have normal txt file, that contains the following:
| Quote: |
David was online for 2 hours 1 minute 3 seconds
Marcus was online for 1 day 3 hours 21 minutes 2 seconds
Helen was online for 35 minutes 25 seconds
Odeline was online for 3 days 25 minutes 3 seconds
David was online for 3 hours 2 minutes 25 seconds
Helen was online for 5 hours 3 minutes 1 second
...
|
What i would like to do, is to make a little script that read the txt file, and checks if the users in the txt file are known in my mysql database.
If exist, the uptime will be updated [expr]
If the nick doesn't exist, it will have to be created.
I have already the following, but i don't know how to check if the field "names" has a user of my txt file.
| Code: |
bind pub - "!update_data" update
proc update {nick uhost hand chan arg} {
mysql_connect MyDB localhost username passw /var/lib/mysql/mysql.sock
set file [open /usr/account/eggdrop/logs/times.txt r]
set buf [read $file]
close $file
foreach line [split $buf \n] {
set line [string trim $line " "]
if {$line == ""} {continue}
set line [split $line]
set user_nick [lindex $line 0]
set user_online [join [lrange $line 4 end]]
set user_online [converter $user_online] #the proc convert is working good (put all in seconds)
if {![info exists useronline($user_nick)]} {
set useronline($user_nick) $user_online
} else {
set useronline($user_nick) [expr $useronline($user_nick)+$user_online]
}
}
foreach {user_nick user_online} [array get useronline] {
#** here should come the part to check if user exists or not, but i'm totally lost for this part**
#So if user exist, i'll go directly to update of database
#If user doesn't exist, create the user with default data of table
#this is the part to update the database uptime
set presenttime [mysql_query "SELECT useruptime FROM Userinfo WHERE name='$usernick'"]
set convpresenttime [converter $presenttime]
set convuseronline [converter $user_online]
set useronline [expr $convuseronline+$convpresenttime]
set onlinetime [duration $user_online($user_nick)]
set newtime [mysql_query "UPDATE Userinfo SET useruptime='$onlinetime' WHERE name='$usernick'"]
}
}
}
|
Thank you.
Buffy |
|
| Back to top |
|
 |
demond
Revered One
Joined: 12 Jun 2004
Posts: 3073
Location: San Francisco, CA
|
| Posted: Sat Nov 26, 2005 5:26 pm Post subject: |
|
|
you need to SELECT * from db first, create a Tcl list out of it, and [foreach] name in this list to [lsearch] the list you made out of your text file
or, if the db is too large, bind a SELECT variable to each name in your file list and execute the SQL statement multiple times
_________________
connection, sharing, dcc problems? click <here>
before asking for scripting help, read <this>
use [code] tag when posting logs, code |
|
| Back to top |
|
|
Buffy_25
Halfop
Joined: 22 Nov 2003
Posts: 63
|
| Posted: Mon Nov 28, 2005 7:35 am Post subject: |
|
|
Hi demond,
I tried to put this into code, but it aint working (and no error):
| Code: |
bind pub - "!update_data" update
proc update {nick uhost hand chan arg} {
mysql_connect MyDB localhost user passw /var/lib/mysql/mysql.sock
set file [open /usr/account/eggdrop/logs/times.txt r]
set buf [read $file]
close $file
foreach line [split $buf \n] {
set line [string trim $line " "]
if {$line == ""} {continue}
set line [split $line]
set user_nick [lindex $line 0]
foreach a [array get user_nick] {
set listuser [mysql_query "SELECT name FROM Userinfo"]
foreach e $listuser {
if {[lsearch -exact $e $a] == -1} {
putserv "PRIVMSG #channel :$e isn't in the database"
#so if no match, user isn't in my mysql data
}
else { putserv "PRIVMSG #channel :$e is in the database"
#if match, then confirm user is in mysql data
}
}
}
}
}
|
Thanks for the help.
Buffy |
|
| Back to top |
|
|
demond
Revered One
Joined: 12 Jun 2004
Posts: 3073
Location: San Francisco, CA
|
| Posted: Tue Nov 29, 2005 12:14 am Post subject: |
|
|
you are confused, user_nick is not an array (even it was, you handle it incorrectly - [array get] returns list of key->value pairs, not just values)
I suggested you create 2 lists separately - that implies you need not use nested loops, which doesn't make sense; follow this pseudo-code:
| Code: |
open file
read each line into list1
close file
open db
SELECT name FROM userinfo
fetch each record into list2
close db
foreach element $list1 {
if {[lsearch $list2 $element] == -1} {
# name from file is not in database, proceed accordingly
}
}
|
_________________
connection, sharing, dcc problems? click <here>
before asking for scripting help, read <this>
use [code] tag when posting logs, code |
|
| Back to top |
|
|
Buffy_25
Halfop
Joined: 22 Nov 2003
Posts: 63
|
| Posted: Tue Nov 29, 2005 7:38 am Post subject: |
|
|
Demond,
I tried to set it up with 2 seperated lists, but i'm kind of stuck with the .Txt file since it's containing info like this (txt after the nicknames):
| Quote: |
David was online for 2 hours 1 minute 3 seconds
Marcus was online for 1 day 3 hours 21 minutes 2 seconds
Helen was online for 35 minutes 25 seconds
Odeline was online for 3 days 25 minutes 3 seconds
...
|
This is what i have as code:
| Code: |
bind pub - "!update_data" update
proc update {nick uhost hand chan arg} {
set file [open /usr/account/eggdrop/logs/times.txt r]
set buf [read $file]
close $file
foreach line [split $buf \n] {
set line [string trim $line " "]
if {$line == ""} {continue}
set line [split $line]
set user_nick [lindex $line 0]
set uptime($user_nick) [join [lrange [split $line] 1 end]]
}
mysql_connect MyDB localhost user passw /var/lib/mysql/mysql.sock
set listuser [mysql_query "SELECT name FROM Userinfo"]
foreach element $listuser {
if {[lsearch $user_nick $element] == -1} {
putserv "PRIVMSG #channel :$user_nick isn't in the database and will be created"
set new_command [mysql_query "INSERT INTO Userinfo (name, useruptime) VALUES ('$usernick', '$uptime($user_nick)'"]
} else { putserv "PRIVMSG #channel :$user_nick is in the database and his uptime will be updated"
set presenttime [mysql_query "SELECT useruptime FROM Userinfo WHERE name='$user_nick'"]
set convpresenttime [converter $presenttime]
set convuseronline [converter $uptime($user_nick)]
set useronline [expr $convuseronline+$convpresenttime]
set onlinetime [duration $useronline]
set newtime [mysql_query "UPDATE Userinfo SET useruptime='$onlinetime' WHERE name='$user_nick'"]
}
}
}
proc converter {time} { #Working OK !
}
|
I'm lost, since my database isn't being updated as mentioned above 
Isn't this the correct way of working ?
Or are there any faultly lines?
(check if name exists, if not create new entry in database with the time behind the nick OR name exists, then update the uptime in database)
Thanks.
Buffy |
|
| Back to top |
|
|
demond
Revered One
Joined: 12 Jun 2004
Posts: 3073
Location: San Francisco, CA
|
| Posted: Tue Nov 29, 2005 11:16 pm Post subject: |
|
|
well, what I presented to you in pseudo-code is extremely simple IMO, you should be able to grasp and implement it
I've got the feeling what you're trying to do is your first script, and you feel quite uncomfortable with basic programming stuff; if that's the case, you need to start from the basics - for example, write a script which adds or removes element, or/and searches through a Tcl list; then try to find common elements in 2 lists
_________________
connection, sharing, dcc problems? click <here>
before asking for scripting help, read <this>
use [code] tag when posting logs, code |
|
| Back to top |
|
|
Buffy_25
Halfop
Joined: 22 Nov 2003
Posts: 63
|
| Posted: Sun Dec 04, 2005 5:39 am Post subject: |
|
|
demond,
I made a less complex script to see how all those basic commands were working, and i got it working now 
"Lappend" is a wonderfull option 
Thanks for your time.
Buffy |
|
| Back to top |
|
|
|
|
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
|
|
FAQ
Search
Memberlist
Usergroups
Register
Profile
Log in to check your private messages
Log in
