Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3
  1. #1
    Regular Coder hinch's Avatar
    Join Date
    Sep 2005
    Location
    UK
    Posts
    923
    Thanks
    25
    Thanked 80 Times in 80 Posts

    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.

    PHP Code:
    UPDATE `tblsitesSET 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.
    A programmer is just a tool which converts caffeine into code

    My work: http://www.fcsoftware.co.uk && http://www.firstcontactcrm.com
    My hobby: http://www.angel-computers.co.uk
    My life: http://www.furious-angels.com

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,020
    Thanks
    75
    Thanked 4,323 Times in 4,289 Posts
    (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.
    Code:
    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( ).
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,020
    Thanks
    75
    Thanked 4,323 Times in 4,289 Posts
    Wait a minute!

    Code:
    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
    Code:
    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:

    Code:
    UPDATE tblsites SET ProjStatus =3, SiteEndDate = CURDATE()
    WHERE SiteEndDate < CURDATE() AND ProjStatus !=4;
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Posting Permissions

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