SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Guru
    Join Date
    Sep 2004
    Location
    Provo, UT
    Posts
    865
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Some rows are not being updated

    I'm fairly certain my code problem is related to mySQL. My website allows users to post experiences they have had with essential oils. I read over and manually approve each one and this is the code that is executed. The first query finds everyone who has searched using the keywords in $matchWords. Then a table is displayed showing each match, and some basic statistics. The problem is not all rows returned from the first query are being updated with the subsequent queries. You will see my //comments down below. Maybe there is a better way to do this? I'm open to any suggestions!

    Thanks!

    PHP Code:
    <?php
            $allMatches 
    "                SELECT           u.uID,           u.firstName,           u.lastName,           u.email,           s.sID,           date_format(s.searchDate, '%m-%d-%Y') as searched,           s.author,           s.type,           s.keyword,           s.results,           s.alerts,           date_format(s.lastAlert, '%m-%d-%Y') as lastAlert,           s.clicks,           date_format(s.lastClick, '%m-%d-%Y') as lastClick,           date_format(u.last, '%m-%d-%Y') as last         FROM users u        INNER         JOIN searches s           ON u.uID = s.author        WHERE         MATCH (keyword) AGAINST ('$matchWords' in boolean mode)         and u.sendMatches = 'Yes'         and u.lastMatch < CURRENT_DATE        GROUP BY         u.uID        ORDER BY         s.searchDate";                $allMatchesResult mysql_query($allMatches) OR die("<b>A fatal MySQL error occurred</b>.\n<br />Query: " $allMatches "<br />\nError: (" mysql_errno() . ") " mysql_error());                 if ($row mysql_fetch_array($allMatchesResult)) {                        $emailSent         0;            $deletedAlerts    0;                        print ("<br>");            print ("<p>Title: $summary</p>");                        print("<table>");                        print("<tr>");            print("<th>Search Date</th>");            print("<th>Name</th>");            print("<th>Log</th>");            print("<th>Search</th>");            print("<th>Alerts</th>");            print("<th>Last Alert</th>");            print("<th>Clicks</th>");            print("<th>Last Click</th>");            print("<th>CTR</th>");            print("<th>Last Login</th>");            print("</tr>");                        do {                                    $uID            =$row["uID"];                // ID of person conducting search                $last            =$row["last"];            // Their last login date                $sID            =$row["sID"];              // ID of zero result search                $searchDate        =$row["searched"];        // Date of search                $keyword        =$row["keyword"];        // Keyword that produced the zero results                $type            =$row["type"];          // The type of search they performed                $firstName        =$row["firstName"];        // Firstname of person who initiated the search                $lastName        =$row["lastName"];        // Lastname of person who initiated the search                $email            =$row["email"];            // Email address of person who initiated search                $results        =$row["results"];        // How many results from original search                $alerts            =$row["alerts"];        // How many alerts have they been sent for this                $lastAlert        =$row["lastAlert"];        // Last time we emailed them                $clicks            =$row["clicks"];        // How many times did they click to read more                $lastClick        =$row["lastClick"];        // Last time they clicked on the link                                                if ($type == "keyword" || $type=="advanced") {                                    if ($clicks > 0) {                                            $ctr = $clicks/$alerts;                        $percent = round($ctr * 100);                        $showPercent = "$percent%";                                        } else {                                            $percent = "0";                        $showPercent = "-";                    }                                                                                // Delete if click rate is too low                                        if ($alerts > 2 && $percent < 33) {                                              $deletedAlerts++;                                                                        print ("<tr><td>$searchDate</td> <td><a href ='editUsers.php?action=showUser&uID=$uID'>$firstName $lastName</a></td><td><a href='communicationPopup.php?action=view&uID=$uID' class='lbOn'>View</a></td> <td>$keyword</td> <td>$alerts</td><td>$lastAlert</td> <td>$clicks</td> <td>$lastClick</td> <td><font color='Red'>Deleted</font></td><td>$last</td></tr>");                                                 $deleteAlerts = "delete from searches where sID = '$sID'";                                                $result2 = mysql_query($deleteAlerts) OR die("<b>A fatal MySQL error occurred</b>.\n<br />Query: " . $deleteAlerts . "<br />\nError: (" . mysql_errno() . ") " . mysql_error());                                     } else {                                              // Click rate is good so send them today's email                                                    $alerts = $alerts + 1;                                                                        if ($clicks > 0) {                                                    $ctr = $clicks/$alerts;                            $percent = round($ctr * 100);                            $showPercent = "$percent%";                                                } else {                                                    $percent = "0";                            $showPercent = "-";                        }                                                                                                                        print ("<tr><td>$searchDate</td> <td><a href ='editUsers.php?action=showUser&uID=$uID'>$firstName $lastName</a></td> <td><a href='communicationPopup.php?action=view&uID=$uID' class='lbOn'>View</a></td><td>$keyword</td> <td>$alerts</td> <td>$lastAlert</td> <td>$clicks</td> <td>$lastClick</td> <td>$showPercent</td> <td>$last</td> </tr>");                                                 // This particular update is not happening to every single row that is returned                        // from the query at the top.                                                                                        $increaseAlerts = "UPDATE searches SET alerts = $alerts, lastAlert = '$longDate' where sID = $sID";                                                mysql_query($increaseAlerts) OR die("<b>A fatal MySQL error occurred</b>.\n<br />Query: " . $increaseAlerts . "<br />\nError: (" . mysql_errno() . ") " . mysql_error());                                                                         $emailSent++;                                                                        $testimonial_message = "This email tells the user that a new testimonial was posted that matches what they searched on.";                                                                        $testimonial_message    = stripslashes($testimonial_message);                        $testimonial_type        = "Search Match";                        $testimonial_subject    = "New testimonial match";                                                $testimonial_message = addslashes($testimonial_message);                                                                        $testimonial_update = "INSERT INTO notifications (date, priority, type, sender, recipient, subject, uID, message) VALUES (                                                '$longDate',                         '2',                        '$testimonial_type',                        '$sender',                        '$email',                         '$testimonial_subject',                         '$uID',                         '$testimonial_message')";                                                mysql_query($testimonial_update) OR die("<b>A fatal MySQL error occurred</b>.\n<br />Query: " . $testimonial_update . "<br />\nError: (" . mysql_errno() . ") " . mysql_error());                                                                                                 // Mark today's date so we can keep track of the last sent match.                                                                    $lastMatch = "UPDATE users SET lastMatch = '$longDate' WHERE uID = '$uID'";                                                mysql_query($lastMatch) OR die("<b>A fatal MySQL error occurred</b>.\n<br />Query: " . $lastMatch . "<br />\nError: (" . mysql_errno() . ") " . mysql_error());                                                                                         } // end else if ($alerts > 5 && $percent < 25)                                                    } // end if ($type == "keyword" || $type=="advanced")                                         } while($row = mysql_fetch_array($allMatchesResult));                                    print("<tr>");            print("<th colspan='10' align='center'>Total: $emailSent</th>");            print("</tr>");                        print("</table>");
    ?>
    Convert your dollars into silver coins. www.convert2silver.com

  2. #2
    SitePoint Guru
    Join Date
    Sep 2004
    Location
    Provo, UT
    Posts
    865
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Ugh, why doesn't this editor allow me to gracefully ad PHP code? It screws up the formatting. What am I doing wrong?
    Convert your dollars into silver coins. www.convert2silver.com

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,323
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    here's what i saw...
    Code:
    SELECT u.uID
         , u.firstName
         , u.lastName
         , u.email
         , s.sID
         , date_format(s.searchDate, '%m-%d-%Y') as searched
         , s.author
         , s.type
         , s.keyword
         , s.results
         , s.alerts
         , date_format(s.lastAlert, '%m-%d-%Y') as lastAlert
         , s.clicks
         , date_format(s.lastClick, '%m-%d-%Y') as lastClick
         , date_format(u.last, '%m-%d-%Y') as last   
      FROM users u  
    INNER   
      JOIN searches s 
        ON u.uID = s.author  
     WHERE MATCH (keyword) AGAINST ('$matchWords' in boolean mode)   
       and u.sendMatches = 'Yes'   
       and u.lastMatch < CURRENT_DATE  
    GROUP 
        BY u.uID  
    ORDER 
        BY s.searchDate
     -- 
     -- some incomprehensible php stuff --
     --  
    UPDATE searches 
       SET alerts = $alerts
         , lastAlert = '$longDate' 
     where sID = $sID
     --     
     -- some more incomprehensible php stuff --
     --   
    INSERT 
      INTO notifications 
         ( date
         , priority
         , type
         , sender
         , recipient
         , subject
         , uID
         , message ) 
    VALUES 
         ( '$longDate'
         , '2'
         , '$testimonial_type'
         , '$sender'
         , '$email'
         , '$testimonial_subject'
         , '$uID'
         , '$testimonial_message' )
    my guess is, the first query is not returning all the rows you want because of the GROUP BY

    you're only getting one search result from the searches table for each user

    is that what you want?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Guru
    Join Date
    Sep 2004
    Location
    Provo, UT
    Posts
    865
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Yes, Rudy, only returning one result per user is what I need. Sometimes a person will search on "back pain", "lower back pain" AND "peppermint oil for back pain". So if I don't put the group by line in there, this same person would get three separate email notifications alerting them to the fact that a new matching testimonial has been posted.....not good.

    Can you think of any other reason why the lastAlert column doesn't get updated for everyone found in the first query? It's very strange to me.

    Thanks!
    Convert your dollars into silver coins. www.convert2silver.com

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,323
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by busboy View Post
    Can you think of any other reason why the lastAlert column doesn't get updated for everyone found in the first query?
    nope, not me

    it's probably due to something in that incomprehensible part of what you posted

    i can only read the sql part, and it looks fine
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •