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.
SELECT
date_format(s.searchDate, '%m-%d-%Y') as searchDate,
u.uID,
u.totalSearches,
date_format(u.lastMatch, '%m-%d-%Y') as lastMatch,
s.type,
s.keyword,
s.alerts,
date_format(s.lastAlert, '%m-%d-%Y') as lastAlert,
datediff(now(), s.lastAlert) AS sinceLastAlert,
s.clicks,
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')
GROUP BY
u.uID
ORDER BY
s.lastAlert;
Once the email is generated this update is executed for each matching search:
UPDATE searches SET alerts = alerts +1, lastAlert = '$longDate' where sID = $sID
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.
Any thoughts?
Thanks!