SQL syntax error

[Avium]

Hi guys,

 

I get this error on the right column whenever a customer logs into their account:

 

1064 - You have an error in your SQL syntax near 'MAX_DISPLAY_PRODUCTS_IN_ORDER_HISTORY_BOX' at line 1

 

select distinct op.products_id from orders o, orders_products op, products p where o.customers_id = '14' and o.orders_id = op.orders_id and op.products_id = p.products_id and p.products_status = '1' group by products_id order by o.date_purchased desc limit MAX_DISPLAY_PRODUCTS_IN_ORDER_HISTORY_BOX

 

 

Any comments on how to stop this problem?

[wizardsandwars]

It looks as though 'MAX_DISPLAY_PRODUCTS_IN_ORDER_HISTORY_BOX' is for some reason not assigned a value. It is supposed to be set in the admin configurtion, however, for some reason it is not for you.

 

You can easily overide this porblem by inserting a define statementt in the order_history.php file in the includes/boxes directory.

 

Just add

 

Define('MAX_DISPLAY_PRODUCTS_IN_ORDER_HISTORY_BOX', 5);

 

Right after the include applixation topo command at the top of the page.

[Avium]

Can't seem to see where to insert it - any help?

 

<?php

/*

 $Id: order_history.php,v 1.4 2003/02/10 22:31:02 hpdl Exp $



 osCommerce, Open Source E-Commerce Solutions

 http://www.oscommerce.com



 Copyright (c) 2003 osCommerce



 Released under the GNU General Public License

*/



 if (tep_session_is_registered('customer_id')) {

// retreive the last x products purchased

   $orders_query = tep_db_query("select distinct op.products_id from " . TABLE_ORDERS . " o, " . TABLE_ORDERS_PRODUCTS . " op, " . TABLE_PRODUCTS . " p where o.customers_id = '" . $customer_id . "' and o.orders_id = op.orders_id and op.products_id = p.products_id and p.products_status = '1' group by products_id order by o.date_purchased desc limit " . MAX_DISPLAY_PRODUCTS_IN_ORDER_HISTORY_BOX);

   if (tep_db_num_rows($orders_query)) {

?>

<!-- customer_orders //-->

         <tr>

           <td>

<?php

     $info_box_contents = array();

     $info_box_contents[] = array('text' => BOX_HEADING_CUSTOMER_ORDERS);



     new infoBoxHeading($info_box_contents, false, false);



     $product_ids = '';

     while ($orders = tep_db_fetch_array($orders_query)) {

       $product_ids .= $orders['products_id'] . ',';

     }

     $product_ids = substr($product_ids, 0, -1);



     $customer_orders_string = '<table border="0" width="100%" cellspacing="0" cellpadding="1">';

     $products_query = tep_db_query("select products_id, products_name from " . TABLE_PRODUCTS_DESCRIPTION . " where products_id in (" . $product_ids . ") and language_id = '" . $languages_id . "' order by products_name");

     while ($products = tep_db_fetch_array($products_query)) {

       $customer_orders_string .= '  <tr>' .

                                  '    <td class="infoBoxContents"><a href="' . tep_href_link(FILENAME_PRODUCT_INFO, 'products_id=' . $products['products_id']) . '">' . $products['products_name'] . '</a></td>' .

                                  '    <td class="infoBoxContents" align="right" valign="top"><a href="' . tep_href_link(basename($PHP_SELF), tep_get_all_get_params(array('action')) . 'action=cust_order&pid=' . $products['products_id']) . '">' . tep_image(DIR_WS_ICONS . 'cart.gif', ICON_CART) . '</a></td>' .

                                  '  </tr>';

     }

     $customer_orders_string .= '</table>';



     $info_box_contents = array();

     $info_box_contents[] = array('text' => $customer_orders_string);



     new infoBox($info_box_contents);

?>

           </td>

         </tr>

<!-- customer_orders_eof //-->

<?php

   }

 }

?>

 

Thanks in advance!

[Ajeh]

Can you peek in your configuration table and see what the setting reads for configuration_key= MAX_DISPLAY_PRODUCTS_IN_ORDER_HISTORY_BOX

[wizardsandwars]

You can just put it at the top after

 

Released under the GNU General Public License 

*/

 

This isn't the best solution, as it's not where the definitrion is supposed to go, but it is the easiest quickest fix.

 

Actually, you could just replace the variable with the number 5 in the query if you wanted.

[Avium]

Hi Chris,

 

Thanks - it works now. Although a cleaner way of doing it might have been better.

 

Thanks again! :D

[Ajeh]

That setting Chris had you make should be in your configuration table.

 

Sound like you may be missing a configuration_key

[jeffcraig]

-----------------------------------------

INSERT INTO configuration (configuration_title, configuration_key, configuration_value, configuration_description, configuration_group_id, sort_order, date_added) VALUES ('Customer Order History Box', 'MAX_DISPLAY_PRODUCTS_IN_ORDER_HISTORY_BOX', '6', 'Maximum number of products to display in the customer order history box', '3', '17', now());

----------------------------------------------------------

 

I found yesterday that these variables are in the configuration table Ajeh is refering to. They should be listed in the configuration_key field.

All these configuration records are supposed to be entered by the /catalog/install/oscommerce.sql script.

 

For some reason this script missed installing some things for me.

I had a bunch of "1064 - You have an error in your SQL syntax ...." error messages.

 

The insert query about is line 717 in the oscommerce.sql

Lines 664 to 817 in my copy -- lines starting with INSERT INTO configuration.... should have added all the necessary records.

 

You can run the query about to add 'MAX_DISPLAY_PRODUCTS_IN_ORDER_HISTORY_BOX' record.

 

If you have others search the oscommerce.sql script to see if it is in there.

You could run the individual queries or try running the entire oscommerce.sql again.

 

HTH

[Ajeh]

Thanks Jeff ... saved me digging that one up ... :D