Skip navigation links

MySQL Forums :: Newbie :: Query syntax


Advanced Search

Query syntax
Posted by: Iain MacDonald ()
Date: February 10, 2007 08:00AM

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....?

Options: ReplyQuote


Subject Written By Posted
Query syntax Iain MacDonald 02/10/2007 08:00AM
Re: Query syntax Iain MacDonald 02/11/2007 03:11AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.