mySQL query syntax

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/

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!