Trying to email just once from database

[b_radikal]

So, I'm trying to get this to email only once for the jobs that are at 75% of estimated cost, but something must be wrong because it keeps emailing everytime this code is called. Any help is appreciated.

    function display() {
        global $dbm;
        print '<span class="header">Open Jobs Report</span><p>';
        
        // Get the data for this report.
        $results = $dbm->select("SELECT j_jobnumber, j_desc, j_datedue, j_clientid, j_graphicest, j_printingest, j_scansest, j_warningest FROM jobs WHERE j_dateclosed IS NULL ORDER BY j_jobnumber");
        if ($dbm->num_rows($results) == 0) {
            print 'There are no open jobs.';
        } else {
            print '<table width="'.REPORT_WIDTH.'"><tr><th>Job Number</th><th>Description</th><th>Date Due</th><th>Est. Cost</th><th>Cost to Date</th></tr>';
            $color = '';
            while ($data = $dbm->fetch_array($results)) {
                // Determine the color for this line.
                if ($color == '') {
                    $color = ' class="color"';
                } else {
                    $color = '';
                }
            
                // Run subqueries to determine the cost to date.
                $costToDate = 0;
                $subData = $dbm->fetch_array($dbm->select("SELECT SUM(l_total) AS subtotal FROM labor WHERE l_jobnumber='" . $data['j_jobnumber'] . "'"));
                //$laborD = $dbm->fetch_array($dbm->select("SELECT l_jobnumber FROM labor"));
                $costToDate += $subData['subtotal'];
                $subData = $dbm->fetch_array($dbm->select("SELECT SUM(f_fee) AS subtotal FROM fees WHERE f_jobnumber='" . $data['j_jobnumber'] . "'"));
                //$feeD = $dbm->fetch_array($dbm->select("SELECT f_jobnumber FROM fees"));
                $costToDate += $subData['subtotal'];
                $subData = $dbm->fetch_array($dbm->select("SELECT SUM(m_total) AS subtotal FROM materials WHERE m_jobnumber='" . $data['j_jobnumber'] . "'"));
                //$materialD = $dbm->fetch_array($dbm->select("SELECT m_jobnumber FROM materials"));
                $costToDate += $subData['subtotal'];
                
                $estCost = "$" . number_format(($data['j_graphicest'] + $data['j_printingest'] + $data['j_scansest']), 2);
            
        // Email designer at 75% estimate used
        $to = 'pg.test@hotmail.com';
        $subject = "Job number " . $data['j_jobnumber'] . " has reached 75% of its estimate!";
        $message = "Job number " . $data['j_jobnumber'] . " has reached 75% of its estimate!\n
                    Job Description: " . $data['j_desc'] . "\n
                    Cost to date: " . "$" . number_format($costToDate, 2) . "\n
                    Estimated Cost: " . $estCost . "\n
                    Client ID Number: " . $data['j_clientid'] . "\n";
        $headers = 'From: pg.test@hotmail.com' . "\r\n" . 'Reply-To: pg.test@hotmail.com' . "\r\n" . 'X-Mailer: PHP/' . phpversion();
        
        // 75% of estimate used
        $nearingEstimate = 0;
        $tempestCost = $data['j_graphicest'] + $data['j_printingest'] + $data['j_scansest'];
        $tempcostToDate = $costToDate;
        if($tempestCost > 0) {
            $nearingEstimate = $tempcostToDate / $tempestCost;
        }
        if($nearingEstimate >= 0.75 && !$data['j_warningest']) {
            //send email to designer
            mail($to,$subject,$message,$headers);
            //update 0 to 1
            $updateSQL = ("UPDATE jobs SET(" . $data['j_warningest'] . "=1) WHERE l_jobnumber='" . $data['j_jobnumber'] . "' OR f_jobnumber='" . $data['j_jobnumber'] . "' OR m_jobnumber='" . $data['j_jobnumber'] . "'");
            mysql_query($updateSQL);
        }

                
                // Display the info on this job.
                print '<tr'.$color.'>';
                print '<td>'.$data['j_jobnumber'].'</td>';
                print '<td>'.$data['j_desc'].'</td>';
                print '<td>'.$data['j_datedue'].'</td>';
                print '<td align="right">'.$estCost.'</td>';
                print '<td align="right">$'.number_format($costToDate, 2).'</td>';
                print '</tr>';
            }
            print '</table>';
        }
        
        if (!$this->printVersion) {
            printVersionButton();
        }
    } // display 

[elgumbo]

You'd need at add a flag to the record after the email has been sent and then check for this flag on the original query.

Is this what you are trying to do with your update query?

$updateSQL = ("UPDATE jobs SET(" . $data['j_warningest'] . "=1) ...

[b_radikal]

That's exactly what I am trying to do with that line of code. j_warningest is the column I have defaulting to '0' and it's supposed to change to 1 at that line, but it never does. I don't know if I just wrote it wrong or if something else was wrong in the code.

[sneha24]

i guess the mistake is in this line

 $updateSQL = ("UPDATE jobs SET(" . $data['j_warningest'] . "=1) WHERE l_jobnumber='" . $data['j_jobnumber'] . "' OR f_jobnumber='" . $data['j_jobnumber'] . "' OR m_jobnumber='" . $data['j_jobnumber'] . "'"); 

it shud be

 $updateSQL = ("UPDATE jobs SET j_warningest=1 WHERE l_jobnumber='" . $data['j_jobnumber'] . "' OR f_jobnumber='" . $data['j_jobnumber'] . "' OR m_jobnumber='" . $data['j_jobnumber'] . "'"); 

[elgumbo]

You'd also need to check the value of j_warningest in your initial query to only get the jobs that have not been emailed yet.

 $results = $dbm->select("
SELECT j_jobnumber, j_desc, j_datedue, j_clientid, 
j_graphicest, j_printingest, j_scansest, j_warningest 
FROM jobs 
WHERE j_dateclosed IS NULL 

AND j_warningest=0

ORDER BY j_jobnumber"); 

[b_radikal]

maybe my problem comes from l_jobnumber being in the labor table, j_jobnumber being in the jobs table, m_jobnumber being in the materials table, and f_jobnumber being in the fees table? How would I be able to access them like that? Here's what I've tried and it hasn't worked yet, but I don't know if I've been coding it right.

$laborD = $dbm->fetch_array($dbm->select("SELECT l_jobnumber FROM labor"));
    $feeD = $dbm->fetch_array($dbm->select("SELECT f_jobnumber FROM fees"));
    $materialD = $dbm->fetch_array($dbm->select("SELECT m_jobnumber FROM materials"));
    if($nearingEstimate >= 0.75 && !($data['j_warningest'])) {
            //send email to designer
            ////////mail($to,$subject,$message,$headers);
            //update 0 to 1
            ////////echo "Job #: " . $data['j_jobnumber'];
            $updateSQL = ("UPDATE jobs SET j_warningest=1 WHERE (" . $laborD['l_jobnumber'] . "=j_jobnumber OR " . $feeD['f_jobnumber'] . "=j_jobnumber OR " . $materialD['m_jobnumber'] . "=j_jobnumber)");
            ////////echo " jwarn: " . $data['j_warningest'];
            mysql_query($updateSQL);
        }