php & mysql subscribe to search/matching queries

Question

I have a real estate website that has several categories. And i want the users to be able to subscribe via email to a certain list of offers, they can either be offers from a category ... or offers from a search list.

How can i store they're subscription the best way in mysql ... and also how do i go about and do the notification.

The best i came up with is to have a mysql sting stored in mysql table with the user ID and run a cron that takes all of that and iterates through it ... runs the cron and if there are new ads it sends an email.

But i don't think that this is the best idea, since it also means that if there is a change in database design ( ex, field names ) then all those queries would go bad.

thanks a lot

Answer 1

If you change database design, you'll certainly have to fix something, that's how this world works.

If for some reason it's hard for you to change the cron task, you can create a stored procedure that always returns a resultset suitable for the task, and change the stored procedure instead.

Answer 2

In general you want to design your tables to be as flexible as possible to account for unexpected change. If those fields change your queries would need to change anyway.

Without having the specifics, these would be my guidelines:

1. Have a Users Table with the index and email

2. Have a subscription types table

3. Have a User ID + Subscription lookup table

4. Have a pending messages table

When a new update is present, collect all the emails and create a message log entry into a pending messages table.

Build your Cron to grab 20 unsent messages every few minutes and delete the message once it's sent.

The reason I say to iteratively send them via the CRON is because as your subscribers grow you don't want to have hundreds of emails have to go out at once and tie up your server's resources for the mail queue. You can just keep adding messages as need be and your queue will consistently send them out.

We have been using this method pretty effectively for all our mass-emailing applications, error logging and reminder notification systems. It may not be the best solution but it's shown to be very manageable and flexible.