Mysql Problem

fluiduk · 4 Dec 2007 at 22:31

Hi All

Can anyone see why I am getting the following error with this code

Warning: sprintf() [function.sprintf]: Too few arguments in /****.php on line 64

Code:

$query_rec_order_details = sprintf("SELECT *, date_format(date_time, '%Y-%c-%e') as formatted_date from tbl_orders 
		LEFT JOIN tbl_order_status_option ON tbl_order_status_option.order_status_id = tbl_orders.order_status
		LEFT JOIN tbl_shipping_methods ON tbl_shipping_methods.shipping_method_id = tbl_orders.shipping_method_id
	    WHERE formatted_date >= '$start_date' AND formatted_date =< '$end_date'");

Any help appreciated

Thanks
Aaron

SiriusB · 4 Dec 2007 at 22:41

sprintf() function replaces markers with values passed to it... for example:

sprintf("Hello my name is %s and I live in %s", "SiriusB", "Manchester");

Basically you have used sprintf with just the string, you haven't passed it any values.

Not sure why you have used sprintf, as there aren't any markers in your SQL statement.

More here: http://uk.php.net/sprintf

voodooflux · 4 Dec 2007 at 22:45

I'm not a PHP expert, but I'd say that sprintf is expecting you to pass 3 arguments to populate what it determines to be formatting directives beginning with % - namely %Y, %c and %e. In your example the intention is that these are actually formatting directives to the date_format statement within the SQL rather than to sprintf itself, so you need to change...

Code:

date_format(date_time, '%Y-%c-%e')

...to...

Code:

date_format(date_time, '%%Y-%%c-%%e')

...I think. Or even easier, just assign the whole string literal directly to your $query_rec_order_details variable rather than using sprintf...

Code:

$query_rec_order_details = "SELECT *, date_format(date_time, '%Y-%c-%e') as formatted_date from tbl_orders 
LEFT JOIN tbl_order_status_option ON tbl_order_status_option.order_status_id = tbl_orders.order_status
LEFT JOIN tbl_shipping_methods ON tbl_shipping_methods.shipping_method_id = tbl_orders.shipping_method_id
WHERE formatted_date >= '$start_date' AND formatted_date =< '$end_date'";

fluiduk · 4 Dec 2007 at 22:51

Interesting - that has solved one problem but now with the code below I am getting another error.

Unknown column 'date' in 'where clause'

Code:

$query_rec_order_details = sprintf("SELECT *, date_format(tbl_orders.date_time, '%%Y-%%c-%%e') as date from tbl_orders 
		LEFT JOIN tbl_order_status_option ON tbl_order_status_option.order_status_id = tbl_orders.order_status
		LEFT JOIN tbl_shipping_methods ON tbl_shipping_methods.shipping_method_id = tbl_orders.shipping_method_id
	    WHERE date >= '$start_date' AND date <= '$end_date'");

voodooflux · 4 Dec 2007 at 23:08

Just guessing, but as "date" is a MySQL keyword (data type) it might be worth changing it to "formatted_date" or something like that.

fluiduk · 4 Dec 2007 at 23:09

tried that

voodooflux · 5 Dec 2007 at 00:13

Reading around that seems to be a commonly reported issue. The only solution I've seen suggested is to do away with the column alias:

Code:

SELECT *, date_format(tbl_orders.date_time, '%Y-%c-%e') from tbl_orders 
LEFT JOIN tbl_order_status_option ON tbl_order_status_option.order_status_id = tbl_orders.order_status
LEFT JOIN tbl_shipping_methods ON tbl_shipping_methods.shipping_method_id = tbl_orders.shipping_method_id
    WHERE date >= date_format(tbl_orders.date_time, '%Y-%c-%e') AND date <= date_format(tbl_orders.date_time, '%Y-%c-%e')

Not ideal, but will get you the result you're after.

fluiduk · 9 Dec 2007 at 12:52

Sadly that still didnt't seem to work. It is still saying that the column in unknown

Any other suggestions?

Thanks
Aaron

voodooflux · 9 Dec 2007 at 13:09

Sorry chief - my bad. Try this...

Code:

$query_rec_order_details = "SELECT *, date_format(tbl_orders.date_time, '%Y-%c-%e') as formatted_date from tbl_orders 
LEFT JOIN tbl_order_status_option ON tbl_order_status_option.order_status_id = tbl_orders.order_status
LEFT JOIN tbl_shipping_methods ON tbl_shipping_methods.shipping_method_id = tbl_orders.shipping_method_id
WHERE tbl_orders.date_time >= '$start_date' AND tbl_orders.date_time <= '$end_date'";

If you don't need the date formatted in your result set then you can also simplify it to:

Code:

$query_rec_order_details = "SELECT * from tbl_orders 
LEFT JOIN tbl_order_status_option ON tbl_order_status_option.order_status_id = tbl_orders.order_status
LEFT JOIN tbl_shipping_methods ON tbl_shipping_methods.shipping_method_id = tbl_orders.shipping_method_id
WHERE tbl_orders.date_time >= '$start_date' AND tbl_orders.date_time <= '$end_date'";