I have a php form that users enter dates. I am using the format dd/mm/yy. This is saved to a mysql database, however, when the table of the orders are displayed, it is not in the right order. How do I handle this problem with the dates?
Thanks everyone.
You didn’t provide enough information… In your database, what column type are you using?
It’s set at at varchar(8) at the moment.
Then change it to a datetime
I’ve changed it to date type to datetime. The date input now is YYYY-MM-DD HH-MM-SS. The field in the form has the user enter the date as DD/MM/YY. Do I need to convert the input field to the mysql format? If so, how do I do that?
Can someone help me here?
I have a php form that users complete orders. They enter the date of order and it is saved to mysql database. I have the date type set as “date” in mysql, however, I would prefer the user enters the date as dd/mm/yy as this is more natural to my users to enter it that way instead of yyyy/mm/dd.
Users can then look at their orders and again I would like them to see their orders in chronological order in the way they entered the dates ie. dd/mm/yy
I have read about strtotime() but not sure how or where I should place this function. Or whether this will achieve the outcome that I am trying to achieve.
This is the code that generates/outputs my table of orders.
this is the query that retrieves the data
mysql_select_db($database_TrophyMaster, $TrophyMaster);
$query_Orders = "SELECT DISTINCT tbl_OrderDetails.OrderNumber, Customer, OrderDate, RequiredDate, Freight FROM tbl_OrderDetails WHERE ClientID = ’ “. $row_Clients[‘ClientID’]. " ’ ORDER BY OrderDate DESC”;
$Orders = mysql_query($query_Orders, $TrophyMaster) or die(mysql_error());
$row_Orders = mysql_fetch_assoc($Orders);
$totalRows_Orders = mysql_num_rows($Orders);
If anyone can help much appreciated.
You can do something like below.
[php] SELECT DISTINCT tbl_OrderDetails.OrderNumber, Customer, RequiredDate, Freight, date_format(OrderDate , ‘%m/%d/%Y %l:%i:%s %p’) OrderDate FROM tbl_OrderDetails order by OrderDate DESC[/php]
Thanks for the suggestion, however, it did not do the trick.
Result was no dates displayed.
Thank you phphelp,
Had another look at it and it HAS fixed the problem. Date is looking how I want it.
Thank you. Legendary.
Hi phphelp.
My other hurdle…
I would like to have the user input the field like dd/mm/yy.
How can I convert the format from dd/mm/yy into the mysql format?
This code inserts into mysql database
$insertSQL = sprintf(“INSERT INTO tbl_Orders (OrderNumber, OrderDate, RequiredDate, fk_TrophyCode, TrophyQty, ClientID, Supplier, Customer, Freight) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)”,
GetSQLValueString($_POST[‘OrderNumber’], “text”),
GetSQLValueString($_POST[‘OrderDate’], “date”),
GetSQLValueString($_POST[‘RequiredDate’], “date”),
GetSQLValueString($_POST[‘TrophyCode’], “text”),
GetSQLValueString($_POST[‘TrophyQty’], “int”),
GetSQLValueString($_POST[‘ClientID’], “int”),
GetSQLValueString($_POST[‘Supplier’], “text”),
GetSQLValueString($_POST[‘Customer’], “text”),
GetSQLValueString($_POST[‘Freight’], “text”));
Their’s a few dozen ways you can do it. Following your logic this should work.
[php]GetSQLValueString(date(’%m/%d/%Y %l:%i:%s %p’, strtotime($_POST[‘OrderDate’])), “date”)[/php]