SitePoint Sponsor

User Tag List

Results 1 to 9 of 9

Thread: How to compare

  1. #1
    SitePoint Zealot fredep57's Avatar
    Join Date
    Aug 2009
    Location
    Pacific Northwest
    Posts
    137
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    How to compare

    OK, I have put into my mySQL database a date (the date only) as a varchar of 2. So, if, say today was May 18, 2011, I store the 18 in the varchar storedDate. Now I want to retrieve that and compare it with today. Isn't working for me.

    I am storing these broken out for a particular reason as I wanted to store the complete date (month day year) but other requirements is not allowing for that - might have been easier.

    Here is one way I compare it after my getting the row from the master query
    PHP Code:

    // this works
       
    if ($info['storedMonth'] ==  date("F"))
         echo 
    'Months match<br>';
       else
           echo 
    'oops, months not matching???<br>?';

    // this does not
        
    if (trim($info['storedDate']) == strtotime(date("j")))
          echo 
    'Dates match<br>';
        else
          echo 
    'hmmmmm. dates not matching!!!<br>'
    I also tried by removing the strtotime, trim and both. Not working.

    Any help would be greatly appreciated.

    F

  2. #2
    SitePoint Wizard cranial-bore's Avatar
    Join Date
    Jan 2002
    Location
    Australia
    Posts
    2,634
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Have you tried debugging by doing var_dump($info['storedDate']) and var_dump($date('j')) ?

    A better way would be to store your whole date in a DATE column and use that for the comparison.
    PHP Code:
    $info['date'] = '2011-05-18'//from MySQL DATE column

    if(date('j') == date('j'strtotime($info['date']))) {
      echo 
    'Days match';

    Or you can format the components in your SQL query
    Code SQL:
    SELECT 
       DATE_FORMAT(your_date_col, '%e') AS day_of_month,
       DATE_FORMAT(your_date_col, '%m') AS MONTH
    FROM your_table

    Then day_of_month = 18 and month = 05. You can compare those components individually.
    mikehealy.com.au
    diigital.com art, design . Latest WorkSaturday Morning

  3. #3
    SitePoint Zealot fredep57's Avatar
    Join Date
    Aug 2009
    Location
    Pacific Northwest
    Posts
    137
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    cranial-bore, thanks. You pointed me in the right direction.

    This worked

    PHP Code:
        $mNewdate $info['storedYear'].'-'.date("m",strtotime($info['storedMonth'])).'-'.$info['storedDate'];
        

        echo 
    'New Date concat: '.$mNewdate;     
        
        if(
    date('j') > date('j'strtotime($mNewdate))) 
          echo 
    'Days match';
       else
          echo 
    'nope,no match'
    I found another error - mine - in that I needed to get dates AFTER today - and this works like I want.

    F

  4. #4
    SitePoint Wizard cranial-bore's Avatar
    Join Date
    Jan 2002
    Location
    Australia
    Posts
    2,634
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Glad it's working, but I'd still recommend using a proper date column. More flexible for other things you may wish to do in future (e.g. sorting, selecting) and would use less space too.
    mikehealy.com.au
    diigital.com art, design . Latest WorkSaturday Morning

  5. #5
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    To build upon cranial-bore's suggestion, there are other mysql functions you might consider using to do similar.

    Code:
    select DAY(pubdate) from rss_news where id = 1;
    rss_news
    =========
    id | pubdate
    =========
    1 | '2011-02-13'

    returns '13'

    You can also use MONTH(), YEAR() and so on on date and datetime fields - overall though: avoid using varchars for dates.

  6. #6
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,506
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Quote Originally Posted by fredep57 View Post
    I wanted to store the complete date (month day year) but other requirements is not allowing for that
    Could you tell what requirements would not allow you to store the date in a date type column?

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,328
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Cups View Post
    returns '13'
    no, it returns 13

    there's a difference

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    9,095
    Mentioned
    153 Post(s)
    Tagged
    2 Thread(s)
    Off Topic:

    You're both right. MySQL returns 13, but PHP gets '13' because in PHP everything that comes from the database is a string.
    Rémon - Hosting Advisor

    SitePoint forums will switch to Discourse soon! Make sure you're ready for it!

    Minimal Bookmarks Tree
    My Google Chrome extension: browsing bookmarks made easy

  9. #9
    SitePoint Wizard bronze trophy Immerse's Avatar
    Join Date
    Mar 2006
    Location
    Netherlands
    Posts
    1,661
    Mentioned
    7 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by ScallioXTX View Post
    Off Topic:

    You're both right. MySQL returns 13, but PHP gets '13' because in PHP everything that comes from the database is a string.
    Off Topic:


    Ooh, that's a good one for Cups' thread!


Bookmarks

Posting Permissions

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