I store the searches that my users conduct. Then, when new testimonials are posted, the following query is run. It finds matches and emails the person to let them know of the new posting.
Once the email is generated this update is executed for each matching search:
date_format(s.searchDate, '%m-%d-%Y') as searchDate,
date_format(u.lastMatch, '%m-%d-%Y') as lastMatch,
date_format(s.lastAlert, '%m-%d-%Y') as lastAlert,
datediff(now(), s.lastAlert) AS sinceLastAlert,
date_format(s.lastClick, '%m-%d-%Y') as lastClick,
date_format(u.lastLogin, '%m-%d-%Y') as lastLogin
FROM searches s
INNER JOIN users u
ON s.uID = u.uID
WHERE (MATCH (keyword) AGAINST ('wrinkles' in boolean mode) or keyword in (""))
and u.sendMatches = 'Yes'
and u.bouncing = 'No'
and s.type in ('basic', 'advanced')
The problem occurs when a new testimonial is approved. Somehow the update query isn't applied to every single row of the matches that were made in the first query above. Sometimes it's just a few rows, other times 30-40 rows don't get updated out of roughly 1500 matches. Because its a sporadic issue, it makes it difficult to pinpoint why this is happening.
UPDATE searches SET alerts = alerts +1, lastAlert = '$longDate' where sID = $sID