mySQL query syntax

[johngordon12]

I'm trying to get a date field to display in the format 01 Jan 2007 rather than mySQLs default format.

My original query 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);
?>

Which works fine, but with the mySQL default format.

So I added a separate query to convert it to the reformatted version :

<?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);
?>

It works fine in that the format is correct, but it's only showing the first date in the recordset for each order, rather than the correct date for each order.

I'm not sure why, as other than the date format the query is the same.

My best guess is that I need to reference the OrderID, but I'm not sure of the correct syntax....?

[johngordon12]

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 :

[code]<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);
?>[/code]

So i changed it to :

[code]<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);
?>[/code]

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!

[Newsgroup_User]

Iain71 wrote:
> In the end it just needed the
> It uses SPRINTF which I'm not quite sure is

Why not consult the PHP online documentation?

http://www.php.net/manual/en/function.sprintf.php

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

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

sprintf() uses conversion specifications that begin with a percentage
sign. Your revamped query contains MySQL format specifiers that also
begin with percentage signs. As a result PHP thinks you have supplied
too few arguments to sprintf(). When using a literal percentage sign in
sprintf(), it needs to be escaped with another percentage sign like this:

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

I'm surprised that your other query worked. Normally the asterisk cannot
be combined with other column names. Perhaps it's because you're using
formatted_date as an alias, rather than using the name of the column
itself. The correct way to specify this sort of query is to name each
column explicitly.

--
David Powers, Adobe Community Expert
Author, "Foundation PHP for Dreamweaver 8" (friends of ED)
Author, "PHP Solutions" (friends of ED)
http://foundationphp.com/

[johngordon12 · Accepted solution]

Thanks David - double %% instead of %.