#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Location
    Adelaide, South Australia
    Posts
    15
    Rep Power
    0

    Unhappy PHP Search MySQL between 2 dates


    Hi Guys,

    I've hit a bit of a brick wall with a project and have spent most of the morning and afternoon diving through forums/stackoverflow and just can't seem to get this working.

    Basically what i'm wanting to do is query a MySQL table and display all records within a specific date range.

    When I set the database up (quite a while ago) I set the date column as VARCHAR instead of DATE (don't ask why, because I don't know myself)

    I'm using the following:

    SELECT * FROM tablename WHERE columnname BETWEEN $startdate AND $enddate

    $startdate and $enddate are set just before the SELECT........... and for example look like this 01/07/2013 31/07/2013


    The dates as I look in the database are currently set to this format: DD/MM/YYYY (ie the 7th of december 2012 would look like this 07/12/2012)


    I've tried converting the few thousand records to YYYY-MM-DD with a UPDATE and then changing the column from VARCHAR to DATE but it makes all dates from this year (2013) jump up to 2020 and a bunch of whacky stuff.

    I assume its something to do with the VARCHAR column?

    Any help/guidance would be greatly appreciated.

    Thanks,
    Ben
  2. #2
  3. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    Hi,

    MySQL question -> MySQL forum. This has nothing to do with PHP. I'll ask the moderators to move it.



    Originally Posted by ben.y
    I assume its something to do with the VARCHAR column?
    No. Your "DD/MM/YYYY" simply isn't a valid MySQL date format.

    Create an additional DATE column, parse the VARCHAR stuff with STR_TO_DATE() and store the result in the new column.
    The 6 worst sins of security ē How to (properly) access a MySQL database with PHP

    Why canít I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Location
    Adelaide, South Australia
    Posts
    15
    Rep Power
    0
    Originally Posted by Jacques1
    Hi,

    MySQL question -> MySQL forum. This has nothing to do with PHP. I'll ask the moderators to move it.





    No. Your "DD/MM/YYYY" simply isn't a valid MySQL date format.

    Create an additional DATE column, parse the VARCHAR stuff with STR_TO_DATE() and store the result in the new column.
    I ended up working this out about 2 mins after I posted this but the forums were online and then offline like a yoyo last night so I gave up trying to post and let everyone know not to worry.

    Thanks
    Ben

IMN logo majestic logo threadwatch logo seochat tools logo