SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Enthusiast
    Join Date
    Jun 2008
    Location
    Canton, OH
    Posts
    61
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Trying to email just once from database

    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.

    PHP Code:
        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($costToDate2) . "\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($costToDate2).'</td>';
                    print 
    '</tr>';
                }
                print 
    '</table>';
            }
            
            if (!
    $this->printVersion) {
                
    printVersionButton();
            }
        } 
    // display 

  2. #2
    SitePoint Evangelist elgumbo's Avatar
    Join Date
    Nov 2002
    Location
    North West, UK
    Posts
    545
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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) ...

  3. #3
    SitePoint Enthusiast
    Join Date
    Jun 2008
    Location
    Canton, OH
    Posts
    61
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  4. #4
    SitePoint Addict
    Join Date
    Mar 2009
    Posts
    226
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    i guess the mistake is in this line
    PHP Code:
     $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
    PHP Code:
     $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'] . "'"); 

  5. #5
    SitePoint Evangelist elgumbo's Avatar
    Join Date
    Nov 2002
    Location
    North West, UK
    Posts
    545
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

    PHP Code:
     $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"
    ); 

  6. #6
    SitePoint Enthusiast
    Join Date
    Jun 2008
    Location
    Canton, OH
    Posts
    61
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

    PHP Code:
    $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);
            } 


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •