Hi,

I did my share of searching before posting this msg. So as to not ask repeated qns. However I couldnt get the information I need and I hope to seek the advise of those who have done this or know how to do this :)

I did a search on "pricing alert", "price" and "price reminder" but couldnt get the info I need (i searched hotscripts too and there was a result but too not what I need).

I would like to have a Price Alert feature like that of Pricegrabber(see pricegrabber's site) the "Price Alert" link.

What I need is basically a feature where subscribed members can take a interest in a particular product and while doing so, specify a price for a particular product say X. And as I monitor the prices of all products on my site, say the price of X drops to the value where my members specify, the system would generate an email to the member regarding the latest price for product X.

As I was going thro my mind bout how I can do this efficiently, it occures that ALL my members can subscribe to ALL my products to monitor their prices and this can be rather taxing on the server. I intend to use crontab entry to call checking script in regular intervals for price changes and match the changes based on another table of all the products the my members subscribe to and then send out all the related mail. Sounds very processing power hungry to me.

Can anyone suggest any simple and efficient way to come about this? As much as I know I am seeking advice but it will be great if the solutions proposed is based on php and mysql as these are the 2 languages I am picking up (hehe yes im a noob and im still learning)

And in a more ideal situation, are there any open source (preferably) or commerical solutions out there?

Cheers!

Is there an efficent way of doing this?
-- Nothing more efficent then probably how you thought how to do it.

Yes, it's possible that every customer will "alert" to every product but, not probable.

Assuming you have a table set up similar to the following.

Table - PriceAlert

ID - int, autoincrement, primary
ProductID - int (relates back to the product to be watched)
CustID - int (relates back to the customer to be alerted)
ReqPrice - float (the price at which to be notifed)
Expire - datetime (if the alert has an expiration date)


I included the expire b/c most people when shopping have a date at which they either need it or expect to aquire it. This will also ease the load on your DB when you do the call so you don't have to search alerts that the customer doesn't want to see anymore.

My Pseduo-Code would go about like this

Purge all Alerts past expiration date
-- DELETE where CurrentDate > Expire

Purge all Alerts where customer doesn't exist
-- DELETE where CustID != AnyCustomer

Purge all Alerts where product doesn't exist
-- DELETE where ProdID != AnyProducts

(These previous purges will reduce the scope of the search to lessen the load on the DB)

FindAlerts to be sent
-- SELECT where Price < CurrentPrice of Product
-- sendAlert to Customer
-- DELETE sentAlerts

That would be a rough PseduoCode of what I'd do for this situation. Purge all uneeded data first, find the products to alert, alert them, delete executed alerts.

Studio64,

Thanks a lot for your time and effort in helping me!

It is very much appreciated!

Thansk a lot once again :)

Cheers!