Email when table changes on webpage

PHP
1

Hi

I am working on project in which new data is added to table regularly.
some users want that when it updated they should get email. email should be customizable like on forums where you can receive mail every time something get posted or daily or weekly.

I am confused as to where should i start.

thanks

2

First, make a table to hold these subscriptions. It needs a column to identify a user (a user ID relating to another table, or an e-mail address). It needs a column to hold the frequency of subscription they want.

Second, make a table to hold the last updated date of your ‘data table’. Modify the script you use to add new data so that it updates this date whenever you add something.

Now create 2 scripts:

  1. Checks if the last updated date is less than 24 hours from the current time. If so, it selects all of the users from the subscriptions table with frequency set to daily, and mails them a notice that there is new data.

  2. Checks if the last updated date is less than 7 days from the current time. If so, it selects all of the users from the subscriptions table with frequency set to weekly, and mails them a notice that there is new data.

Now set up two cron jobs, one which runs the first script once a day, and another that runs the second script once a week.

3

Hi

Thanks for reply.

After reading your reply i got the idea of creating hook. Now whenever table changes it calls another function. This function gets emails of subscribers from subscribe table and send them latest changes.

I didn’t setup daily and weekly options as users were comfortable with receiving email the moment data changes. At max they will get 2-4 emails daily so it is not a big deal

now i want to know bit more about cron jobs. do i need to setup some script in unix or cron is just generic term in php context.

how i can setup cron job in php.

for example what if i had to check data daily at 10pm and then collect data that has been changed since last night 10pm.
do i need some kind of counter to keep track of time.

i am confused about this.

4

Cron is a job scheduler program for UNIX operaitng systems. It’s not a generic term. It is not part of PHP, but your job can be to execute a PHP script.

To know what data has changed since last night at 10PM, you need to either store times on all the rows so you can select those created later than 10PM, or you need to have an AUTO_INCREMENT column and store the highest value seen at the previous check to know which are newer.

5

sorry I wasn’t clear before.
I know about cron on linux but don’t know how to tie it with php.

I am using framework which is similar to general mvc frameworks. every thing needs that user must be logged in.

how i can run a script with user authentication.

suppose i want to run example.com/subscription.php, how should i approach that?
i am reading more about cron now.

6

Hi,

Cant you just add to your script that inserts the record so when a new record is added to the table, follow on from the same script with your mail() script. Each time a record is added it will also mail out to your users… This will eliminate the need for a cron job.

7

thanks for reply.

yeah I have created trigger function in php when record is inserted. It is serving purpose for the time being as emails generated are very low in numbers(2-4)/day.

But i was thinking about about different cases in future when email traffic will be higher.

For learning i created one cron job and tied it to simple script which emailed me at 5 pm.

I realised that i don’t need authentication for the job. cron will call subscribe.php at specified time and then its upto me to setup function to do whatever i want.

thanks for help.