Query error in modified order.php list

[stanj]

Hello Osc'ers

I added a couple additional tables and 2 new fields in table ORDERS

 

I wanted to capture information on checkout for cruise ship selection and date of embarkation. That works fine in checkout with pull down lists populated with the added OFF_SHIPS and PORTCALL tables using two dependent list boxes. The data is displayed corrected in the order detail page but I have been pulling my hair out trying to figure out the error in the select querys in the Admin section Order.php listing page. I only added the Ship table to this join and

 

$orders_query_raw = "select o.orders_id, o.customers_name, o.ship_id, os.ShipName, o.pc_id, o.customers_id, o.payment_method, o.date_purchased, o.last_modified, o.currency, o.currency_value, s.orders_status_name, ot.text as order_total from " . TABLE_ORDERS . " o left join " . TABLE_ORDERS_TOTAL . " ot on (o.orders_id = ot.orders_id), " . TABLE_OFF_SHIPS . " os on (o.ship_id = os.ship_ID), ". TABLE_ORDERS_STATUS . " s where o.customers_id = '" . (int)$cID . "' and o.orders_status = s.orders_status_id and s.language_id = '" . (int)$languages_id . "' and ot.class = 'ot_total' order by orders_id DESC";

 

 

 

The error returned is:

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'on (o.ship_id = os.ship_ID), orders_status s where o.orders_status = s.orders_st' at line 1

 

select count(*) as total from orders o left join orders_total ot on (o.orders_id = ot.orders_id), TABLE_OFF_SHIPS os on (o.ship_id = os.ship_ID), orders_status s where o.orders_status = s.orders_status_id and s.language_id = '1' and ot.class = 'ot_total'

It is probably my needing new glasses or maybe it is just that I am in over my head;>)

Any obvious errors in my syntax?

Thanks

[♥geoffreywalton]

You have a left join in there, if your hosting package is Mysql5 follow the useful threads link below and search for

 

Whilst we are on the subject of MySQL, this link will solve most problems when upgrading to MySQL5/Left Join/1054 error/.

 

Might help

 

G

[stanj]

You have a left join in there, if your hosting package is Mysql5 follow the useful threads link below and search for

 

Whilst we are on the subject of MySQL, this link will solve most problems when upgrading to MySQL5/Left Join/1054 error/.

 

Might help

 

G

Thank you for your advice. I am still at a loss as to the cause, the syntax of the added code ". TABLE_OFF_SHIPS . " os on o.ship_id = os.ship_ID," seems to be correct based on MySql 5.0.41 web docs but removing it, the page works properly.

 

I am trying to get a text display of the ship name from the off_ships table when the orders table field ship_id = the off-ships table index. It works in the order detail page but not in the order list, both "order.php" ;>(

 

Stan

[stanj]

Thank you for your advice. I am still at a loss as to the cause, the syntax of the added code ". TABLE_OFF_SHIPS . " os on o.ship_id = os.ship_ID," seems to be correct based on MySql 5.0.41 web docs but removing it, the page works properly.

 

I am trying to get a text display of the ship name from the off_ships table when the orders table field ship_id = the off-ships table index. It works in the order detail page but not in the order list, both "order.php" ;>(

 

Stan

An Update on this problem.I scraped this method and used another way that worked well.

Stan

[MrPhil]

Um, so is (was) TABLE_OFF_SHIPS the actual name of the table, or a macro name that you neglected to define?