in reply to Building a notification system based on MySQL

It sounds like when you've got multiple values, you might want to use an in statement, I.E. 
If SlotA = Value and SlotB = Value and SlotC in (value1, value2) then 
+notify GroupA

[download]
I'm curious if you have another table that you're getting the Group to notify from - it seems likely to me that there's a way to determine the person to notify in a single sql query (without requiring building a query up dynamically in perl), but without knowing more about the specifics of the tables in question, it's impossible to say.

Replies are listed 'Best First'.
Re^2: Building a notification system based on MySQL
by shank098 (Initiate) on Nov 18, 2008 at 14:20 UTC

    There are actually a bunch of other tables,

    • users: this table contains email address, and user names
    • groups: this table defines all the groups - group id's and names
    • membership: this table relates users to groups
    • notify: this table relates filters to groups
    • filter: this table actually contains the filters
[reply]
Re^2: Building a notification system based on MySQL
by shank098 (Initiate) on Nov 18, 2008 at 16:42 UTC

    Thanks for the tip! Here's the new code to handle a situation when the key already exists

    while (my($slot,$value) = $filterSth->fetchrow_array()) {
  if ($filerHash{$slot}) {
     //slot already exists, just append to it
     my $existingValue = $filterHash{$slot};
     my $newValue = $existingValue . " | " . $value;
     $filterHash{$slot} = $newValue;
  } else {
     //slot does not exist, just add it
     $filterHash{$slot} = $value;
  }
}

    [download]
[reply]
[d/l]

In Section Seekers of Perl Wisdom