Send email on new mysql insert

[ryanlitwiller]

With the help of another php freak member I have created a script that scraps the last 50 posts on a forum site it grabs the title, url, and date and populates my database. I also have an auto increment id. 

 

I have also made a function using the Mail.php package that successfully sends me an email, I would like to call my email function anytime a new insert is made into my db, although I do not want 50 emails for the first 50 entries, just the new ones, which I intend to set up a cron job to check for a new entries. However I am having a hard time coding the proper sql and php syntax. I figured possibly if does not exist then insert and call mail function. Or use $_Get  to grab my column fields in variables and then put it in an if statement greater than 50. I have not been able to come up with anything promising. Any help would be greatly appreciated. I will provide a link to my site and can post my code if needed but it is rather long so let me know. THanks!

 

Ryan

 

http://php-ryanlitwiller.rhcloud.com/indexhelp.php

[zekova]

All you need to do is run your sendmail script after inserting the data into mysql if it's a new record.

 

For checking existing records, look into MySQL count. It will return a number of records. Use php if statement to decide if you should do something.

 

Good luck!

[ryanlitwiller]

yes I have tired that and it does send me mail, but I do my insert in a foreach loop and I have print my results to the page prior to inserting and when I put my script after it kills my foreach after two records

    //access the new links array
    foreach($meta_array as $link){

    echo "<p>";
    echo "<a href='".$link['href']."' target='_blank'>".$link['title']."</a><br />";
    echo " Date: ".$link['date']." Time: ".$link['time']." ".$link['timezone']."<br />"; //Posted by: ".$link['username_first']." ".$link['username_last']."<br />";
    echo "</p>";
		    //convert array to string for db
		    $link_href = $link['href'];
		    $link_title = $link['title'];
		    $link_date = $link['date'] . " " . $link['time'];
		    //populate db
		    mysql_query("insert into `bp` (`url`,`title`,`date`) values ('$link_href','$link_title','$link_date')")
		    //send email
                    sendMail();
		  

[ryanlitwiller]

Well I am pretty close to having the solution. 

  foreach($meta_array as $link){

    echo "<p>";
    echo "<a href='".$link['href']."' target='_blank'>".$link['title']."</a><br />";
    echo " Date: ".$link['date']." Time: ".$link['time']." ".$link['timezone']."<br />"; //Posted by: ".$link['username_first']." ".$link['username_last']."<br />";
    echo "</p>";
		    //convert array to string for db
		    $link_href = $link['href'];
		    $link_title = $link['title'];
		    $link_date = $link['date'] . " " . $link['time'];
		    
		    
		    $result = mysql_query("select * from bp");
		    $number_of_rows_before = mysql_num_rows($result);
		    echo "Number of rows fetched are : ". $number_of_rows_before;
		  
		    //populate db
		    mysql_query("insert into `bp` (`url`,`title`,`date`) values ('$link_href','$link_title','$link_date')");
		    $number_of_rows_after = mysql_num_rows($result);
		    echo "Number of rows fetched are : ". $number_of_rows_after;
		    
		    if($number_of_rows_after>$number_of_rows_before) 
		    	{
		    	sendMail();
		    	}

I have echoed the values for testing and the only problem I am having is that my sendMail() function always get called whether the if statement is true or false.

Edited April 9, 2014 by ryanlitwiller

[maxxd]

You're getting the number of rows in the table on line 113, inserting a record on 117, then checking the number of rows in the table on 118. Unless the insert fails, the total on line 118 is always going to be 1 more than the total on line 113. If you want to e-mail yourself only after 50 records have been inserted, get the total number of rows in the table *before* your foreach() loop, add 50 to that, and increment a counter inside your foreach(). Once the counter plus the initial row count equals or is greater than the initial row count plus your cut-off for not sending e-mails, send yourself an e-mail.

[ryanlitwiller]

one of my columns in my database is unique to prevent duplicate entries, so if a new post has not been published there will not be a new insert. I am not having any problems with my if statement condition but what I am having problems with is that my function gets called regardless of wether or not the if statement is true

Edited April 9, 2014 by ryanlitwiller