woodgoldenrod
New Member
Here is what I want to do. I have an idea on how I can do it but I want to ask you experts what would be the best way to do this.I have a mysql table where every row has a status flag which changes as per business rules set from the website. What I want to do is with every change in the status flag I want to send an email to the client about the status change.I am using PHP and MySQL (InnoDB) and Postmarkapp API calls from PHP to send emails currently.My idea of achieving this - [*]Build a table in the DB email_status_changes with fields client_id, client_email, status_before, status_after, is_email_sent (Y/N), dttm_email_sent.[*]Create a stored procedure or trigger on the table where the status changes are written.[*]The stored procedure or trigger writes into the new table email_status_changes based on what changed in the row from the table where the status actually changed.[*]Create a CRON job that runs that reads this table email_status_changes and sends email to client_email if is_email_sent is N and toggle the status is_email_sent to Y when the mail has been sent.Do you think there is an easier way to do this?Thanks for the help.