On Apr 25, 2013, at 3:06 PM, Carl Kuczun ckuczun@PROTECTED wrote:

A worthwhile improvement, Justin If you are going to get into very complex search capabilities, you might consider Regular Expressions (http://www regular-expressions info/reference html) where ^80 would match the beginning two digits and \d{3}$ would wildcard the last three digits RegEx is an extremely complete search language, but probably overkill for an email list

Actually, my worry with getting more complicated is the user interface to all that - as it stands right now, it's getting a little awkward I'd most like an interface, where you'd presented with just, one search box, that allows you to select the field, the type of search (equal/not like/not), and then your search term Want more fields? Click the, "+" button (or, whatever) That way, you can perform different types of searches on the same field Not sure exactly what the easiest way to do that zip code example is though - from a user interface point of view

Whatever it is, the searches are all done via SQL, so whatever I use has to be compatible with both MySQL and PostgreSQL an probably SQLite (although, I can let that slide…) That even sounds like a nightmare I could do some of the filtering just in Perl, but that's usually slower (and uses more memory) than doing it in SQL Doesn't matter with a list of 1,000, but does with a list of 250,000 Ah, constraints ;)

So, for example, here's what the query looks like, when I do that search, from my example:

SELECT dada_subscribers email, dada_subscribers list, dada_profile_fields first_name, dada_profile_fields last_name, dada_profile_fields city, dada_profile_fields state, dada_profile_fields fav_color FROM dada_subscribers LEFT OUTER JOIN dada_profile_fields ON dada_subscribers email = dada_profile_fields email WHERE dada_subscribers list = 'j' AND dada_subscribers list_type = 'list' AND dada_subscribers list_status = '1' AND ( ( dada_subscribers email LIKE '%justin%' ) AND ( dada_profile_fields city = 'Washington' ) AND ( dada_profile_fields fav_color NOT LIKE '%red%' AND dada_profile_fields fav_color NOT LIKE '%yellow%' ) ) ORDER BY dada_subscribers list, dada_subscribers email ASC

It was already making my head hurt ;)