Broad range update not working but not erroring

OK I think I must be going insane.

On every page load (in the footer of my site) I run a series of update queries to automatically adjust status' of items based off start and end dates.

This appeared to be working when I first wrote it months ago and since nobody had complained to me I assumed it was still working. However I've recently (today) implemented an email alert system to the update the response to which was 300+ emails arriving - one for every page load - almost instantly which shouldn't happen as the update should adjust the status's and then the items should stop hitting the email flag as they'll be in the correct status for the date.

I've tracked the error down to the update queries but can't for the life of me work out why they're not working properly.

UPDATE `tblsites` SET ProjStatus =3 WHERE `SiteEndDate` < NOW( ) AND ProjStatus !=4;# MySQL returned an empty result set (i.e. zero rows)


SELECT GGInternalID
FROM `tblsites`
WHERE `SiteEndDate` < NOW( )
AND ProjStatus !=4; # Showing rows 0 - 29 (59 total, Query took 0.0416 sec)

The select should return zero as the update was done prior to it but as you can see the update returns zero affected rows.

Where am I going wrong.

(1) UPDATE never returns a result set. Never. So if that's what you are relying on, you goofed.

http://www.php.net/manual/en/function.mysql-query.php

For other type of SQL statements, INSERT, UPDATE, DELETE, DROP, etc, mysql_query() returns TRUE on success or FALSE on error.
...
Use mysql_num_rows() to find out how many rows were returned for a SELECT statement or mysql_affected_rows() to find out how many rows were affected by a DELETE, INSERT, REPLACE, or UPDATE statement.

(2) The NOW() function returns the current date *AND TIME* to the nearest MILLISECOND.

WHERE `SiteEndDate` < NOW( )

So if the UPDATE happened 1 millisecond ago (and it was probably more like 4 or 5 milliseconds) the SELECT would still pick up all those records.

If SiteEndDate is supposed to be a DATE-only field, then use CURDATE( ) instead of NOW( ).

Wait a minute!


UPDATE `tblsites` SET ProjStatus =3 WHERE `SiteEndDate` < NOW( ) AND ProjStatus !=4;

So now all those records have a ProjStatus of 3.

But that query *DOES NOT* change the SiteEndDate field in those records, at all.

So now you do

SELECT GGInternalID FROM `tblsites` WHERE `SiteEndDate` < NOW( ) AND ProjStatus !=4;

And since you just set all those ProjStatus values to 3, indeed that means ProjStatus != 4 is TRUE. And since you did *NOT* change the value of SiteEndDate then all the records that were update will *STILL* be found!

If you only want this to happen once per day, you of course need to use CURDATE() in place of NOW() but you also need to update the SiteEndDate when you change ProjStatus. So:


UPDATE tblsites SET ProjStatus =3, SiteEndDate = CURDATE()
WHERE SiteEndDate < CURDATE() AND ProjStatus !=4;