I have no idea what the technical term for this is but here's what I need to do, my idea on how to do it, and I would like any feedback or advice if any of y'all see problems or have better ideas.

I have a set of data (a few database tables) that has an interface through which advanced searches can be done. These advanced searches however can also be saved as alerts that will e-mail the alerts owner when new information is added to the table that meets the search criteria.

At first I thought well I'll just cron a process that searches the table with the given criteria every hour or so. I then proceeded to slap myself silly for thinking that way. Depending on how often the table in question gets updated I could be running lots of wasted queries and I would have a to run a single query for EVERY alert. This is terribly un-scalable and just a bad idea in general.

So, now what I am thinking is this. I would store the information used to perform the search in the database and then whenever a record that is to be searchable changes I would somehow run a query that would select the alerts that the new record matches. (Hence the reversed search.)

Example time: (The example is going to be pretty rudimentary only because I don't want to take the time to post the actual tables controlling my data...it's really big)

I have a table containing the information that I want to search.

  Table: Books
   BookID  | Name			| Category | HaveRead | Description
 1	 | PoEAA		 | Design | y		| A good book for 
 	 |				 |		 |		 | enterprise software 
 	 |				 |		 |		 | design patterns.
 2	 | Dilbert		 | Comic	| y		| The best book for a light 
 	 |				 |		 |		 | hearted laugh at 
 	 |				 |		 |		 | software engineers.
 3	 | Scarlett Letter | Girly	| n		| Some novel that I know 
 	 |				 |		 |		 | nothing about.
Then I have a table containing a set of alerts

   Table: Alerts
   AlertID | Name			| Category | HaveRead | Description
 1	 | %			 | All	 | b		| software
 2	 | Dilbert%		| Comic	| b		| laugh
 3	 | %			 | Girly	| n	 | novel
For my example I am going to pretend I am adding the book in row 2 (Dilbert's my favorite)

Now, bear in mind that this is a pretty simple example, normally descriptions would be MUCH longer and the search terms a bit more specific.

There are three basic types of searching I need to do to make this work:

1. Wildcarded string matching: Basically the equivalent of LIKE='term%'\

2. Complete string matching: term must equal value

3. Full Text search: I don't know why I feel the need to describe these.

The two types of string matchings are easy.

The wildcarded matching.
SELECT * FROM Alerts WHERE 'Dilbert' LIKE Name

The complete matching.
SELECT * FROM ALERTS WHERE Category IN ('Comic', 'All')

The fulltext matching.

This is where I can't quite figure out how to get this working. I don't know how to reverse a full text search. The values I am using to search the descriptions may make it seem like I could just use a pattern matching type search (ala LIKE) but most of the times the descriptions will be searched with more than one word and I don't want to restrict it to just one word or even "phrases".

Any Ideas?


I just realized that this post actually has nothing to do with php the project is in php though!...so if a friendly moderator could move it I would love you forever. Sorry for my stupidity