OSC-Sevilla Posted September 11, 2011 Share Posted September 11, 2011 Lets say you are sellng a BATHROOM TAP. OK so your basic product has code: 007 yourhave 6 attributes - all different finishes - Gold pated : 007G - crome : 007C -bronze 007B etc. That same Basic product has code 007 your customer selects Crome we see: (BITS IN RED IS WHAT IM AFTER) Products (Edit) 2 x Products Name PN: 007/007C/11 - Finish: Crome 007C - Size: 11 11 Thinking...: MAybe add in Products Attributes (the bottom table) an extra column. Similar to value price "Option ID" where you can enter the product PN: 007C. Add an extra table in SQL Then pull this info into the Order information.... Any ideas or conrtibs that can help? - really after something very simple and clean. Thanks in advance! Link to comment Share on other sites More sharing options...
hughesca Posted September 11, 2011 Share Posted September 11, 2011 Depends on where you need it. If you need the current OSC system to do it for you...good luck, I haven't found anything that will do this. I had the same issue where I needed to append the size attribute into the part number. However, I only need it so that it matches up with the correct product in Quickbooks. If your situation is similar and you just need the part numbers for accounting purposes and will be exporting the data, just do the append in the SQL query itself on your data export. On the OSC store invoices, the customer will still only see 007 then under it Chrome, however your exported file will see 007C. Here is an example where I append the size onto my model numbers. SELECT op.orders_id, CONCAT(op.products_model, (SELECT CASE opa.products_options_values WHEN '3X-Small' THEN '-3XS' WHEN '2X-Small' THEN '-2XS' WHEN 'X-Small' THEN '-XS' WHEN 'Small' THEN '-S' WHEN 'Medium' THEN '-M' WHEN 'Large' THEN '-L' WHEN 'X-Large' THEN '-XL' WHEN '2X-Large' THEN '-2XL' WHEN '3X-Large' THEN '-3XL' WHEN '4X-Large' THEN '-4XL' WHEN '5X-Large' THEN '-5XL' WHEN 'Small/Medium' THEN '-S/M' WHEN 'Large/XLarge' THEN '-L/XL' ELSE opa.products_options_values END)) AS products_model FROM orders_products_attributes AS opa, orders_products AS op WHERE opa.orders_id = op.orders_id AND opa.products_options = 'Size'; Now, if you are going to have a bunch of options (I have less than 15) you would want to dump the data into a temporary table and join from there. It would get really nasty and confusing if you had 100's of options. Hope that helps! Peace, Chris Link to comment Share on other sites More sharing options...
Guest Posted September 11, 2011 Share Posted September 11, 2011 Have you looked at Attribute Product Codes ? Chris Link to comment Share on other sites More sharing options...
hughesca Posted September 11, 2011 Share Posted September 11, 2011 ....figures! :P I have some vague memory of not being able to get that to work with my OSC v2.3.1...but I'm really not certain. I know I searched for quite some time before going with the SQL route, LOL! I might play around with it tonight and see what I can do. Thanks for pointing this out! Peace, Chris Link to comment Share on other sites More sharing options...
OSC-Sevilla Posted September 11, 2011 Author Share Posted September 11, 2011 i have also run into some issues with the 2.2 to 2.3.1 rewrite.. Anything you can cook up would be great! Thanks Link to comment Share on other sites More sharing options...
Guest Posted September 11, 2011 Share Posted September 11, 2011 Gemma, If / when you come across an issue, post it and we will help you correct it. Chris Link to comment Share on other sites More sharing options...
OSC-Sevilla Posted September 12, 2011 Author Share Posted September 12, 2011 RE: PRODUCTS Attribute Product Codes ? ALTER TABLE `products_attributes` ADD `code_suffix` varchar(11) NOT NULL default '' AFTER `options_values_id`; ALTER TABLE `orders_products_attributes` ADD `code_suffix` varchar(11) NOT NULL default '' AFTER `products_options_values`; ALTER TABLE `orders_products` ADD `products_code` varchar(20) NOT NULL default '' AFTER `products_model`; ALTER TABLE `products_attributes` ADD `suffix_sort_order` int(11) NOT NULL default '0' AFTER `code_suffix`; First this is when i add the 2 extra columns to product attributes in my SQL (RED): I GET: 1136 - Column count doesn't match value count at row 1 insert into products_attributes values (null, '323', '1', '23', '0', '+') [TEP STOP] -------------------------------------- Even when i add any extra column is get the same error. Looking at the rest of the ADDON it should go smoothly - just this initial hurdle!.... Thoughts??? Link to comment Share on other sites More sharing options...
hughesca Posted September 12, 2011 Share Posted September 12, 2011 Hmmm...maybe the database is corrupt? I was able to run the query perfectly fine using phpmyadmin. I think it is pretty straight forward. I tried to install it on my store today and it seemed to function just fine. It still won't work for my purposes as I sometimes need to append a color attribute into the middle of the part number. :P You should be able to skip these parts in it though: Find: around line 640 <td><form name="attributes" action="<?php echo tep_href_link(FILENAME_PRODUCTS_ATTRIBUTES, 'action=' . $form_action . '&option_page=' . $option_page . '&value_page=' . $value_page . '&attribute_page=' . $attribute_page); ?>" method="post"><table border="0" width="100%" cellspacing="0" cellpadding="2"> <tr> <td colspan="7" class="smallText"> Change to: <td><form name="attributes" action="<?php echo tep_href_link(FILENAME_PRODUCTS_ATTRIBUTES, 'action=' . $form_action . '&option_page=' . $option_page . '&value_page=' . $value_page . '&attribute_page=' . $attribute_page); ?>" method="post"><table border="0" width="100%" cellspacing="0" cellpadding="2"> <tr> <td colspan="9" class="smallText"> As the only change is the colspan and it should be 9 already. Peace, Chris Link to comment Share on other sites More sharing options...
OSC-Sevilla Posted September 13, 2011 Author Share Posted September 13, 2011 Thanks Chris I will give it another go - very strange, when i add any xtra table to the arrtibutes sql it throws me an error.... will do some more digging. Link to comment Share on other sites More sharing options...
OSC-Sevilla Posted September 13, 2011 Author Share Posted September 13, 2011 With a fresh install of OSC 2.3.1. I insert this SQL (from the CONTRIB): ALTER TABLE `products_attributes` ADD `code_suffix` varchar(11) NOT NULL default '' AFTER `options_values_id`; ALTER TABLE `orders_products_attributes` ADD `code_suffix` varchar(11) NOT NULL default '' AFTER `products_options_values`; ALTER TABLE `orders_products` ADD `products_code` varchar(20) NOT NULL default '' AFTER `products_model`; ALTER TABLE `products_attributes` ADD `suffix_sort_order` int(11) NOT NULL default '0' AFTER `code_suffix`; and...... 1136 - Column count doesn't match value count at row 1 insert into products_attributes values (null, '8', '1', '2', '0', '+') [TEP STOP] ---------------------------- Link to comment Share on other sites More sharing options...
hughesca Posted September 13, 2011 Share Posted September 13, 2011 Very strange...same code I just tried again with no problems: ALTER TABLE `products_attributes` ADD `code_suffix` varchar(11) NOT NULL default '' AFTER `options_values_id`; ALTER TABLE `orders_products_attributes` ADD `code_suffix` varchar(11) NOT NULL default '' AFTER `products_options_values`; ALTER TABLE `orders_products` ADD `products_code` varchar(20) NOT NULL default '' AFTER `products_model`; ALTER TABLE `products_attributes` ADD `suffix_sort_order` int(11) NOT NULL default '0' AFTER `code_suffix`; Do you have other databases that load up in your phpmyadmin? Maybe you can try adding SELECT nameofyourdb just before the alter table commands. Also, you'll have to ensure the first query runs before the last as the last ALTER TABLE positions itself after the column added in the first line. Peace, Chris Link to comment Share on other sites More sharing options...
OSC-Sevilla Posted September 13, 2011 Author Share Posted September 13, 2011 Thanks Chris! for peace of mind here is the dump from products_attributes table in mysql: CREATE TABLE `products_attributes` ( `products_attributes_id` int(11) NOT NULL auto_increment, `products_id` int(11) NOT NULL, `options_id` int(11) NOT NULL, `options_values_id` int(11) NOT NULL, `options_values_price` decimal(15,4) NOT NULL, `price_prefix` char(1) NOT NULL, PRIMARY KEY (`products_attributes_id`), KEY `idx_products_attributes_products_id` (`products_id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=264 ; Please could add the extra two lines, test it and send it back... Just to make sure your its all works 100% before i dig even deeper into this issue.. So annoying! Cheers Chris Link to comment Share on other sites More sharing options...
hughesca Posted September 13, 2011 Share Posted September 13, 2011 That's not really something I can do for you right now. :P What's the error you are getting in phpmyadmin when you try and run the 4 line query? The TEP STOP is from something else...I'm assuming you adding values and it failing via admin? Peace, Chris Link to comment Share on other sites More sharing options...
OSC-Sevilla Posted September 14, 2011 Author Share Posted September 14, 2011 Chris - Problem Solved!!!!!!!!!!!!!!!! Dont mind having problems just so long as i can fix them! Link to comment Share on other sites More sharing options...
OSC-Sevilla Posted September 14, 2011 Author Share Posted September 14, 2011 Great up and running, if i want to show the attributes part number in the drop down list: eg:TEXT IN DROP DOWN MENU finish: Gold (+50) [G786] How do i pull ths info in??? This is my attributes code: $products_options_name_query = tep_db_query("select distinct popt.products_options_id, popt.products_options_name from " . TABLE_PRODUCTS_OPTIONS . " popt, " . TABLE_PRODUCTS_ATTRIBUTES . " patrib where patrib.products_id='" . (int)$HTTP_GET_VARS['products_id'] . "' and patrib.options_id = popt.products_options_id and popt.language_id = '" . (int)$languages_id . "' order by popt.products_options_name"); while ($products_options_name = tep_db_fetch_array($products_options_name_query)) { $products_options_array = array(); $products_options_query = tep_db_query("select pov.products_options_values_id, pov.products_options_values_name, pa.options_values_price, pa.price_prefix from " . TABLE_PRODUCTS_ATTRIBUTES . " pa, " . TABLE_PRODUCTS_OPTIONS_VALUES . " pov where pa.products_id = '" . (int)$HTTP_GET_VARS['products_id'] . "' and pa.options_id = '" . (int)$products_options_name['products_options_id'] . "' and pa.options_values_id = pov.products_options_values_id and pov.language_id = '" . (int)$languages_id . "'"); while ($products_options = tep_db_fetch_array($products_options_query)) { $products_options_array[] = array('id' => $products_options['products_options_values_id'], 'text' => $products_options['products_options_values_name']); if ($products_options['options_values_price'] != '0') { $products_options_array[sizeof($products_options_array)-1]['text'] .= ' (' . $products_options['price_prefix'] . $currencies->display_price($products_options['options_values_price'], tep_get_tax_rate($product_info['products_tax_class_id'])) .') '; } } if (is_string($HTTP_GET_VARS['products_id']) && isset($cart->contents[$HTTP_GET_VARS['products_id']]['attributes'][$products_options_name['products_options_id']])) { $selected_attribute = $cart->contents[$HTTP_GET_VARS['products_id']]['attributes'][$products_options_name['products_options_id']]; } else { $selected_attribute = false; } ?> <li class="fl_left"><label><?php echo $products_options_name['products_options_name'] . ':'; ?></label><?php echo tep_draw_pull_down_menu('id[' . $products_options_name['products_options_id'] . ']', $products_options_array, $selected_attribute); ?></li> <?php } ?> </ul> </div> <?php } ?> Link to comment Share on other sites More sharing options...
OSC-Sevilla Posted September 15, 2011 Author Share Posted September 15, 2011 When i have 2 options for a product: EG - a lamp. OPTIONS: 1. Height: 90cm / 180cm / 260cm - CODES: LAMP90 / LAMP180 / LAMP260 2. Color: Green / Blue / Red - CODES: G / B / R $order->products[$i]['code'] ONLY PULLS IN THE FINAL CODE. therefore if we select: LAMP180, then Green I see just G as the product code Has anyone noted this or has a solution to solve it???? Many Thanks Link to comment Share on other sites More sharing options...
OSC-Sevilla Posted September 15, 2011 Author Share Posted September 15, 2011 When i have 2 options for a product: EG - a lamp. OPTIONS: 1. Height: 90cm / 180cm / 260cm - CODES: LAMP90 / LAMP180 / LAMP260 2. Color: Green / Blue / Red - CODES: G / B / R $order->products[$i]['code'] ONLY PULLS IN THE FINAL CODE. therefore if we select: LAMP180, then Green I see just G as the product code Has anyone noted this or has a solution to solve it???? Many Thanks ------------------------ SOLVED - sily me did not add a sort order "each attribute product code can be given a sort value, so that your code is in the right order" HOOORAR! Link to comment Share on other sites More sharing options...
OSC-Sevilla Posted September 15, 2011 Author Share Posted September 15, 2011 JUST need to pull in the $product_info['products_code'] into product_info so that as the customer selects his/her options the part number generates itself in clear view of the customer.. -----HELP WANTED----- Re: above post: SOMEWHERE WITHIN THIS LOT I NEED TO CALL : $orders_products['products_code'] $products_options_name_query = tep_db_query("select distinct popt.products_options_id, popt.products_options_name from " . TABLE_PRODUCTS_OPTIONS . " popt, " . TABLE_PRODUCTS_ATTRIBUTES . " patrib where patrib.products_id='" . (int)$HTTP_GET_VARS['products_id'] . "' and patrib.options_id = popt.products_options_id and popt.language_id = '" . (int)$languages_id . "' order by popt.products_options_name"); while ($products_options_name = tep_db_fetch_array($products_options_name_query)) { $products_options_array = array(); $products_options_query = tep_db_query("select pov.products_options_values_id, pov.products_options_values_name, pa.options_values_price, pa.price_prefix from " . TABLE_PRODUCTS_ATTRIBUTES . " pa, " . TABLE_PRODUCTS_OPTIONS_VALUES . " pov where pa.products_id = '" . (int)$HTTP_GET_VARS['products_id'] . "' and pa.options_id = '" . (int)$products_options_name['products_options_id'] . "' and pa.options_values_id = pov.products_options_values_id and pov.language_id = '" . (int)$languages_id . "'"); while ($products_options = tep_db_fetch_array($products_options_query)) { $products_options_array[] = array('id' => $products_options['products_options_values_id'], 'text' => $products_options['products_options_values_name']); if ($products_options['options_values_price'] != '0') { $products_options_array[sizeof($products_options_array)-1]['text'] .= ' (' . $products_options['price_prefix'] . $currencies->display_price($products_options['options_values_price'], tep_get_tax_rate($product_info['products_tax_class_id'])) .') '; } } if (is_string($HTTP_GET_VARS['products_id']) && isset($cart->contents[$HTTP_GET_VARS['products_id']]['attributes'][$products_options_name['products_options_id']])) { $selected_attribute = $cart->contents[$HTTP_GET_VARS['products_id']]['attributes'][$products_options_name['products_options_id']]; } else { $selected_attribute = false; } ?> <li class="fl_left"><label><?php echo $products_options_name['products_options_name'] . ':'; ?></label><?php echo tep_draw_pull_down_menu('id[' . $products_options_name['products_options_id'] . ']', $products_options_array, $selected_attribute); ?></li> <?php } ?> </ul> </div> <?php } Link to comment Share on other sites More sharing options...
captgary Posted October 11, 2011 Share Posted October 11, 2011 Gemma.....I'm having the same problem and tried to decipher what you did to fix the following error. 1136 - Column count doesn't match value count at row 1 insert into products_attributes values (null, '8', '1', '2', '0', '+') [TEP STOP] Would you please share what you did? Thanks in advance for your help. Capt Gary Link to comment Share on other sites More sharing options...
OSC-Sevilla Posted October 11, 2011 Author Share Posted October 11, 2011 very easy.. look for this: 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']); $code_suffix = tep_db_prepare_input($HTTP_POST_VARS['code_suffix']); $suffix_sort_order = tep_db_prepare_input($HTTP_POST_VARS['suffix_sort_order']); $value_price = tep_db_prepare_input($HTTP_POST_VARS['value_price']); $price_prefix = tep_db_prepare_input($HTTP_POST_VARS['price_prefix']); tep_db_query("insert into " . TABLE_PRODUCTS_ATTRIBUTES . " values ('', '" . (int)$products_id . "', '" . (int)$options_id . "', '" . (int)$values_id . "', '" . tep_db_input($code_suffix) . "', '" . tep_db_input($suffix_sort_order) . "', '" . tep_db_input($value_price) . "', '" . tep_db_input($price_prefix) . "', '" . tep_db_input($products_options_sort_order) . "')"); -------------------- I added at the end: . "', '" . tep_db_input($products_options_sort_order) . "')"); Link to comment Share on other sites More sharing options...
OSC-Sevilla Posted October 11, 2011 Author Share Posted October 11, 2011 just to add, this on the ADMIN/products_attributes.php Link to comment Share on other sites More sharing options...
captgary Posted October 11, 2011 Share Posted October 11, 2011 Never mind....I figured it out. 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']); $price_prefix = tep_db_prepare_input($HTTP_POST_VARS['price_prefix']); $sort_order = "0"; tep_db_query("insert into " . TABLE_PRODUCTS_ATTRIBUTES . " values (null, '" . (int)$products_id . "', '" . (int)$options_id . "', '" . (int)$values_id . "', '" . (float)tep_db_input($value_price) . "', '" . tep_db_input($price_prefix) . "', '". $sort_order ."')"); Added the code highlighted in red to the admin/products_attributes.php file Thanks, Capt Gary Link to comment Share on other sites More sharing options...
captgary Posted October 11, 2011 Share Posted October 11, 2011 Thanks Gemma......I'll use your fix. Appreciate your help. Link to comment Share on other sites More sharing options...
captgary Posted October 11, 2011 Share Posted October 11, 2011 Humm.....I tried you're fix and got another error. I must be using some older code as the 'add_product_attributes' case does not look the same. In any case the simple code I entered did the trick. Thank you so much for posting your fix, I'm sure others will benefit from your work. Regards, Capt Gary Link to comment Share on other sites More sharing options...
OSC-Sevilla Posted October 11, 2011 Author Share Posted October 11, 2011 this is because i have added the ajax attribute manager Link to comment Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.