Magento, programmatically adding category to products in script with SQL

Question

I am in the process of determining the best way to programmatically add Categories to Products. The Categories are obtained from third party data outside of the magento install, I then parse this data and want to update the magento: "catalog_category_product" table, associating found categories to their respective products.

After adding a category to product association in "catalog_category_product" as test, the category did not appear on the magento front-end site. After more searching I added an entry to the "catalog_category_product_index" table, the product now shows in the proper category on the front-end site.

Is this all that is necessary? I am afraid there are additional magento event hooks tied to the Products and Categories and they may be updating additional tables that I am not aware of.

Can I add rows to these two tables with SQL without incident, or do I need to load the magento app and do this through the EAV Product and Category models? Am I on the right track?

Update

I am not actually 'importing' any new data. The Products and Categories are in-place in the system already. I have also not added any custom observers and am using the vanilla Products and Categories models.

I am parsing the text file to get the proper values and then using plain SQL UPDATE statements.

I simply want to make sure that by taking this route, the only tables I need to programmatically update are 'catalog_category_product' and 'catalog_category_product_index'.

Answer 1

I personally don't advise you to use plain SQL rather use the API like:

define('MAGENTO', realpath(dirname(__FILE__)));
require_once MAGENTO . '/app/Mage.php';
Mage::app()->setCurrentStore(Mage_Core_Model_App::ADMIN_STORE_ID);

// Load products 
$products = Mage::getModel('catalog/product')
            ->setStoreId(Mage_Core_Model_App::ADMIN_STORE_ID)
            ->getAll();

// Load categories
$category = Mage::getModel('catalog/category');
            ->setStoreId(Mage_Core_Model_App::ADMIN_STORE_ID);
$categories = $category->getAll();
foreach($products as $product) {
    // Get relevant category 
    $product->setCategoryIds(array($category->getId()));
    $product->save();
}

The code above is very rough example but with few tweaks should do the job. If you use plain SQL you risk to break your data or relations but using the API Magento should handle it right for you.

Answer 2

How do you link your categories and products ? Did you code something specific ? If yes : Show some code.

Anyways, magento offers many ways to do what you need in STANDARD. Look in the Backoffice in the menu : System / Imports-Exports

There are 2 differents methods in magento :

• Dataflow profiles : it works with Models and is pretty slow (but by calling ->save() and ->load() on all your models, it ensures that any Observer you added will be called !!)

• new Imports/Exports profiles : it works with pure SQL queries and is pretty fast (but it's hardcoded so ... say bye-bye to any load/save custom observers you added)

Like you said, Magento got events hooks linked to product and categories (and all other models). In the after save call back of a product and categories, for example, magento will reindex all the associated INDEXES like the catalog_category_index you tried to add manually..

So :

• don't do that yourself, use magento Import/Exports profiles, Dataflow profiles or API.
• depending on the quantity of product/categories you have, choose the right Import to use (see above, the differences). Dataflow is slower but uses Models ..