only send one mail to user.

[hanwei]

hi,

the name of the script is testmail.php
[code]<?

$link = mysql_connect("127.0.0.1", "mysql") or die("Could not connect: " . mysql_error());
mysql_select_db("ttmodule", $link) or die("Unable to select database: . mysql_error()");



$to = "hanwei<[email protected]>";
$subject = "New Ticket Created";
$message = "";


$selectSqlOne= "SELECT * FROM indexpri ORDER BY indexPriNo ASC ";
$resultOne = mysql_query($selectSqlOne, $link);
if(!$resultOne) die("Invalid Query: " .mysql_error() ."<br>" .$selectSqlOne);


while($row1 = mysql_fetch_array($resultOne))
{

$secondaryTbl = $row1["indexSecTbl"];
$selectSqlTwo= "SELECT * FROM $secondaryTbl ORDER BY indexSecNo ASC ";
$resultTwo = mysql_query($selectSqlTwo, $link);
if(!$resultTwo) die("Invalid Query: " .mysql_error() ."<br>" .$selectSqlTwo);


while($row2 = mysql_fetch_array($resultTwo))
{



$mainTbl = $row2["mainTbl"];
$selectSqlThree = "SELECT ttNo, ttCompany, ttStatus, ttEngrAsgn, ttNewTime FROM $mainTbl where ttStatus=='New' ORDER BY ttNo";
$resultThree = mysql_query($selectSqlThree, $link);
if(!$resultThree) die("Invalid Query: " .mysql_error() ."<br>" .$selectSqlThree);


while($row3=mysql_fetch_array($resultThree))
{


$no=$row3["ttNo"];
$comp=$row3["ttCompany"];
$stat=$row3["ttStatus"];
$engr=$row3["ttEngrAsgn"];
$time=$row3["ttNewTime"];


$message .= "Ticket Number:"."\n".$no."<br></br>"."Company:"."\n".$comp."<br></br>"."Status:"."\n".$stat."<br></br>"."Raised Date:"."\n".$time."<br></br>"."Engineer Assigned:"."\n".$engr."<br></br>"."\n------------------------------------"."<br></br>";

$headers = "From: TTM\r\n" .
      'X-Mailer: PHP/' . phpversion() . "\r\n" .
      "MIME-Version: 1.0\r\n" .
      "Content-Type: text/html; charset=utf-8\r\n" .
      "Content-Transfer-Encoding: 8bit\r\n\r\n";


}
}
}

mail($to, $subject, $message, $headers);


?>[/code]

the code above will send a mail to the user where ttStatus='New'.

if i use the crontab below, it will send a mail every minute to the user:
* * * * * /usr/local/bin/php -q /usr/local/apache2/htdocs/Test/testmail.php

but how do i send only 1 mail to the user to inform him that there's a ticket which is in status New, is created? i don't want to spam the user with this mail every minute. is there any way to do it?

thanks in advance...

[Orio]

Add another column "mail_sent" set to 0 or 1. Send emails only to people that have mail_sent on 0, and then update it to 1.

Orio.

[hanwei]

thanks alot.