jeeves245 Posted July 4, 2009 Share Posted July 4, 2009 Hi guys, i'm currently building a website for a friend. It's 90% finished, but i'm having a few issues which I can't figure out. Firstly, formatting a date in MySQL.. I've tried several different uses of DATE_FORMAT but I can't get it right. Could anyone give me any pointers? The date needs to be inserted as DD-MM-YYYY AND outputted as DD-MM-YYYY. The insert code: $sql="INSERT INTO TABLE_DELIVERIES (PSNUMBER, DELIVERYDATE) VALUES ('$_POST[psnumber]','$_POST[deliverydate]')"; The output code: ("SELECT PSNUMBER,DELIVERYDATE FROM TABLE_DELIVERIES"); I have tried date_format(DELIVERYDATE,'%d/%m/%Y') (found somewhere on Google) but it didn't work (date doesn't output at all). Any info/tips would be greatly appreciated Cheers Quote Link to comment Share on other sites More sharing options...
fenway Posted July 4, 2009 Share Posted July 4, 2009 MySQL date format is YYYY-MM-DD... store it this way, and use DATE_FORMAT() to make it pretty on the way out. Quote Link to comment Share on other sites More sharing options...
jeeves245 Posted July 4, 2009 Author Share Posted July 4, 2009 MySQL date format is YYYY-MM-DD... store it this way, and use DATE_FORMAT() to make it pretty on the way out. Yep that's what i'm trying to do.. but no luck so far.. Quote Link to comment Share on other sites More sharing options...
The Eagle Posted July 4, 2009 Share Posted July 4, 2009 DATE() DATE_FORMAT() THEDATE() Quote Link to comment Share on other sites More sharing options...
jeeves245 Posted July 4, 2009 Author Share Posted July 4, 2009 DATE() DATE_FORMAT() THEDATE() Thanks for your reply, but that doesn't mean much to me. I know a decent amount about PHP but i'm still a bit of a beginner... I was hoping someone would be able to tell me how to write the line of code (or at least point me in the right direction) Quote Link to comment Share on other sites More sharing options...
PFMaBiSmAd Posted July 4, 2009 Share Posted July 4, 2009 To format your incoming DD-MM-YYYY as a DATE data type (YYYY-MM-DD), you can explode it on the '-' character, which will also allow you to validate it (all external data cannot be trusted and must be validated before you attempt to use it and in this case you must also use mysql_real_escape_string on the resulting value put into the query to prevent sql injection) using checkdate. You can then produce the YYYY-MM-DD format from the exploded parts. To select a DATE value (YYYY-MM-DD) and format it the way you want, use the mysql DATE_FORMAT() function in your select query. I would recommend also using an alias name for the value to make referencing it in the php code easier. The '%d/%m/%Y' format string you came up with does not match the stated format of DD-MM-YYYY. It would need to be '%d-%m-%Y' Quote Link to comment Share on other sites More sharing options...
PFMaBiSmAd Posted July 4, 2009 Share Posted July 4, 2009 Here is further information on your $_POST[deliverydate] DD-MM-YYYY values. Expecting a human to consistently and correctly proved you a value in that format is not going to happen. You are going to get all possible variations - DD-MM-YY, DD/MM/YYYY, DD/MM/YY, DD MM YYYY, DD MM YY, MM-DD-YYYY, MM-DD-YY, MM/DD/YYYY, MM/DD/YY, MM DD YYYY, MM DD YY, and a whole bunch of other possibilities (even if you have a label and an example showing what you want.) The best method is to use clearly labeled, separate, drop-down-selects for each part that makes up the date. Even if someone does not read the labels, seeing a drop-down with 1-12 is a pretty big hint that it means months, and a separate drop down with 1-31 is a pretty big hint that it means days. Quote Link to comment Share on other sites More sharing options...
fenway Posted July 4, 2009 Share Posted July 4, 2009 Plus most date widgets do this anyway. Quote Link to comment Share on other sites More sharing options...
jeeves245 Posted July 5, 2009 Author Share Posted July 5, 2009 Thanks for the help. I'll keep working on it and let you know how I go Quote Link to comment Share on other sites More sharing options...
jeeves245 Posted July 5, 2009 Author Share Posted July 5, 2009 Done! Thanks guys. I ended up changing the database a bit, and instead of having the one date field, I now have DAY, MONTH, YEAR, and then drop down boxes on the input page so no one can mess it up. I have another question now though... I use this code to return all data from the database: $result = mysql_query("SELECT PSNUMBER,DAY,MONTH,YEAR FROM TABLE_DELIVERIES"); The data is listed down the page from the page from the oldest added field first. Is there any way to list it from the most recently added field first? Cheers. Quote Link to comment Share on other sites More sharing options...
PFMaBiSmAd Posted July 5, 2009 Share Posted July 5, 2009 I now have DAY, MONTH, YEAR That means that every query that tries to find a date will be more complicated and slower than if it was a DATE data type. Is there any way to list it from the most recently added field first Yes, use a DATE data type and just use - ORDER BY your_date_field DESC The different data types exist for a reason, use them. If you have a date, store it in a DATE data type. Quote Link to comment Share on other sites More sharing options...
jeeves245 Posted July 5, 2009 Author Share Posted July 5, 2009 I now have DAY, MONTH, YEAR That means that every query that tries to find a date will be more complicated and slower than if it was a DATE data type. Yes I realise this, but it's a very simple database that will never have anymore than about 10 fields... so i'm just taking the easy way out. Cheers for the info. Quote Link to comment Share on other sites More sharing options...
haku Posted July 5, 2009 Share Posted July 5, 2009 Why not take the time to learn to do it properly? The point isn't even that it's split into three sections, it's that you lose all the benefits of having a date column in your database. Quote Link to comment Share on other sites More sharing options...
fenway Posted July 5, 2009 Share Posted July 5, 2009 Yes I realise this, but it's a very simple database that will never have anymore than about 10 fields... so i'm just taking the easy way out. Oh, it's definitely not going to easier to "fake" a DATE. Quote Link to comment Share on other sites More sharing options...
jeeves245 Posted July 9, 2009 Author Share Posted July 9, 2009 Ok i've unsolved this topic because I have some more questions.. I've been thinking about it,and I guess using 3 separate fields for the date is probably a bad idea. But i'm still extremely confused on how to use the date_format function. I need the date entered by the user to be in DD-MM-YYYY format, and then I need it outputted in the same format when I go to select it from the database. Can anyone help me out with this? I'm guessing it's not too hard...... Also, is it possible to make it so the user adds DD-MM to the text box, and PHP adds the current year itself? Any more info is appreciated Quote Link to comment Share on other sites More sharing options...
jeeves245 Posted July 10, 2009 Author Share Posted July 10, 2009 Anyone? Quote Link to comment Share on other sites More sharing options...
PFMaBiSmAd Posted July 10, 2009 Share Posted July 10, 2009 Reply #5 in this thread already gave methods of breaking apart the incoming format and producing a DATE format from the exploded parts and gave the correct format string to use in the mysql DATE_FORMAT() function to get a DATE type back into the desired format. Quote Link to comment Share on other sites More sharing options...
jeeves245 Posted July 10, 2009 Author Share Posted July 10, 2009 Reply #5 in this thread already gave methods of breaking apart the incoming format and producing a DATE format from the exploded parts and gave the correct format string to use in the mysql DATE_FORMAT() function to get a DATE type back into the desired format. Yes but this means nothing to me. Do you know of any tutorials or something that will explain it in more depth? As I said, i'm a beginner... Quote Link to comment Share on other sites More sharing options...
fenway Posted July 15, 2009 Share Posted July 15, 2009 I need the date entered by the user to be in DD-MM-YYYY format Doesn't matter at all... you can still put the pieces back together so that you use a date field properly. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.