Mysql problem for osc_active_desktop - any experts out there?

[Guest]

The osc_active_desktop.php file is really wonderful - but I've had to customize it a bit to suit our needs. We need 1 screen where we can process orders offline w/ our credit card machine. I've succesfully added the Credit Card Number and Expiration date to the list of columns and fields being queried - but now I'm a bit stuck.

 

First it that for "order total" the active desktop just gives you the subtotal for 1 product. It adds tax, but no shipping or other products. That is confusing for the purposes of processing orders.

 

So I'm trying to do 2 things - from data contained in the

 

orders_total table.

 

1- trying to get the total order price to display - even if it shows the same total in duplicate for a person who ordered 2 products, at least we would know what to charge them.

 

This information is contained twice - in both the 'text' and 'value' fields. Both the field class= 'ot_total' and field title='Total:' should bring up this information.

 

2- trying to get the shipping method and total to display. The shipping info is contained in the same table, under the field class='ot_shipping'

 

I'm having a very difficult time getting this data to properly display! I've tried all sorts of WHERE and AND clauses, and most seem to bring up 10 dulicates of each order.

 

My latest attempt looked like this:

 

 $command2_query = tep_db_query("select o.orders_id, date_purchased, customers_name, products_name, payment_method, cc_number, cc_expires, title, text, value, products_quantity, final_price, op.products_tax, op.orders_products_id from orders o, orders_products op, orders_total WHERE o.orders_id = op.orders_id and orders_status='4' AND orders_total.class='ot_shipping' AND orders_total.title='Total:' ORDER BY date_purchased DESC");

 

Any suggestions would be most helpful!

[kgt]

$command2_query = tep_db_query("select o.orders_id, date_purchased, customers_name, products_name, payment_method, cc_number, cc_expires, title, text, value, products_quantity, final_price, op.products_tax, op.orders_products_id from orders o, orders_products op, orders_total WHERE o.orders_id = op.orders_id and orders_status='4' AND orders_total.class='ot_shipping' AND orders_total.title='Total:' ORDER BY date_purchased DESC");

 

Okay, I see two problems:

 

1. You are not relating the orders_total table to the other two. That would be why you're getting duplicate rows, and the really bad part is, you're getting rows from orders_total that aren't even related to the real order! o.orders_id=orders_total.orders_id

 

2. You want ot_shipping and ot_total, but each order has several records in the orders_total table (one record for each ot_ line). So you really want orders_total.class='ot_shipping' OR orders_total.title='Total:'.

 

Here's my final query, which works for me:

 

select o.orders_id, date_purchased, customers_name, products_name, payment_method, cc_number, cc_expires, title, text, value, products_quantity, final_price, op.products_tax, op.orders_products_id FROM orders o, orders_products op, orders_total WHERE o.orders_id = op.orders_id and o.orders_id=orders_total.orders_id AND orders_status='4' and (orders_total.class='ot_shipping' OR orders_total.title='Total:') ORDER BY date_purchased DESC

 

Note that you'll still get two lines for every item in the order. If you just need order totals but don't need to see each order item, you can use something like:

 

select o.orders_id, date_purchased, customers_name, payment_method, cc_number, cc_expires, title, text, value FROM orders o, orders_total WHERE o.orders_id=orders_total.orders_id AND orders_status='4' and (orders_total.class='ot_shipping' or orders_total.title='Total:') ORDER BY date_purchased DESC

 

This will give you only two lines - one for the order total and one for the shipping total for each order.

[Guest]

You are awesome! Thanks so much - what a kind soul to help me out.

 

It's perfect now! I had figured out that I was missing the relationship key - so I finally got order totals to show - but the subquery thing was totally eluding me.

 

Thanks for making our business a bit easier to run today :)

[Guest]

Ok here's a new feature I think would be very handy for order processing...

 

How about some formatting both for credit card entry and exp date? dealing w/ numbers 15 or 16 digits long w/ no space in between is a bit of a nightmare.

 

I wouldn't mind if the customers enter it all in 1 box - but can we get the database to put a space in between every 4 digits as well as separating the exp date month from year - or code the query in the order manager - to display the CC results in a formatted way?