How can I make PHP repeatedly check my database for modifications?

Question

I have a program which makes changes to an MSSQL database. I wanted some actions to happen when these changes are made, like sending an e-mail. This program doesn't do any of those things and I also can't modify the program, so as my question says.. can I use PHP to keep checking for changes in the database and do those actions? I was thinking something like:

$originalDBContent = "SELECT events FROM evTable";

while(true){
  $checkContent = "SELECT events FROM evTable";
  while($originalDBContent == $checkContent) sleep(1);

  //command below if changes made to db
  mail(...);
  }

Will it work or any other programs suggested to accomplish this?

If Linux, use [cron](http://www.unixgeeks.org/security/newbie/unix/cron-1.html). If Windows, use [Task Scheduler](http://stackoverflow.com/questions/132971/what-is-the-windows-version-of-cron). — SomeShinyObject, Dec 22 '13 at 14:39
Can you use triggers in the database? If you can, write to the table all changes — test1604, Dec 22 '13 at 14:44
I don't know about anything about triggers.. I can probably add new tables, but I'm not sure if anything else will make the program I was mentioning unstable. I will try the Task Scheduler. Using Windows — Vantalk, Dec 22 '13 at 14:51

score 0 · Answer 1 · answered Dec 22 '13 at 14:42

0

no, you can't do that on PHP since if you do that

while(true){
  $checkContent = "SELECT events FROM evTable";
  while($originalDBContent == $checkContent) sleep(1);

  //command below if changes made to db
  mail(...);
  }

this will cause the browser or whoever accessing it got a timeout since it'll make the page keep loading.

the best thing you can do with PHP is doing a cron job but why did you need to check every time database changed ?

answered Dec 22 '13 at 14:42

Rio

1

As I said, wanted an email to be sent on the moment there was a change in the events table. – Vantalk Dec 22 '13 at 14:47

exussum · Accepted Answer · 2013-12-22T15:30:24.730

0

The use of triggers with sqlcmd should be done easily enough

http://technet.microsoft.com/en-us/library/ms170572.aspx

this would allow you to call the php command

Another way could be (im not sure of the exitinace in mssql) monitor the binary log change date, The binary log shows all modifications to data on a server. you could check the modified time on the file and keep track.

The last option requires you to have full control. But on insert you could run the script. This wouldnt fire anything assuming you change the data manually though, So all changes would need to be made though your application

The best option would be trigger + sqlcmd

More info and examples

http://dbalink.wordpress.com/2008/06/20/how-to-sql-server-trigger-101/

edited Dec 22 '13 at 15:30

answered Dec 22 '13 at 14:45

exussum

16,939
7
29
61

triggers + sqlcmd, transact seems to be the way. But I don't know how to get the trigger started, I'm also time pressured. Can you please provide a little more information to get the trigger or script started? – Vantalk Dec 22 '13 at 15:27
One question. Do I need the paid SQL Agent or Management Studio (paid version, not express)? – Vantalk Dec 22 '13 at 15:38

How can I make PHP repeatedly check my database for modifications?

2 Answers2