Page 1 of 1

Advanced Algorithm Mail Sorting

Posted: Wed Jan 19, 2011 10:19 pm
by kamerondotcom
Hello-

I'm looking to this forums expertise in order to structure an algorithm to return messages in a php webmail app similar to apple mail's rule system.

Criteria:
-Unlimited "rules" or sorting criteria
-Example rules "integer ranges" "string matches" "date ranges"


For Example:
Rule 1: Find all messages that were sent between 1/10/2011 AND 1/19/2011
Rule 2: The message sender must contain "@gmail.com"
Rule 3: Sender is NOT in my address book
Rule 4: Message is high priority
Return: X messages


I know how to structure each of these "rules" on an individual basis but would like to use off of them combined in a simple, clean and efficient algorithm.. Rules will vary and may be used in different orders, combinations, quantities etc.

How should I structure something like this? Thanks in advance!! :)

Screen shot 2011-01-19 at 11.07.39 PM.png
Screen shot 2011-01-19 at 11.07.39 PM.png (63.82 KiB) Viewed 2432 times

Re: Advanced Algorithm Mail Sorting

Posted: Wed Jan 19, 2011 10:49 pm
by Technical
How do you store mail? The most efficient way is using SQL queries, but you need your mail stored in SQL database.

Re: Advanced Algorithm Mail Sorting

Posted: Thu Jan 20, 2011 11:33 am
by kamerondotcom
Yeah, it'll be a mySQL database. I'm just sure how to append all of the data qualifiers on top of one another to return the right rows or results.


Thanks!

Re: Advanced Algorithm Mail Sorting

Posted: Thu Jan 20, 2011 11:52 am
by Technical
SQL has a lot of useful operators:

1) <
2) <=
3) >
4) >=
5) =
6) ==
7) !=
8 ) <>
9) IS
10) IS NOT
11) IN
12) LIKE
13) GLOB
14) MATCH
15) REGEXP
16) AND
17) OR
18) BETWEEN

You can use them in conjunction. For example, lets build condition from your first screenshot:

Code: Select all

SELECT * FROM table WHERE "recipient" LIKE '%devnet@devnetwork.net%' AND "received" < 7 AND "sent" < 7 AND "from" LIKE '%devnet@devnetwork.net%'
Pretty easy, isn't?

Re: Advanced Algorithm Mail Sorting

Posted: Thu Jan 20, 2011 12:04 pm
by d3ad1ysp0rk
http://dev.mysql.com/doc/refman/5.0/en/select.html

The WHERE clause can match as many conditions as you can throw at it.

Example:

Code: Select all

$mailSQL = "SELECT * FROM email WHERE (timestamp BETWEEN UNIX_TIMESTAMP('2011-01-10 00:00:00') AND UNIX_TIMESTAMP('2011-01-19 23:59:59')) AND from LIKE '%@gmail.com%' AND ... ";
Good luck!

[edit]Posted too slow.. lol