SQL Help - Need To Change Product Type

[robinstl]

Hello,

I need to run a periodic SQL query which updates the 'product type' for all items that have a specific 'category_id'.

Here's where my abbreviated thinking takes me...

UPDATE products SET products_type ='3' WHERE category_id = '9'

This would work but the 'products' table does not have 'category_id' as a field. The one table I can find which has both (and only) 'product_id' AND 'category_id' is 'products_to_categories'.

I ASSume that I have to somehow incorporate 'products_to_categories' in the query to tie 'category_id' to 'products' to achieve what I need?

Thanks

[DrByte]

Um ... what you're doing is VERY dangerous.

Changing product types just because you can change the number in one of the many related database tables can cause you to end up with data seriously out of sync -- especially if you're switching it between product types which have additional field data which needs to be collected for them.

Nevertheless, I'm sure you've got a reason why you're convinced that what you're doing is acceptable. Thus, yes, you need to incorporate the products_to_categories table as well.

[robinstl]

Ahhh... I very glad that you replied to this thread. First, I'll explain the reason I think I want to be able to do this...

I'm working on a site which has both items for sale (products_type 1) and a 'gallery' of the site owner's personal items he wishes to display (Document - General/product_type 3)

I'm using Easy Populate to add the 'items for sale' just fine however when I use it to populate the one and only category that is set to 'Document General' it forces the items as a product_type 1. Even though the category is set to clearly limit all items to 'Document General' it does not when using EP, in other words they appear 'for sale' which is exactly what I don't want. (If I add them manually it's fine)

Soooo... I logged into phpMyAdmin and changed ONE of the item's product_type from 1 to 3 and it then looked fine on the front end. I thought just writing a simple query would be quicker?

I would MUCH rather find a way to have it import correctly or at the very least have the items listed as product_type 1 and somehow trick it into looking like they aren't for sale as opposed to horsing with the database directly but I can't find any other way.

Thanks for any help.

[DrByte]

You could rewrite EP to add support for a v_products_type field, but that's not exactly an easy task.



*** FOR FUTURE READERS OF THIS THREAD ... I recommend that you be *VERY* sure that you understand all of what you're doing if you are messing around with this. ***

In the case of products_type=1 and products_type=3, the structures are the same, and don't rely on extra fields in other tables (unless you've customized either of those product types in that way).
Thus it's relatively safe to "just change" the products_type setting in *that* case.

The SQL to do it manually would be:

UPDATE products p, products_to_categories ptc 
SET products_type=3
WHERE p.products_id = ptc.products_id 
AND categories_id = 9;

[robinstl]

Hey there,

Thank you very much. I was pretty sure under my specific circumstances that updating that field would be okay but I really didn't know.

I'd love to know how to add the product_type to EP but that eclipses my knowledge. Seems like a handy thing to have doesn't it?

Anyway thanks again :)

[kbellare]

So, is there a way to use EP to update Document-General type, without hacking it? Pl. advise

kiran