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> Quote Link to comment 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 Quote Link to comment 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. Quote Link to comment 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). Quote Link to comment 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')"; Quote Link to comment 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. Quote Link to comment 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 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.