How do I store user preferences for further queries?

I would like to implement a mailing system which sends my registered users notifications when a new blog posts matches their configured preferences.

When the user config their preferences, this basically create a SQL query underneath, but I don't find it really clean/safe to store a SQL query in a database. Unless I'm told otherwise.

Also, I want a solution that will scale well if I add more filter's criterias in the future.

One of the idea I had, is to serialize() a PHP array containing all the WHERE data.

Is that the most effective way to accomplish this ? Or is there any other solutions ?

Thank you in advance.

PS: I am not looking for a mailing library. I am only looking for a hint on how I need to design my application for the most efficient way.

Edit: I received two similar answers offering the same solutions. I'm afraid my question is a bit more complicated though.

The solutions works if I only add 开发者_StackOverflowtags preferences possibles.

What if I want to make specific filterings possible eg: UserB wants to get notified when a post tagged html is made and have atleast one comments (or votes).

That is why I said that a SQL query is basically created as the users selects it's preferences.

For reference, I'm adding this link.

Thank you.

Why don't you use a table with just an auto_increment id field and a email field then a simple email input on your blog ?

You have a user table ?

EDIT : i think you need a second table named user_preferences with id + id_user + tag like :

id | id_user | tag
1  |    1    | php
2  |    1    | html
3  |    2    | php

Then

SELECT DISTINCT email
FROM user
INNER JOIN user_preferences ON id_user = user.id
WHERE tag IN ('tag1', 'tag2', 'tag3');

Serializing is an option. I happened to be in a similar situation and management wanted a report on what is being searched on the most. So I had to query the entire table and build arrays, then do reports from large arrays...not the best way to handle it.

I updated the system to have a table with field/value columns corresponding to what type of filter was being used and then it's value. Then wrote a function to convert those into queries.

So in your example, there would be an entry in the table of "tag","php" and then "tag","database-queries" for that user id.

Below is similar to what I used (edited to handle question change):

CREATE TABLE `user_prefs` (
  `user_pref_id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `type` varchar(255) NOT NULL,
  `condition` varchar(10) DEFAULT '=' NOT NULL,
  `value` varchar(255) NOT NULL,
  PRIMARY KEY (`user_pref_id`)
);

INSERT INTO `user_prefs` (`user_id`, `type`, `value`) VALUES ({UserA}, 'tag', 'php');
INSERT INTO `user_prefs` (`user_id`, `type`, `value`) VALUES ({UserA}, 'tag', 'database-queries');
INSERT INTO `user_prefs` (`user_id`, `type`, `value`) VALUES ({UserB}, 'tag', 'html');
INSERT INTO `user_prefs` (`user_id`, `type`, `condition`, `value`) VALUES ({UserB}, 'comments', '>', '1');

I was then able to run reports on common searches, etc. Worked better for me anyway.