Iwant2Lrn Posted March 8, 2006 Share Posted March 8, 2006 HELP!!! I have just installed the Sort Option Values Module 1.0 contribution and all went great until I went to my product attributes in Admin. The code breaks at the Option Values section of the Product attributes page. This is the error I get: 1054 - Unknown column 'pov.products_options_values_sortorder' in 'field list' select pov.products_options_values_id, pov.products_options_values_name, pov.products_options_values_sortorder, pov2po.products_options_id from products_options_values pov left join products_options_values_to_products_options pov2po on pov.products_options_values_id = pov2po.products_options_values_id where pov.language_id = '1' order by pov2po.products_options_id, pov.products_options_values_sortorder, pov.products_options_values_id [TEP STOP] After doing some research for about 2 days I tried some of the fixes I saw for problems like this. I tried putting the parentheses after the from and before the pov left join. After I did this in 2 places I get this error: 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 'pov left join products_options_values_to_products_options pov2po on pov.products' at line 1 select pov.products_options_values_id, pov.products_options_values_name, pov.products_options_values_sortorder, pov2po.products_options_id from (products_options_values) pov left join products_options_values_to_products_options pov2po on pov.products_options_values_id = pov2po.products_options_values_id where pov.language_id = '1' order by pov2po.products_options_id, pov.products_options_values_sortorder, pov.products_options_values_id [TEP STOP] I think this is the code it is referring to: // sortoptionvalues - Modify the following line $values = "select pov.products_options_values_id, pov.products_options_values_name, pov.products_options_values_sortorder, pov2po.products_options_id from (" . TABLE_PRODUCTS_OPTIONS_VALUES . ") pov left join " . TABLE_PRODUCTS_OPTIONS_VALUES_TO_PRODUCTS_OPTIONS . " pov2po on pov.products_options_values_id = pov2po.products_options_values_id where pov.language_id = '" . (int)$languages_id . "' order by pov2po.products_options_id, pov.products_options_values_sortorder, pov.products_options_values_id"; // sortoptionvalues_eof I changed the highlighted part. The parentheses were not there in the original contribution code. The other piece of code is this: <?php $per_page = MAX_ROW_LISTS_OPTIONS; $attributes = "select pa.* from (" . TABLE_PRODUCTS_ATTRIBUTES . ") pa left join " . TABLE_PRODUCTS_DESCRIPTION . " pd on pa.products_id = pd.products_id and pd.language_id = '" . (int)$languages_id . "' order by pd.products_name"; $attribute_query = tep_db_query($attributes); Again, I modded the highlighted. I do not believe this text is what is causing the error since it is the original OSC code. Any have any suggestions! I am doing a lot of reading but I am just a beginner and this seems to me like it in next level stuff!!!!!!!!!!!!!! Quote Link to comment Share on other sites More sharing options...
Iwant2Lrn Posted March 8, 2006 Author Share Posted March 8, 2006 HELP!!! I have just installed the Sort Option Values Module 1.0 contribution and all went great until I went to my product attributes in Admin. The code breaks at the Option Values section of the Product attributes page. This is the error I get: 1054 - Unknown column 'pov.products_options_values_sortorder' in 'field list' select pov.products_options_values_id, pov.products_options_values_name, pov.products_options_values_sortorder, pov2po.products_options_id from products_options_values pov left join products_options_values_to_products_options pov2po on pov.products_options_values_id = pov2po.products_options_values_id where pov.language_id = '1' order by pov2po.products_options_id, pov.products_options_values_sortorder, pov.products_options_values_id [TEP STOP] After doing some research for about 2 days I tried some of the fixes I saw for problems like this. I tried putting the parentheses after the from and before the pov left join. After I did this in 2 places I get this error: 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 'pov left join products_options_values_to_products_options pov2po on pov.products' at line 1 select pov.products_options_values_id, pov.products_options_values_name, pov.products_options_values_sortorder, pov2po.products_options_id from (products_options_values) pov left join products_options_values_to_products_options pov2po on pov.products_options_values_id = pov2po.products_options_values_id where pov.language_id = '1' order by pov2po.products_options_id, pov.products_options_values_sortorder, pov.products_options_values_id [TEP STOP] I think this is the code it is referring to: // sortoptionvalues - Modify the following line $values = "select pov.products_options_values_id, pov.products_options_values_name, pov.products_options_values_sortorder, pov2po.products_options_id from (" . TABLE_PRODUCTS_OPTIONS_VALUES . ") pov left join " . TABLE_PRODUCTS_OPTIONS_VALUES_TO_PRODUCTS_OPTIONS . " pov2po on pov.products_options_values_id = pov2po.products_options_values_id where pov.language_id = '" . (int)$languages_id . "' order by pov2po.products_options_id, pov.products_options_values_sortorder, pov.products_options_values_id"; // sortoptionvalues_eof I changed the highlighted part. The parentheses were not there in the original contribution code. The other piece of code is this: <?php $per_page = MAX_ROW_LISTS_OPTIONS; $attributes = "select pa.* from (" . TABLE_PRODUCTS_ATTRIBUTES . ") pa left join " . TABLE_PRODUCTS_DESCRIPTION . " pd on pa.products_id = pd.products_id and pd.language_id = '" . (int)$languages_id . "' order by pd.products_name"; $attribute_query = tep_db_query($attributes); Again, I modded the highlighted. I do not believe this text is what is causing the error since it is the original OSC code. Any have any suggestions! I am doing a lot of reading but I am just a beginner and this seems to me like it in next level stuff!!!!!!!!!!!!!! Also, I want to add that products_options_values_sortorder is not getting added to the database like the other talbe entries. Here is the code I have for where it gets added to the db, i think! The highlighted is where I think it is getting added! <?php /* $Id: products_attributes.php,v 1.52 2003/07/10 20:46:01 dgw_ Exp $ adapted for quantity_mod_1_2.zip adapted for sortoptionvalues_1_0.zip osCommerce, Open Source E-Commerce Solutions http://www.oscommerce.com Copyright © 2003 osCommerce Released under the GNU General Public License */ require('includes/application_top.php'); $languages = tep_get_languages(); $action = (isset($HTTP_GET_VARS['action']) ? $HTTP_GET_VARS['action'] : ''); if (tep_not_null($action)) { $page_info = ''; if (isset($HTTP_GET_VARS['option_page'])) $page_info .= 'option_page=' . $HTTP_GET_VARS['option_page'] . '&'; if (isset($HTTP_GET_VARS['value_page'])) $page_info .= 'value_page=' . $HTTP_GET_VARS['value_page'] . '&'; if (isset($HTTP_GET_VARS['attribute_page'])) $page_info .= 'attribute_page=' . $HTTP_GET_VARS['attribute_page'] . '&'; if (tep_not_null($page_info)) { $page_info = substr($page_info, 0, -1); } // QuantityMod - BEGIN editing function update_stock($product_id) { $get_quantity_query = tep_db_query("select sum(options_quantity) as total_quantity from " . TABLE_PRODUCTS_ATTRIBUTES . " where products_id='" . (int)$product_id . "'"); $get_quantity = tep_db_fetch_array($get_quantity_query); $total_quantity = $get_quantity['total_quantity']; tep_db_query("update " . TABLE_PRODUCTS . " set products_quantity = '" . $total_quantity . "' where products_id = '" . (int)$product_id . "'"); } // QuantityMod - FINISH editing switch ($action) { case 'add_product_options': $products_options_id = tep_db_prepare_input($HTTP_POST_VARS['products_options_id']); $option_name_array = $HTTP_POST_VARS['option_name']; for ($i=0, $n=sizeof($languages); $i<$n; $i ++) { $option_name = tep_db_prepare_input($option_name_array[$languages[$i]['id']]); tep_db_query("insert into " . TABLE_PRODUCTS_OPTIONS . " (products_options_id, products_options_name, language_id) values ('" . (int)$products_options_id . "', '" . tep_db_input($option_name) . "', '" . (int)$languages[$i]['id'] . "')"); } tep_redirect(tep_href_link(FILENAME_PRODUCTS_ATTRIBUTES, $page_info)); break; case 'add_product_option_values': $value_name_array = $HTTP_POST_VARS['value_name']; $value_id = tep_db_prepare_input($HTTP_POST_VARS['value_id']); $option_id = tep_db_prepare_input($HTTP_POST_VARS['option_id']); // sortoptionvalues - Insert the following line $values_sortorder = tep_db_prepare_input($HTTP_POST_VARS['values_sortorder']); // sortoptionvalues_eof for ($i=0, $n=sizeof($languages); $i<$n; $i ++) { $value_name = tep_db_prepare_input($value_name_array[$languages[$i]['id']]); // sortoptionvalues - Modify the following line tep_db_query("insert into " . TABLE_PRODUCTS_OPTIONS_VALUES . " (products_options_values_id, language_id, products_options_values_name) values ('" . (int)$value_id . "', '" . (int)$languages[$i]['id'] . "', '" . tep_db_input($value_name) . "')"); // sortoptionvalues_eof } tep_db_query("insert into " . TABLE_PRODUCTS_OPTIONS_VALUES_TO_PRODUCTS_OPTIONS . " (products_options_id, products_options_values_id) values ('" . (int)$option_id . "', '" . (int)$value_id . "')"); tep_redirect(tep_href_link(FILENAME_PRODUCTS_ATTRIBUTES, $page_info)); break; case 'add_product_attributes': $products_id = tep_db_prepare_input($HTTP_POST_VARS['products_id']); $options_id = tep_db_prepare_input($HTTP_POST_VARS['options_id']); $values_id = tep_db_prepare_input($HTTP_POST_VARS['values_id']); $value_price = tep_db_prepare_input($HTTP_POST_VARS['value_price']); // QuantityMod - BEGIN editing $value_quantity = tep_db_prepare_input($HTTP_POST_VARS['value_quantity']); // QuantityMod - FINISH editing $price_prefix = tep_db_prepare_input($HTTP_POST_VARS['price_prefix']); // QuantityMod - BEGIN editing tep_db_query("insert into " . TABLE_PRODUCTS_ATTRIBUTES . " values ('', '" . (int)$products_id . "', '" . (int)$options_id . "', '" . (int)$values_id . "', '" . tep_db_input($value_price) . "', '" . tep_db_input($price_prefix) . "', '" . (int)$value_quantity . "')"); update_stock($products_id); // QuantityMod - FINISH editing if (DOWNLOAD_ENABLED == 'true') { $products_attributes_id = tep_db_insert_id(); $products_attributes_filename = tep_db_prepare_input($HTTP_POST_VARS['products_attributes_filename']); $products_attributes_maxdays = tep_db_prepare_input($HTTP_POST_VARS['products_attributes_maxdays']); $products_attributes_maxcount = tep_db_prepare_input($HTTP_POST_VARS['products_attributes_maxcount']); if (tep_not_null($products_attributes_filename)) { tep_db_query("insert into " . TABLE_PRODUCTS_ATTRIBUTES_DOWNLOAD . " values (" . (int)$products_attributes_id . ", '" . tep_db_input($products_attributes_filename) . "', '" . tep_db_input($products_attributes_maxdays) . "', '" . tep_db_input($products_attributes_maxcount) . "')"); } } tep_redirect(tep_href_link(FILENAME_PRODUCTS_ATTRIBUTES, $page_info)); break; case 'update_option_name': $option_name_array = $HTTP_POST_VARS['option_name']; $option_id = tep_db_prepare_input($HTTP_POST_VARS['option_id']); for ($i=0, $n=sizeof($languages); $i<$n; $i ++) { $option_name = tep_db_prepare_input($option_name_array[$languages[$i]['id']]); tep_db_query("update " . TABLE_PRODUCTS_OPTIONS . " set products_options_name = '" . tep_db_input($option_name) . "' where products_options_id = '" . (int)$option_id . "' and language_id = '" . (int)$languages[$i]['id'] . "'"); } tep_redirect(tep_href_link(FILENAME_PRODUCTS_ATTRIBUTES, $page_info)); break; case 'update_value': $value_name_array = $HTTP_POST_VARS['value_name']; $value_id = tep_db_prepare_input($HTTP_POST_VARS['value_id']); $option_id = tep_db_prepare_input($HTTP_POST_VARS['option_id']); // sortoptionvalues - Insert the following line $values_sortorder = tep_db_prepare_input($HTTP_POST_VARS['values_sortorder']); // sortoptionvalues_eof for ($i=0, $n=sizeof($languages); $i<$n; $i ++) { $value_name = tep_db_prepare_input($value_name_array[$languages[$i]['id']]); // sortoptionvalues - Modify the following line tep_db_query("update " . TABLE_PRODUCTS_OPTIONS_VALUES . " set products_options_values_name = '" . tep_db_input($value_name) . "', products_options_values_sortorder = '" . tep_db_input($values_sortorder) . "' where products_options_values_id = '" . tep_db_input($value_id) . "' and language_id = '" . (int)$languages[$i]['id'] . "'"); // sortoptionvalues_eof } tep_db_query("update " . TABLE_PRODUCTS_OPTIONS_VALUES_TO_PRODUCTS_OPTIONS . " set products_options_id = '" . (int)$option_id . "' where products_options_values_id = '" . (int)$value_id . "'"); tep_redirect(tep_href_link(FILENAME_PRODUCTS_ATTRIBUTES, $page_info)); break; case 'update_product_attribute': $products_id = tep_db_prepare_input($HTTP_POST_VARS['products_id']); $options_id = tep_db_prepare_input($HTTP_POST_VARS['options_id']); $values_id = tep_db_prepare_input($HTTP_POST_VARS['values_id']); $value_price = tep_db_prepare_input($HTTP_POST_VARS['value_price']); // QuantityMod - BEGIN editing $value_quantity = tep_db_prepare_input($HTTP_POST_VARS['value_quantity']); // QuantityMod - FINISH editing $price_prefix = tep_db_prepare_input($HTTP_POST_VARS['price_prefix']); $attribute_id = tep_db_prepare_input($HTTP_POST_VARS['attribute_id']); // QuantityMod - BEGIN editing tep_db_query("update " . TABLE_PRODUCTS_ATTRIBUTES . " set products_id = '" . (int)$products_id . "', options_id = '" . (int)$options_id . "', options_values_id = '" . (int)$values_id . "', options_values_price = '" . tep_db_input($value_price) . "', price_prefix = '" . tep_db_input($price_prefix) . "', options_quantity = '" . (int)$value_quantity . "' where products_attributes_id = '" . (int)$attribute_id . "'"); update_stock($products_id); // QuantityMod - FINISH editing if (DOWNLOAD_ENABLED == 'true') { $products_attributes_filename = tep_db_prepare_input($HTTP_POST_VARS['products_attributes_filename']); $products_attributes_maxdays = tep_db_prepare_input($HTTP_POST_VARS['products_attributes_maxdays']); $products_attributes_maxcount = tep_db_prepare_input($HTTP_POST_VARS['products_attributes_maxcount']); if (tep_not_null($products_attributes_filename)) { tep_db_query("replace into " . TABLE_PRODUCTS_ATTRIBUTES_DOWNLOAD . " set products_attributes_id = '" . (int)$attribute_id . "', products_attributes_filename = '" . tep_db_input($products_attributes_filename) . "', products_attributes_maxdays = '" . tep_db_input($products_attributes_maxdays) . "', products_attributes_maxcount = '" . tep_db_input($products_attributes_maxcount) . "'"); } } tep_redirect(tep_href_link(FILENAME_PRODUCTS_ATTRIBUTES, $page_info)); break; case 'delete_option': $option_id = tep_db_prepare_input($HTTP_GET_VARS['option_id']); tep_db_query("delete from " . TABLE_PRODUCTS_OPTIONS . " where products_options_id = '" . (int)$option_id . "'"); tep_redirect(tep_href_link(FILENAME_PRODUCTS_ATTRIBUTES, $page_info)); break; case 'delete_value': $value_id = tep_db_prepare_input($HTTP_GET_VARS['value_id']); tep_db_query("delete from " . TABLE_PRODUCTS_OPTIONS_VALUES . " where products_options_values_id = '" . (int)$value_id . "'"); tep_db_query("delete from " . TABLE_PRODUCTS_OPTIONS_VALUES . " where products_options_values_id = '" . (int)$value_id . "'"); tep_db_query("delete from " . TABLE_PRODUCTS_OPTIONS_VALUES_TO_PRODUCTS_OPTIONS . " where products_options_values_id = '" . (int)$value_id . "'"); tep_redirect(tep_href_link(FILENAME_PRODUCTS_ATTRIBUTES, $page_info)); break; case 'delete_attribute': $attribute_id = tep_db_prepare_input($HTTP_GET_VARS['attribute_id']); // QuantityMod - BEGIN editing $get_products_id_query = tep_db_query("select products_id from " . TABLE_PRODUCTS_ATTRIBUTES . " where products_attributes_id='" . (int)$attribute_id . "'"); $get_products_id = tep_db_fetch_array($get_products_id_query); // QuantityMod - FINISH editing tep_db_query("delete from " . TABLE_PRODUCTS_ATTRIBUTES . " where products_attributes_id = '" . (int)$attribute_id . "'"); // QuantityMod - BEGIN editing update_stock($get_products_id['products_id']); // QuantityMod - FINISH editing // added for DOWNLOAD_ENABLED. Always try to remove attributes, even if downloads are no longer enabled tep_db_query("delete from " . TABLE_PRODUCTS_ATTRIBUTES_DOWNLOAD . " where products_attributes_id = '" . (int)$attribute_id . "'"); tep_redirect(tep_href_link(FILENAME_PRODUCTS_ATTRIBUTES, $page_info)); break; } } ?> <!doctype html public "-//W3C//DTD HTML 4.01 Transitional//EN"> <html <?php echo HTML_PARAMS; ?>> Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.