Date format in MySQL or PHP

[BarneyJoe]

I'm trying to figure out how to display a date field in the format 01 January 2007, rather than mySQL default format.

 

I thought I had it figured out, but it's not working in my example.

 

I basically have query that looks like :

 

<?php
mysql_select_db($database_connOriental, $connOriental);
$query_rsOrders = "SELECT * FROM Orders INNER JOIN Customers WHERE OrderCustomerID = CustomerID ORDER BY OrderID ASC";
$rsOrders = mysql_query($query_rsOrders, $connOriental) or die(mysql_error());
$row_rsOrders = mysql_fetch_assoc($rsOrders);
$totalRows_rsOrders = mysql_num_rows($rsOrders);
?>

 

To keep things simple, I've also created a separate query for the date format, which is identical except for the date format :

 

<php
mysql_select_db($database_connOriental, $connOriental);
$query_DateFormat = "SELECT date_format (OrderDate, '%D %b %Y') as formatted_date FROM Orders INNER JOIN Customers WHERE OrderCustomerID = CustomerID ";
$DateFormat = mysql_query($query_DateFormat, $connOriental) or die(mysql_error());
$row_DateFormat = mysql_fetch_assoc($DateFormat);
$totalRows_DateFormat = mysql_num_rows($DateFormat);
?>

 

But on the page, it's erroring out as :

 

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(OrderDate, '%D %b %Y') as formatted_date FROM Orders' at line 1.

 

Not helped as I'm not sure what the relevence of line 1 is.

 

Any ideas where I'm going wrong here?

[richardw]

try removing the space after the date format  -->  date_format(OrderDate, '%D %b %Y')

 

Hope this works.

[BarneyJoe]

Thank you! That was it.

 

I don't think I've got the query quite right tho', as it's now showing the same date for each order, rather than the correct date matching the order ID - how do I tie it in to the OrderID?

[richardw]

Could you post the schema for the two tables?

 

 

[BarneyJoe]

Orders Table :

 

OrderID (int, PK)

OrderCustomerID (int)

OrderTotal

OrderDate

OrderStatus

 

Customers Table :

 

CustomerID (int, PK)

FirstName

LastName

etc

 

So the join is on OrderCustomerID and CustomerID -

 

INNER JOIN Customers WHERE OrderCustomerID = CustomerID

[richardw]

hi... I am checking back somewhat slowly today, as I am demolishing a wall as part of a renovation project.

 

One more question now that I see the table structure. Is the OrderDate field a timestamp?

 

 

[BarneyJoe]

It is, yes.

 

If I just echo OrderDate from the rsOrders query, it's fine - although badly formatted.

 

But echoing the formatted_date from the DateFormat query isn't.

[richardw]

Well, I looked at the schema and script, and decided to redo it as I would have programmed it, you may want to change it back to meet your programming technique, but if this seems to work, modify it slowly so that you can see the changes...

 

I set up a test DB w/tables at http://www.mywebforyou.com/join_test.php, and I do get different dates with my test data using two tables on a left join:

 

<?php
$dbname =  "YourDBName";
$hostname = "localhost";
$dbuser = "YourUserName";
$dbpassword = "YourPassword";
$db=mysql_connect($hostname,$dbuser,$dbpassword);
mysql_select_db($dbname) or die("Unable to connect to the database");
$sql = "SELECT *, date_format(OrderDate, '%d %M %Y') FROM orders LEFT JOIN customers  ON orders.OrderCustomerID = customers .CustomerID ORDER BY orders.OrderID";
$result = mysql_query($sql); 
?>
<?php
while ($row = mysql_fetch_assoc($result)) { 
echo $row["date_format(OrderDate, '%d %M %Y')"]," ", $row["OrderTotal"]," ", $row["FirstName"]," ", $row["LastName"]," id=", $row["CustomerID"]; 
?> 
<?php
echo "<br>";
} 
?>

 

I hope this helps you re-work your script to meet your needs.

 

best, 

[BarneyJoe]

Thank you! In the end it just needed the

 

, date_format(OrderDate, '%d %M %Y') as formatted_date

 

added into the original query, as you had it, and that's working fine.

 

I have another page, where a customer logs in to an account section, which does it slightly differently, ie they click on 'View orders' link, which displays their orders based in matching the customer ID of the customer currently logged in.

 

So I thought it would be a simple case of adding in the date_format bit above, but that's giving an error.

 

It uses SPRINTF which I'm not quite sure is, and the whole query looks like :

 

<php
$colname_rsOrders = "1";
if (isset($_SESSION['CustomerID'])) {
  $colname_rsOrders = (get_magic_quotes_gpc()) ? $_SESSION['CustomerID'] : addslashes($_SESSION['CustomerID']);
}
mysql_select_db($database_connOriental, $connOriental);
$query_rsOrders = sprintf("SELECT * FROM Orders WHERE OrderCustomerID = %s ORDER BY OrderID Asc", $colname_rsOrders);
$rsOrders = mysql_query($query_rsOrders, $connOriental) or die(mysql_error());
$row_rsOrders = mysql_fetch_assoc($rsOrders);
$totalRows_rsOrders = mysql_num_rows($rsOrders);
?>

 

So i changed it to :

 

<php
$colname_rsOrders = "1";
if (isset($_SESSION['CustomerID'])) {
  $colname_rsOrders = (get_magic_quotes_gpc()) ? $_SESSION['CustomerID'] : addslashes($_SESSION['CustomerID']);
}
mysql_select_db($database_connOriental, $connOriental);
$query_rsOrders = sprintf("SELECT *, date_format(OrderDate, '%d %M %Y') as formatted_date FROM Orders WHERE OrderCustomerID = %s ORDER BY OrderID Asc", $colname_rsOrders);
$rsOrders = mysql_query($query_rsOrders, $connOriental) or die(mysql_error());
$row_rsOrders = mysql_fetch_assoc($rsOrders);
$totalRows_rsOrders = mysql_num_rows($rsOrders);
?>

 

As per the other one, and that's giving the error :

 

Warning: sprintf() [function.sprintf]: Too few arguments in /home/qdiizyfg/public_html/orderhistory.php on line 52

Query was empty

 

It's never straightforward!

[richardw]

I too am not very familiar with "sprintf"....

 

Definition and Usage

The sprintf() function writes a formatted string to a variable.

 

The arg1, arg2, ++ parameters will be inserted at percent (%) signs in the main string. This function works "step-by-step". At the first % sign, arg1 is inserted, at the second % sign, arg2 is inserted, etc.

from http://www.w3schools.com/php/func_string_sprintf.asp

 

 

Try adding a %s before the formmatted_date:

$query_rsOrders = sprintf("SELECT *, date_format(OrderDate, '%d %M %Y') as %s formatted_date FROM Orders WHERE OrderCustomerID = %s ORDER BY OrderID Asc", $colname_rsOrders);

[BarneyJoe]

Cheers - that fixed it - just needed double %%.