mySQL date format help

[turtleton09]

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?

[turtleton09]

OK - had a type in there - should have been :

SELECT date_format(OrderDate, '%D %b %Y')

instead of :

SELECT date_format (OrderDate, '%D %b %Y')

The query isn't quite right as it's showing the same date for each order (the first in the recordset) rather then the correct date - where am I going wrong with it?

I presume I need to reference the OrderID somewhere, but not sure where, as it's not being posted or got from another page.

[turtleton09]

In the end it just needed the

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

added into the original query and that's working fine.

I have another page however, 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!

[bradgrafelman]

Since you don't know what [man]sprintf/man is, you have two options:

1. Read the manual page for [man]sprintf/man. Learn that it uses percent signs plus various specifiers to format a string with the given arguements. Realize that since your date_format() bit contains percent signs followed by letters, sprintf() thinks you're asking it to fill those in with variables (which you aren't), this the "too few arguments" error. Finally, read how to insert a literal '%' character into your string and make the needed corrections.

2. Don't use sprintf(). Just make the query a plain ol' string:

   $query = "SELECT whatever FROM mytable WHERE something = '$variable'";