Mysql.mod / checking info

[Buffy_25]

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 :

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:

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

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

[demond]

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

[Buffy_25]

Hi demond,

I tried to put this into code, but it aint working (and no error):

Code: Select all

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

[demond]

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

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
   }
}

[Buffy_25]

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):

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

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

[demond]

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

[Buffy_25]

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