Re: Building a notification system based on MySQL

[duckyd]

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

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.

[shank098]

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

[shank098]

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