CODEWITHSUNDEEP
phpjqueryajaxdatabase
Rob
Rob

Reputation: 183

Listening to database changes using PHP

I have a legacy system that uses Oracle and runs nightly jobs, aka Batch. During batch processing it posts multiple events to several tables in that database. I want to create a web based application that can monitor these changes.

My general idea is

Has anyone done anything like this and can offer some guidance or direction?

Should a project such as this be based on a PHP framework, such as Yii? I read through docs for Yii and it makes mention of its db APIs.

On a side note: I tried a similar exercise using NodeJS. I didn't connect to a real db, instead simulated events in the server code. Again, it was a jquery-based page with events nicely posted via emits (messages) from server.

So, bottom line: want PHP service listening to db changes; register front-end app to listen for changes; update front end.

Thanks. Rob

Upvotes: 0

Views: 4363

Answers (1)

Robbie
Robbie

Reputation: 17720

You still need PHP to connect to the DB (otherwise client side connections would reveal your username and password).

So, in your web app, have an ajax call (use jQuery if you've used that, it does the job just fine) to call a PHP script each minute / 10 seconds / second - however fast you want a response - and act on the results. The PHP script would connect to the database and report on any changes.

If you wanted a separate service "listening" for DB changes, write normal PHP (that you can run / debug with a broswer). This will be the PHP script that monitors the database each minute and does whatever actions are required. You can use whatever framework you like - it's just regular PHP. When you get this working, set it up as a "cron" job. you run "PHP -q /path/to/yourfile.php" each minute. When there's a change, PHP can set whatever flags are needed.

Word or warning on the Javascript polling - make sure you don't user setInterval() - but start the next times each time the AJAX call either returns (or fails to return). If the server's too busy to respond and you send another request, it makes it busier and goes downhill from there. So set the next poll when you get the response, or when you fail to get a response after a reasonable amount of time.

You can see this in action in myresistance.net: It runs WordPress and SMF installations - if you're on the Wordpress part (the blog/news bit) then it's polling the database with a "ping" function every couple of minutes. This checks the database to see if you've logged in or out of the SMF part and will reload the WordPress part if you have, also logging you in or out as required.

Upvotes: 1

Related Questions