Magento 2 - SQL creating attribute based on the last word of the product name

[lukaszwoznb0bb]

Hi

I'm looking for sql script which will create attribute based on the last word of the product name.

For example:
name: Button Down Ladies Fruits Strawberry Printed Ladies Cross Border Long SLeeved Jumpsuit Romper
> attribute value "Romper"

name: Womens Leopard Print Long Sleeves Ladies V Neck Trendy Winters Style Pullover Sweater
> attribute value "Sweater"

name: Halter Shoulder Strap Young Plain High-Cut Two-Piece Bikini
> attribute value "Bikini"

etc..

It's a lot of products (over 100k) that's why it's important to run in sql.

Thanks!

[gaurav_harsh1]

Hello @lukaszwoznb0bb 

SQL query would not be preferable, you can use Magento Console Command  where you can get all products and make an foreach loop and update in loops by getting the last word of your name (by space split).

You can break it down in few steps, for ex: you can pass any argument in console command which will take category id so you can update products for each category at once.

print info for each 1000 products so you can observe the items are updating.

Magento EAV Attribute structure is not straight forward so you should not update via SQL query.

the console command may take a little bit extra time but worth it, and it's a one time process so it's okay to spent that time for it.

[Rahul Gupta]

@lukaszwoznb0bb Sql query is not a good option instead you can create a installer or upgrade file and use the fucntion for creating the attribute in foreach loop where you can get all the products and by splitting them as per space you can get the name of the attribute.

So this way you installer script will run for all the products.

Hope it helps!

Thanks

[lukaszwoznb0bb]

This is our current solution but take to much time. That's why I'm looking for solution in sql directly.

[gaurav_harsh1]

@lukaszwoznb0bb 

Just log or print on every 1000 products, the benefit of using console command is there will be no possible duplicate rows, validation will be there.

It can't be a daily process, it must one time process so just give that time, and still you want to add via SQL query, you can use two tables :

1). eav_attribute : it will have all products attributes (Name and the attribute you want to update)

get the ID of both attributes

2). catalog_product_entity_varchar OR catalog_product_entity_[********] : the catalog_product_entity_varchar will have product name so you can get name from here and the other attribute will depend on your attribute type.

So create a php file, get product name and get the last word, then update/create row in other attribute table with following values :

1. attribute_id
2. store_id
3. entity_id
4. value

make sure if there is a row of that attribute_id, store_id, entity_id, don't create a new row.

Hope it will help you to understand the logic for both process.

[lukaszwoznb0bb]

@gaurav_harsh1 

@Rahul Gupta 

Thank you for reply. We will try it.

I have other idea but I have problem with export products (as I said its over 100k products).

Do you know some way to export:

Product ID | Product name

For all of the products (simple an config products)? I mean from sql to csv?

[gaurav_harsh1]

@lukaszwoznb0bb 

just use basic PHP function of creating CSV, take help from here :

https://stackoverflow.com/questions/4249432/export-to-csv-via-php

get the data from the above shared table with MYSQL query.