Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

Query error in modified order.php list


stanj

Recommended Posts

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

Link to comment
Share on other sites

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

Need help installing add ons/contributions, cleaning a hacked site or a bespoke development, check my profile

 

Virus Threat Scanner

My Contributions

Basic install answers.

Click here for Contributions / Add Ons.

UK your site.

Site Move.

Basic design info.

 

For links mentioned in old answers that are no longer here follow this link Useful Threads.

 

If this post was useful, click the Like This button over there ======>>>>>.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...