Jump to content
dev osCommerce
Forum
  • Checkout
  • Login
  • Contact Us
  • Get in touch

osCommerce

The e-commerce.

New Demo
Our Partners

Atrributes can carry a product CODE

OSC-Sevilla

By OSC-Sevilla
in General Support

Recommended Posts

OSC-Sevilla

OSC-Sevilla

Posted
Posted

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
hughesca

hughesca

Posted
Posted

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
hughesca

hughesca

Posted
Posted

....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
OSC-Sevilla

OSC-Sevilla

Posted
  • Author
Posted

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
hughesca

hughesca

Posted
Posted

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
OSC-Sevilla

OSC-Sevilla

Posted
  • Author
Posted

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]

 

----------------------------wacko.png

Link to comment
Share on other sites
hughesca

hughesca

Posted
Posted

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
OSC-Sevilla

OSC-Sevilla

Posted
  • Author
Posted

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
hughesca

hughesca

Posted
Posted

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
OSC-Sevilla

OSC-Sevilla

Posted
  • Author
Posted

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
OSC-Sevilla

OSC-Sevilla

Posted
  • Author
Posted

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
OSC-Sevilla

OSC-Sevilla

Posted
  • Author
Posted

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
OSC-Sevilla

OSC-Sevilla

Posted
  • Author
Posted

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
  • 4 weeks later...
captgary

captgary

Posted
Posted

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
OSC-Sevilla

OSC-Sevilla

Posted
  • Author
Posted

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
captgary

captgary

Posted
Posted

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
captgary

captgary

Posted
Posted

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

Archived

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

Go to topic listing
×
×
  • Create New...