Mysqltcl foreach loop tcl

Question

My issue is that the first set return drops the $sub1 before it can be used in the string match so the script doesn't continue. I have tried to include the rest of the script inside the first set return and it works but...I get multiple messages to the user and the channel for the other 2 set returns.

Anyway to fix it so that it doesn't send the multiple messages to the users and channel because the for each sub in the "foreach sub" is producing a line for pm user and pm channel which cane be anywhere from 1 or 2 message to 200 messages depending on how many matches in the database.

bind pubm - * bind_pubm
    proc bind_pubm {nick uhost handle channel text} {
        global SQL; sql:start

        set regexp {^!sub (.*) *$}
        if {[regexp -nocase -- $regexp $text -> subscription]} {
            if {[string match -nocase *HDTV* $subscription] || [string match -nocase *S??E??* $subscription]} {

            set return [mysqlsel $SQL(conn) "select sub from DB_Subs where category='TV'" -list] 
            foreach {sub} $return {     ; # Lists all the subs in the DB for matching.
            regsub -all -- {%} $sub "*" sub1    ; # Replaces % in SQL to * for the String match later.
            }

            if {[string match -nocase $sub1* $subscription]} {  ; # Matches if a sub in the previous list matches the regexp subscription fromthe beginging of proc.
                set return [mysqlsel $SQL(conn) "select user from DB_Subs where sub LIKE '[mysqlescape $sub]%' AND category='TV'" -list]
                    foreach line $return {
                        foreach {user} $line break
                            if {[onchan $user $beta]} {     ; # If the user is on the channel it PM each user.
                                putnow "PRIVMSG $nick : Subscription found matching your request."
                            } else {
                        }
                    }

            set return [mysqlsel $SQL(conn) "select count(DISTINCT user) from DB_Subs where sub LIKE '[mysqlescape $sub]%' AND category='TV'" -flatlist] ; # Counts the users for the Channel message.
                foreach {users} $return break
                    putnow "PRIVMSG $beta :SUBSCRIPTION: $users Users With Subscriptions for $subscription"
            } else {
            }
        } else {
        }
    }
}

Its hard to explain what exactly im trying to accomplish.

In the end the outcome im trying to reach is...

• Set the $subscription from the regex
• List all the subs within the DB
• Covert all % within the sub in the DB to * for the following Match
• Try to match the sub to the $subscription
• If they match then continue to the next SELECT
• Select all "USERS" from the DB where the sub is like the %sub%
• Then send the each user a message that match the select
• Last set return counts the number of Users that match the select and send a message to the channel

After using the solution that Donal suggested. Everything seems to perform like it should with one little issue. The [string match -nocase [get_subscription $SQL(conn)]* $subscription] part of the code isn't saving each as a variable to be checked. Which ever row shows up first it uses instead and stops instead of finishing the list to see if there are any more matches. Some of the entries are added in different ways but should provide the same results. for example some entries are added as The.TV.Show.S01 or The%TV%Show%S01 This means that it should match both sections and get a accurate count and users.

Answer 1

It's probably difficult because you've got too much in one piece. Try breaking the code up into smaller parts that do a clearly defined task. This is called refactoring and it is an important part of making your code easy to understand.

Here are a few suggested refactorings:

proc get_subscription {conn} {
    set return [mysqlsel $conn "select sub from DB_Subs where category='TV'" -list] 
    foreach {sub} $return {
        regsub -all -- {%} $sub "*" sub1
    }
    return $sub1
}

proc notify_subscription_user {conn nick sub beta} {
    set return [mysqlsel $conn "select user from DB_Subs where sub LIKE '[mysqlescape $sub]%' AND category='TV'" -list]
    foreach line $return {
        lassign $line user
        if {[onchan $user $beta]} {
            putnow "PRIVMSG $nick : Subscription found matching your request."
        }
    }
}

proc send_subscription_message {conn sub beta subscription_text} {
    set return [mysqlsel $conn "select count(DISTINCT user) from DB_Subs where sub LIKE '[mysqlescape $sub]%' AND category='TV'" -flatlist]
    lassign $return users
    putnow "PRIVMSG $beta :SUBSCRIPTION: $users Users With Subscriptions for $subscription_text"
}

With these, we can then rewrite the rest of your code to be like this (removing empty else clauses, splitting expressions over lines, combining nested if tests; all basic stuff):

bind pubm - * bind_pubm
proc bind_pubm {nick uhost handle channel text} {
    global SQL; sql:start

    if {
        [regexp -nocase -- {^!sub (.*) *$} $text -> subscription]
        && ([string match -nocase *HDTV* $subscription]
            || [string match -nocase *S??E??* $subscription])
        && [string match -nocase [get_subscription $SQL(conn)]* $subscription]
    } then {
        notify_subscription_user $SQL(conn) $nick $sub $beta
        send_subscription_message $SQL(conn) $sub $beta $subscription
    }
}

Does this fix your problem? I don't know, but it should give you a much better base to start from.