CODEWITHSUNDEEP
phpmysql
Bronislaw
Bronislaw

Reputation: 85

Monitoring MySQL database using PHP

I have a mysql database with 12,000 entries, what i want setup is the ability to monitor a column in the database and if/when the column is altered for any entry it sends me an email with the details.

EDIT: I have access to mysql db, but not the script which works with it. So it should monitor it for changes...

Upvotes: 1

Views: 3213

Answers (6)

Mohammed Al-shareif
Mohammed Al-shareif

Reputation: 25

Create a trigger on a change on your column, then insert it to another table as log table.

Run cron job on your table that will send you an email.

Upvotes: 0

saint
saint

Reputation: 3905

you need to understand Data Manipulation Language (DML) triggers in my sql: use 

CREATE TRIGGER salary_trigger
    BEFORE UPDATE ON table_name
    REFERENCING NEW ROW AS n, OLD ROW AS o
    FOR EACH ROW
    IF n.columnName <> o.columnname THEN

    END IF;
;

Upvotes: 0

Sarwar Erfan
Sarwar Erfan

Reputation: 18068

  • Create a trigger on update
  • Create another table (lets call it cron_table), where the trigger will insert information of the updated row (may be old value, new value etc)
  • Setup a cron, which will call a script which will check the cron_table and send email if any entry is found. Cron interval can be setup according to need.

--- If you could send email from trigger, there would be no need for a separate table and cron ---

Upvotes: 3

Brian Hooper
Brian Hooper

Reputation: 22034

You could create some triggers on the table, if your version of MySQL has them. A trigger can then invoke any function you care to create. A trigger has the advantage that any insertion or deletion or any update of the column will cause it to fire; you wouldn't have to change any other code to make it happen. See here for more... http://dev.mysql.com/doc/refman/5.0/en/triggers.html

Upvotes: 5

Toby Allen
Toby Allen

Reputation: 11213

set up one column to be a datetimestamp.

This will update on every change of the row. There you can run a sql query either via a cron job or after every few php queries to return you the list of changed rows since the last check.

Select   * from tbl_myentries where EntryUpdated > '$TimeSinceLastCheck'

Upvotes: 0

TeAmEr
TeAmEr

Reputation: 4773

try something similar to this , you can edit the function to send you and email if the query has insert and TABLE_NAME or COLUMN_NAME in it

Upvotes: 0

Related Questions