Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

Help!! 1054, 1064 error in Admin-Product Attri....


Iwant2Lrn

Recommended Posts

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!!!!!!!!!!!!!!

Link to comment
Share on other sites

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; ?>>

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Unfortunately, your content contains terms that we do not allow. Please edit your content to remove the highlighted words below.
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...