Jump to content


Photo

Script for detecting changes in table


  • Please log in to reply
13 replies to this topic

#1 Grande

Grande
  • Members
  • PipPip
  • Member
  • 12 posts

Posted 31 March 2006 - 11:16 AM

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

#2 elementz

elementz
  • Members
  • PipPipPip
  • Advanced Member
  • 36 posts

Posted 31 March 2006 - 11:41 AM

Use cronjob?

#3 Grande

Grande
  • Members
  • PipPip
  • Member
  • 12 posts

Posted 31 March 2006 - 12:25 PM

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

#4 elementz

elementz
  • Members
  • PipPipPip
  • Advanced Member
  • 36 posts

Posted 31 March 2006 - 12:40 PM

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

#5 Grande

Grande
  • Members
  • PipPip
  • Member
  • 12 posts

Posted 31 March 2006 - 12:49 PM

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

#6 redbullmarky

redbullmarky
  • Staff Alumni
  • Advanced Member
  • 2,863 posts
  • LocationBedfordshire, England

Posted 31 March 2006 - 01:24 PM

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
"you have to keep pissing in the wind to learn how to keep your shoes dry..."

I say old chap, that is rather amusing!

#7 Grande

Grande
  • Members
  • PipPip
  • Member
  • 12 posts

Posted 31 March 2006 - 01:52 PM

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 . . .




#8 redbullmarky

redbullmarky
  • Staff Alumni
  • Advanced Member
  • 2,863 posts
  • LocationBedfordshire, England

Posted 31 March 2006 - 03:27 PM

[!--quoteo(post=360358:date=Mar 31 2006, 02:52 PM:name=Grande)--][div class=\'quotetop\']QUOTE(Grande @ Mar 31 2006, 02:52 PM) View Post[/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).


"you have to keep pissing in the wind to learn how to keep your shoes dry..."

I say old chap, that is rather amusing!

#9 wildteen88

wildteen88
  • Staff Alumni
  • Advanced Member
  • 10,482 posts
  • LocationUK, Bournemouth

Posted 31 March 2006 - 05:47 PM

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.

#10 Grande

Grande
  • Members
  • PipPip
  • Member
  • 12 posts

Posted 10 April 2006 - 08:05 AM

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

#11 wisewood

wisewood
  • Members
  • PipPipPip
  • Advanced Member
  • 226 posts
  • LocationRotherham, England

Posted 10 April 2006 - 10:48 AM

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.
wisewood: proven fact, I am both wise, and wooden.

#12 Grande

Grande
  • Members
  • PipPip
  • Member
  • 12 posts

Posted 10 April 2006 - 02:19 PM

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

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

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"; )

    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'];

Anyway, thx everybody for the help

#13 wisewood

wisewood
  • Members
  • PipPipPip
  • Advanced Member
  • 226 posts
  • LocationRotherham, England

Posted 10 April 2006 - 03:02 PM

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.
wisewood: proven fact, I am both wise, and wooden.

#14 Grande

Grande
  • Members
  • PipPip
  • Member
  • 12 posts

Posted 11 April 2006 - 10:37 AM

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




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users