fife Posted March 26, 2009 Share Posted March 26, 2009 hi. I have a simple form to get a users details and one of the details is D.O.B formatted dd/mm/yy. Now when it goes to my sql database its my understanding it has to be yy/mm/dd. Im new to this and ive search high and low for the answer. can anyone help? Here is a short version of the php code and a very short version of the html: <?php $insertform = $_SERVER['PHP_SELF']; if (isset($_SERVER['QUERY_STRING'])) { $insertform .= "?" . htmlentities($_SERVER['QUERY_STRING']); } if ((isset($_POST["insert"])) && ($_POST["insert"] == "insertform")) { $insertSQL = sprintf("INSERT INTO Members (`FirstName`, `LastName`, `DOB`, ) VALUES %s, %s, %s)", GetSQLValueString($_POST['FirstName'], "text"), GetSQLValueString($_POST['LastName'], "text"), GetSQLValueString($_POST['DOB'], "date"), mysql_select_db($database_db, $db); $Result1 = mysql_query($insertSQL, $db) or die(mysql_error()); ?> <html> <body> <form action="<?php echo $insertform; ?>" method="post" name="insertform" id="insertform"> <input type="text" name="FirstName" value="" size="20" /> <input type="text" name="LastName" value="" size="20" /> <input type="text" name="DOB" size="15" /> </form> </body> </html> Link to comment https://forums.phpfreaks.com/topic/151249-date-formatting/ Share on other sites More sharing options...
micah1701 Posted March 26, 2009 Share Posted March 26, 2009 MySQL stores dates as YYYY-MM-DD there are a zillion things you can do. look into strtotime() and date() functions. The simplest thing though, since you know the format coming in and just need to convert it is to explode the string and reorganize the values: <?php $users_input_dob = "31/12/95"; $parts = explode("/",$users_input_dob); //$parts[0] = 31 $parts[1] = 12 $parts[2] = 95 $newDate = "19".$parts[2]."-".$parts[1]."-".$parts[0]; echo $newDate; //returns 1995-12-31 Link to comment https://forums.phpfreaks.com/topic/151249-date-formatting/#findComment-794506 Share on other sites More sharing options...
redarrow Posted March 26, 2009 Share Posted March 26, 2009 or use varchar and any format you want. Link to comment https://forums.phpfreaks.com/topic/151249-date-formatting/#findComment-794507 Share on other sites More sharing options...
micah1701 Posted March 26, 2009 Share Posted March 26, 2009 or use varchar and any format you want. thats a good idea too - unless later you're going to want to compare dates in mysql (such as searching for users born before 1985 or who are x number of years old). Link to comment https://forums.phpfreaks.com/topic/151249-date-formatting/#findComment-794509 Share on other sites More sharing options...
taquitosensei Posted March 26, 2009 Share Posted March 26, 2009 if it's a date or datetime field in MySQL it's Y-m-d H:i:s format it before you insert $date_field=date("Y-m-d", strtotime($date)); $sql="insert into yourtable(datefield) values('$date_field')"; Link to comment https://forums.phpfreaks.com/topic/151249-date-formatting/#findComment-794511 Share on other sites More sharing options...
PFMaBiSmAd Posted March 26, 2009 Share Posted March 26, 2009 A mysql DATE data type only requires 3 bytes. Storing the equivalent information as a string takes 10 bytes and as has already been mentioned cannot be directly sorted or compared when not in a yyyy-mm-dd (MSD to LSD) format. Link to comment https://forums.phpfreaks.com/topic/151249-date-formatting/#findComment-794524 Share on other sites More sharing options...
mrfitz Posted March 27, 2009 Share Posted March 27, 2009 Hi, Maybe all you need is: $r = mysql_query("select DATE_FORMAT(ad_date,'%m-%d-%Y') from " . $db_prefix . "adds WHERE ad_date >= '" . $todayis . "' order by ad_date DESC"); the key is the mysql function DATE_FORMAT() mrfitz Link to comment https://forums.phpfreaks.com/topic/151249-date-formatting/#findComment-794822 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.