CODEWITHSUNDEEP
mysqlxmlopencart
Ivanka Todorova
Ivanka Todorova

Reputation: 10219

OpenCart - XML to MySQL

I have a XML document and a fresh OpenCart installation. The XML Document's content is: http://pastebin.com/59WeK4Qk I was able to get every tag and its value using simplexml():

$sxml = simplexml_load_file("products-sample.xml");

foreach($sxml->products->children() as $products)
{
    foreach($products->children() as $product)
    {
        echo 'Tag: '. $product->getName(). ' Value:' . $product.'<br />';
    }
    echo '<hr />';
}

But I have no coding experience in opencart, so I was wondering is there any guide or tutorial on using opencart database.

My main question is where to put that information I parse from XML?

Upvotes: 1

Views: 3494

Answers (2)

Paul Feakins
Paul Feakins

Reputation: 727

You could just save the XML as a CSV and then use one of the many CSV import extensions such as https://www.opencart.com/index.php?route=marketplace/extension/info&extension_id=17

Upvotes: 0

Paul Gregory
Paul Gregory

Reputation: 1753

The OpenCart database tables have mostly self-explanatory names, but there are important connections that you may overlook, such as oc_product_to_store. Some, like product_attribute or product_discount, can be ignored, depending on your exact needs.

OpenCart allows products to be in multiple categories, and multiple stores, and some data can potentially be in multiple languages. These one-to-many connections require separate tables. There are also simple one-to-one connections like Manufacturer where the same piece of information is common to multiple products so is linked by an _id.

I'm not going to sit here and write an import script for your data for free, but here are a few things I learned from a recent import project that I completed myself which I hope are helpful to someone.

Your question is "where" not "how", so I am going to assume that you already have a preferred method for executing MySQL queries within your script.

These were the tables and fields I needed to use in an INSERT to get a product into OpenCart so it could be added to cart.

  • oc_product : model, sku, upc, ean, jan, isbn, mpn, location, quantity, stock_status_id, manufacturer_id, shipping, price, tax_class_id, date_available, status, date_added, date_modified, image
  • oc_product_description : product_id, language_id, name, description, meta_description, meta_keyword, tag
  • oc_product_to_store product_id, store_id
  • oc_product_to_category product_id, category_id

You will need to first process your categories and manufacturers and add them, so you can look up their ID and use it.

If it is at all possible that you need to UPDATE the products later using an updated XML file, you should use one of sku, upc, ean, jan, isbn, mpn to store an identifying reference (probably your code).

To identify the correct fields and what a valid value would be, here are four simple steps:

  1. Export a backup of your database (eg with phpMyAdmin) and extract it to a text file
  2. Add one product from your XML manually using OpenCart's admin facility (fully and properly, including setting the right Tax Class)
  3. Export and extract again, and compare the two files (eg with Beyond Compare)
  4. Look carefully at the field types (including string length) for each changed field, and work out what any _id relates to.

Upvotes: 3

Related Questions