View Full Version : how to send automatic alerts ?

03-25-2004, 09:23 AM

I have 3 tables on my B2B trading site, that uses PHP and MySql and runs on Apache. The shortened tables are given below. The second row gives an example of the fields:

Ads (with fields: ad_id, ad_title, ad_category, ad_type, user_id),
Ads (8, "Mobile Phones", "1", "Buy", 100)

Users (with fields: user_id, user_email, user_categories, user_alert)
Users (5, "abc@abc.com", "Phones, Cars", "1")

->user_alert value 1 means only Buy Ads.

Category (cat_id, cat_name, cat_father_id).
Category (1, Phones, 0)

cat_father_id value 0 implies that it's a main category.

There are about 350 different categories & sub-categories in which the Ads can be posted. Similarly, the Users can belong to one or more categories. I want to set up automatic trade alerts with the following scenario:

A Buy Ad is submitted in a category. Users who belong to that category and who want to receive alerts on Buy Ads should get the alert.

1) I would appreciate if someone could post a sample code to achieve the above.

2) Secondly: Anticipating that 200-300 Ads would be posted everyday and Number of users will grow to few thousands, which means that each Ad may have to be mailed to thousands of users. What is the best way to send such alerts ? Shall I Send alerts whenever an Ad is posted, or run it as Cron job at some specific intervals.

Any ideas on how big B2B trading sites such as alibaba.com handle it ?

Looking forward to expert suggestions.


03-25-2004, 10:17 AM
First off: bad db-design

Users (with fields: user_id, user_email, user_categories, user_alert)
Users (5, "abc@abc.com", "Phones, Cars", "1")

should be
Users (with fields: user_id, user_categorie, user_alert)
Users (5, 1, 1)
Users (5, 9, 1)

==> one row per user/category
==> only include the PK-value of the user and the categoy
==> if user_alert is a general parameter (catogory independent) then it should also be removed here + it needs to be a numerical column (so no quotes --> if that meant it was a text-column)

You then need to join with the usertable to get the email-adress
Use the categorys PK value to select on this table --> The two first variables should be indexed.

About your question: i've recently set up something similar. I immedeately send the mails because the number of receivers is quite low. But i've included an admin section where you can compose and schedule the mailing so that it runs overnight.
In your case, it depends on how soon the users should get a notice. If you can bare with a delay of 18 hours or so, then you best send them overnight.

Else, send them immideately. I've tested the performance of mail() and it's not incredibly fast + the average speed drastically drops when the number of mails increases. I don't exactly remember the stats, but it could run it again if you want.
I only tested it up to 500 mails. Sending out thousands of mails at once will not work using the mail() function. You'll need to use a method that only opent the socket once. Skim throught the usercomments at http://www.php.net/function.mail for some code on socket-mail

Huge sites will not send the mails out with mail() or some other embedded mailcode. They will probably make a queue with all mails and then use some seperate mailprogram to send out the mails (without going over the webserver).

But i think your first question should be if you really want to send out so much mails ... You could also just send out one mail /category/user until the user logs back on to your site. And then on logon, display a list of all new entrys. Else you will be just annoying a lott of users, some of which will probably add your send-adress to their blacklist ...

03-25-2004, 10:34 AM
Hey raf! I was about to answer this question that I saw your'e faster! And of course more accurate!!

Hey, how do you let the admin choose wether he likes to send them over night (cron job) or instantly? I mean how do you set up the cron job? You do it with your PHP code? Does your server let ya do it?

03-25-2004, 12:49 PM
I've written this message/mail admin module for that.

The admin first selects a messagetype (private message, general message, broadcast, single mail, general mail)
Then fill in the usual subject and message and submits the form.
This is stored in a db.

For the mails, i then reload the page with a form with a datedropdown and hour/minute dropdowns to specify the send time.

The message record is then update with this datetime.
(is some logic envolved to check if the time hasn't already past etc)

Then i have a cron that runs every 15 minutes and that select all records from the messages table that weren't already processed and that have a send-datetime in the past.
(after the mail is succedfull send, the ID is added to a collection-variable. When all records are processed, the messages table is updated like
update messages set sent=1 where ID in ($collection))

This way, even if the server goes down or so, it will always pick all mails that should have already been sent

The cron fires of a PHP file. I now use an account at ithium.net wich allows me to set up crons

03-25-2004, 05:28 PM
Brilliant! So, you're running your page each 15 minutes by a cron job. Now I got it! I was thinking you can set up a cron job by PHP!

03-25-2004, 07:53 PM
yes. it's as simple as that. Here's a good short tutorial on running php's through crons