Help with adding attributes to many products

[mdegrandis]

We have over 6,000 products, and most of those I need to add an attribute and 3 values to. The products are in all different categories, and the match by existing attribute(from the global changes in option name manager or values manager) can't match on text attributes.
Seems I am stuck in using phpmyadmin to try and sort out which products I need to add the attribute for. My sql is a little weak, so I was hoping a sql guru could point me in the right direction.


Thanks Matt

[kuroi]

You've told us some of the things that you can't match against. But to assess the feasibility of doing this with SQL, we would need to know what it is possible to match against!

[mdegrandis]

Sorry! I should have thought about it.


We can match against product name having 'badge' or the attribute 'name' and 'fastener'
I can see the products_options_id for name (a text attribute) being 1
and fastener at 3. I can't match on just fastener it would match some products that I don't want.


Thanks again for the reply.

[kuroi]

That appears to be enough to select the products needing stock to be set. Do you have an algorithm for knowing how much stock to set for each product/attributes combination?

[mdegrandis]

Thank you again for the prompt reply, but I think I may still need to clarify what I wanted to do. My apologies if you knew this and I am the one confused.

We have products with the text attribute 'Name' and then a dropdown of 'Fastener' (with varying values in it). To these products we want to add a new attribute 'Please Select your Case' (and then all the options values that go with it, but the built in zen global controls can handle that, I think.)

We don't use ZenCart for any stock functions, so they would all be left at zero.

So, I think what I need to do is figure out how to combine these two queries on the products_attributes table:

SELECT *
FROM `zen_products_attributes`
WHERE `options_id` =1

And

SELECT *
FROM `zen_products_attributes`
WHERE `options_id` =3

And then add in my attribute options_id 122 (and values)
I also think I need to only do this once per product_id


Thanks again for helping I really do appreciate it.

[mdegrandis]

After playing around, and learning sub-queries I made some progress. Here is what I used to get my list of product Id's that will need to have rows inserted to the zen_products_attributes table.

SELECT DISTINCT `products_id`
FROM `zen_products_attributes` 
WHERE `products_id` IN 
(SELECT `products_id`
FROM `zen_products_attributes`
WHERE `options_id` = 1)
AND `products_id` IN
(SELECT `products_id`
FROM `zen_products_attributes`
WHERE `options_id` = 3)

This returns all the products with both a name and a fastener attribute, and only once per product Id.

I just need to learn how to wrap that query with my insert so that I can insert my values. Or I might do just one, and use the global ZC copy all options values to option name.

Almost there! Does anyone have any ides, or smart remarks about this issue?

[mdegrandis]

I did finally get this done, in a semi automated way.

SELECT DISTINCT `products_id`
FROM `zen_products_attributes` 
WHERE `products_id` IN 
(SELECT `products_id`
FROM `zen_products_attributes`
WHERE `options_id` = 93)

where the id is the name of the text attribute I wanted to match to.

This returns a list of product ids, so I ran this in a php loop. Here is my code...use your own password.

//EDIT YOUR MySQL Connection Info:
$DB_Server =         //your MySQL Server 
$DB_Username =                  //your MySQL User Name 
$DB_Password =                 //your MySQL Password 
$DB_DBName =                 //your MySQL Database Name 

$sql = "SELECT DISTINCT `products_id`
FROM `zen_products_attributes` 
WHERE `products_id` IN 
(SELECT `products_id`
FROM `zen_products_attributes`
WHERE `options_id` = 93)

";
$Connect = @mysql_connect($DB_Server, $DB_Username, $DB_Password)
    or die("Couldn't connect to MySQL:<br>" . mysql_error() . "<br>" . mysql_errno());

$Db = @mysql_select_db($DB_DBName, $Connect)
    or die("Couldn't select database:<br>" . mysql_error(). "<br>" . mysql_errno());

$result = @mysql_query($sql)
    or die("Couldn't execute query:<br>" . mysql_error(). "<br>" . mysql_errno());
    for($ik=0;$ik<mysql_num_rows($result);$ik++)  
            {
                $rows = mysql_fetch_assoc($result);
                $sql2 = "INSERT INTO `zen_products_attributes` (`products_attributes_id`, `products_id`, `options_id`, `options_values_id`, `options_values_price`, `price_prefix`, `products_options_sort_order`, `product_attribute_is_free`, `products_attributes_weight`, `products_attributes_weight_prefix`, `attributes_display_only`, `attributes_default`, `attributes_discounted`, `attributes_image`, `attributes_price_base_included`, `attributes_price_onetime`, `attributes_price_factor`, `attributes_price_factor_offset`, `attributes_price_factor_onetime`, `attributes_price_factor_onetime_offset`, `attributes_qty_prices`, `attributes_qty_prices_onetime`, `attributes_price_words`, `attributes_price_words_free`, `attributes_price_letters`, `attributes_price_letters_free`, `attributes_required`, `products_attributes_products_id`, `products_attributes_products_quantity`, `products_attributes_type`) VALUES
(NULL," . $rows['products_id'] . ", 122, 322, '0.0000', '+', 0, 1, 0, '+', 1, 1, 1, '', 1, '0.0000', '0.0000', '0.0000', '0.0000', '0.0000', '', '', '0.0000', 0, '0.0000', 0, 0, 0, 0, 0), 
(NULL," . $rows['products_id'] . ", 122, 320, '0.0000', '+', 0, 1, 0, '+', 0, 0, 1, '', 1, '0.0000', '0.0000', '0.0000', '0.0000', '0.0000', '', '', '0.0000', 0, '0.0000', 0, 0, 0, 0, 0), 
(NULL," . $rows['products_id'] . ", 122, 321, '0.0000', '+', 0, 1, 0, '+', 0, 0, 1, '', 1, '0.0000', '0.0000', '0.0000', '0.0000', '0.0000', '', '', '0.0000', 0, '0.0000', 0, 0, 0, 0, 0) ";                            
$Connect = @mysql_connect($DB_Server, $DB_Username, $DB_Password)
    or die("Couldn't connect to MySQL:<br>" . mysql_error() . "<br>" . mysql_errno());

$Db = @mysql_select_db($DB_DBName, $Connect)
    or die("Couldn't select database:<br>" . mysql_error(). "<br>" . mysql_errno());

$result2 = @mysql_query($sql2)
or die("Couldn't execute query:<br>" . mysql_error(). "<br>" . mysql_errno());            
            }//for 

Anyway, job got done, but I ended up with duplicate option values for all of them. I used the built in functions to remove and add values never removing all of them or adding all of them.

Thanks for the help kuroi.