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 10 of 10
  1. #1
    Regular Coder hinch's Avatar
    Join Date
    Sep 2005
    Location
    UK
    Posts
    923
    Thanks
    25
    Thanked 80 Times in 80 Posts

    Date Diffs (the bane of my existence)

    I have a column in a mysql table that stored a mysql based NOW() date in the following format

    2008-07-22 14:46:10

    What I need to do is something like this against the field

    PHP Code:
    if ($ffexists[5]<{3months prior to current date}) {
    // do something
    } else if (($ffexists[5]<{6months prior to current date}) && ($ffexists[1]==1)) {
    //do something else 
    } else {
    //do nothing

    Its a little more complex in reality but that I can resolve my main sticking point is calculating the date 3 or 6 months from NOW() and converting it to a mysql format date and then doing the if comparison so if its 3 months and 1 second prior to now it flags it but if its still 1 minute before the 3 months ie: 2 months something it does nothing.

    any help will be much appreciated
    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
    Master Coder
    Join Date
    Jun 2003
    Location
    Cottage Grove, Minnesota
    Posts
    9,471
    Thanks
    8
    Thanked 1,085 Times in 1,076 Posts
    I don't know if this helps or not ....

    The link below is about using a UNIX timestamp.
    I like timestamp because the resolution is in seconds.
    You can do math on it easily, down to the second.

    Perhaps you can keep the existing date format and add another
    column that represents it in timestamp form ... use that for calculations?

    http://dev.mysql.com/doc/refman/5.1/en/datetime.html

  • #3
    Senior Coder
    Join Date
    Aug 2009
    Location
    Mansfield, Nottinghamshire, UK
    Posts
    1,555
    Thanks
    57
    Thanked 148 Times in 147 Posts
    PHP Code:
    <?php
    $monthAmount 
    3//how many months in the future
    $now time();
    $time1 strtotime("2008-07-22 14:46:10 + ".$monthAmount." months");

    echo 
    $now."<br />".$time1;

    if(
    $now >= $time1)
    {
        echo 
    "arghhh!! ".$monthAmount." months+ have passed since data was inserted!!!! oh dear god!";
    }
    ?>
    Last edited by Phil Jackson; 09-21-2009 at 09:25 PM.
    Website Design Mansfield
    PHP Code:
    function I_LOVE(){function b(&$b='P'){$b.='P';}function a($_){return $_++;}$b='P';define("B",'H');b($b=implode('',array($b=a($b),$b=a(B))));b($b);return $b;}
    echo 
    I_LOVE(); 

  • Users who have thanked Phil Jackson for this post:

    hinch (09-22-2009)

  • #4
    Regular Coder hinch's Avatar
    Join Date
    Sep 2005
    Location
    UK
    Posts
    923
    Thanks
    25
    Thanked 80 Times in 80 Posts
    Quote Originally Posted by mlseim View Post
    I don't know if this helps or not ....

    The link below is about using a UNIX timestamp.
    I like timestamp because the resolution is in seconds.
    You can do math on it easily, down to the second.

    Perhaps you can keep the existing date format and add another
    column that represents it in timestamp form ... use that for calculations?

    http://dev.mysql.com/doc/refman/5.1/en/datetime.html
    not an option unfortunately I already thought about that its on a legacy system with about 8 million records in the table so i'd have to run a process to convert all existing dates over to unix time to populate the new colum (legacy data needs to persist for FSA regulations )
    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

  • #5
    Regular Coder hinch's Avatar
    Join Date
    Sep 2005
    Location
    UK
    Posts
    923
    Thanks
    25
    Thanked 80 Times in 80 Posts
    Quote Originally Posted by Phil Jackson View Post
    PHP Code:
    <?php
    $monthAmount 
    3//how many months in the future
    $now time();
    $time1 strtotime("2008-07-22 14:46:10 + ".$monthAmount." months");

    echo 
    $now."<br />".$time1;

    if(
    $now >= $time1)
    {
        echo 
    "arghhh!! ".$monthAmount." months+ have passed since data was inserted!!!! oh dear god!";
    }
    ?>

    that may work i'll have a bash at it in the morning see what I come up with.
    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

  • #6
    Regular Coder hinch's Avatar
    Join Date
    Sep 2005
    Location
    UK
    Posts
    923
    Thanks
    25
    Thanked 80 Times in 80 Posts
    worked nicely thanks phil.

    after making it fit the existing code base and fields etc I ended up with this.

    PHP Code:
    $statussql "SELECT LeadStatus FROM `tblleads` WHERE ID=".mysql_real_escape_string($_GET['cid']);
            
    $statres mysql_query($statussql);
            if (!
    $statres) {
                    die(
    'Invalid query: ' mysql_error());
                }
            
    $statusid mysql_fetch_row($statres);
            if ((
    $statusid[0]==5) || ($statusid[0]==6)) {
                
    $monthAmount 6//how many months in the future
            
    } else {
                
    $monthAmount 3//how many months in the future
            
    }
            
            
    $now time();
            
    $time1 strtotime($ffexists[5]." + ".$monthAmount." months");
            
            
    //echo $now."<br />".$time1;
            
            
    if($now >= $time1) {
                
    $updatesql2 "UPDATE `tblfactfind` SET govbonds=0,property=0,ftse=0,aim=0,spreadbetting=0,guarantee=0,statement=0 WHERE LID=".mysql_real_escape_string($_GET['cid']);
                
    $updateres mysql_query($updatesql2);
                if (!
    $updateres) {
                    die(
    'Invalid query: ' mysql_error());
                }
            } 
    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

  • #7
    Senior Coder
    Join Date
    Aug 2009
    Location
    Mansfield, Nottinghamshire, UK
    Posts
    1,555
    Thanks
    57
    Thanked 148 Times in 147 Posts
    no worries glad it worked :-)
    Website Design Mansfield
    PHP Code:
    function I_LOVE(){function b(&$b='P'){$b.='P';}function a($_){return $_++;}$b='P';define("B",'H');b($b=implode('',array($b=a($b),$b=a(B))));b($b);return $b;}
    echo 
    I_LOVE(); 

  • #8
    Regular Coder hinch's Avatar
    Join Date
    Sep 2005
    Location
    UK
    Posts
    923
    Thanks
    25
    Thanked 80 Times in 80 Posts
    Quote Originally Posted by Phil Jackson View Post
    no worries glad it worked :-)
    yeah saved me considerably white hair and bald patches.

    if there's 2 things i've never been able to get the hang of in php since switching from primarily c# its dates/times/timestamps and regex's everything else has been a fairly straight forward switch over but those 2 things are just killing me.
    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

  • #9
    Senior Coder
    Join Date
    Aug 2009
    Location
    Mansfield, Nottinghamshire, UK
    Posts
    1,555
    Thanks
    57
    Thanked 148 Times in 147 Posts
    man, wish id started with C#. Look at it once then went to bed and that was that! creating classes is my downfall. It was regex but now i kinda like it!

    P.S. You can never get your head around PHP, there's a function for everything! i find a new one everyday!
    Website Design Mansfield
    PHP Code:
    function I_LOVE(){function b(&$b='P'){$b.='P';}function a($_){return $_++;}$b='P';define("B",'H');b($b=implode('',array($b=a($b),$b=a(B))));b($b);return $b;}
    echo 
    I_LOVE(); 

  • #10
    Regular Coder hinch's Avatar
    Join Date
    Sep 2005
    Location
    UK
    Posts
    923
    Thanks
    25
    Thanked 80 Times in 80 Posts
    not just a function for everything but in the case of regex's and str functions in particular there's 3-4 in most cases that could all be used to achieve the same thing.

    The one thing I've yet to find a real use for though in php is OO based design. I'm still yet to see any real need for it over just well written reusuable functions all it seems to do is add overhead to the page load since its interperated and not compiled I just can't see any benefit for them.
    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


  •  

    Posting Permissions

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