Script for detecting changes in table

[Grande]

Hello,
I'm working on the following: I have to make a script that constantly checks if a certain table in a database
changes. It's a table where alerts are logged, so when a new alert is logged into the table, a mail has to be sent to the admin. The sending of the mail itself will be no problem, I just don't know how to make sure that the table is constantly checked en when there's a new row in it, the e-mail with the data is sent....

Someone who knows what to do ?
Thx
Grande

[elementz]

Use cronjob?

[Grande]

what is cronjob ? Never heard of that before ...

[elementz]

Us cron in CPanel to run a PHP script that checks for changes in the table every few minutes.

[Grande]

No, can't do that, have no Cpanel here and I have to write the script myself and implement it.
I could realy need some help with this because I'm kinda stuck

Cheers ,
Grande

[redbullmarky]

cronjob?? why??

i really apologise if i'm missing the point here, but surely you can just stick a call to 'mail' function directly after executing the INSERT query?

Cheers
Mark

[Grande]

Yess, that's true but the insert happens by a predefined package of Linux, Snort. So I'm not sure it's best to change that package, so for now I'm looking for a different solution . . .

[redbullmarky]

[!--quoteo(post=360358:date=Mar 31 2006, 02:52 PM:name=Grande)--][div class=\'quotetop\']QUOTE(Grande @ Mar 31 2006, 02:52 PM) [snapback]360358[/snapback][/div][div class=\'quotemain\'][!--quotec--]
Yess, that's true but the insert happens by a predefined package of Linux, Snort. So I'm not sure it's best to change that package, so for now I'm looking for a different solution . . .
[/quote]

if the package is just a standard package of php scripts, then why not change it? seriously, it's going to be the easiest way for you, and will take 1 line of code. a call to 'mail' inserted in the right place (ie, after the INSERT/UPDATE query is called).
other ways (such as the cronjob idea) will require alot more effort. and even with a cronjob or a normal script, how do you propose to find out if a table has changed without having something to compare it to? ok, so you could log the 'mysql_num_rows', but what happens if a row is edited, and not inserted? you'll get the same number of rows back = no notification, etc.

just find the INSERT and UPDATE parts of the script and stick a 'mail' call after them. you could be done and dusted within about 10 minutes (9 mins to find the right part, 1 to add the modifications).

[wildteen88]

I agree with redbullmarky here. Just sitck your mail function right after the insert query when an alert is added. Its the best possible solution to this.

To make PHP email you it will only take one line of code, or a few lines to setup the message part of the email and the headers too. 10 lines max.

[Grande]

Ok' I'll look into it,
Thx for the help !!
Cheers

[wisewood]

if you are really that set on having it check the database for an update, why not have an autoincrementing integer for each entry... then use a javascript timer to run a query on the database every XX seconds/minutes or whatever. Compare the integer of the last entry of the table with the integer of the last integer of the table this time, and send an email if they're different.

It's a hell of a hassle when you could do as said and insert one line of code into the script thats already there though.

[Grande]

Yess , it was a hell of a hassle but it was a challenge ;)

I made it like this:
A relocater to the same page
[code]<meta http-equiv="refresh" content="5;
url=http://localhost/rulesen/test.php" />[/code]

And code that checks if the total number of rows changes, while logging the previous number of rows in a session variable
(query: $query2 = "SELECT count(*) as number FROM phptest"; )

[code]    while($next_user = mysql_fetch_array($result2)){
            global $user_count;
            $user_count = $next_user["number"];
            
            if(isset($_SESSION['count'])){
                //echo "session count is set";
                if ($_SESSION['count'] != $user_count){
                //echo "session count and user_count are different";
                mail($to, $subject, $message);
                }
            }
            
          //echo "Number of rows are: ".$user_count;
          // Use $HTTP_SESSION_VARS with PHP 4.0.6 or less
          $_SESSION['count']=$user_count;
    
    }
    //echo "variable session count is: ".$_SESSION['count'];[/code]

Anyway, thx everybody for the help

[wisewood]

clever. If you wanted to get really clever you could do the same thing using AJAX, which would allow you to do all the same stuff but without refreshing the page.

[Grande]

Could be, but sometimes your lazy and you use the stuff you allready know ;)
hehe