Virtuemart Table Structure

Replies to This Discussion

Permalink Reply by Thomas Kahl on January 9, 2010 at 5:40pm

Hi,

i don't have the database structure and relationship in a document - but it is in my head ;-) If you tell me what you want to know, i'm sure that i can help you...

Thomas

Permalink Reply by Alexis Marks on January 9, 2010 at 5:56pm

Here is my immediate problem:

I have a client that I setup a Virtuemart site for. He had almost 1000 products to add so he used a spreadsheet. Of course, instead of using the pre-formatted one I sent him, he decided to make his own. My contract with the client is complete as soon as the products are updated. Usually I would stick to my guns about not using the right spreadsheet, but I just want to close this one out.

I have managed to do enough Excel magic to convert the categories into categories id, etc and form the INSERT INTO statements. I ran them in phpMyAdmin and all the rows were successfully added.

However, the products are not showing up on the front end or back end. I am assuming that somewhere along the way, I missed inputting some relational table and it is not grabbing the rows when the JOIN is used.

Here are the tables I originally inserted into:
jos_vm_product
jos_vm_product_category_xref
jos_vm_product_mf_xref
jos_vm_product_price

Later, added records for:
jos_vm_product_product_type_xref

I appreciate any help. Thank you.

Permalink Reply by Thomas Kahl on January 10, 2010 at 7:51am

Hi,

first of all: i would use CSVimproved to import the products. It is much easier and more important, it checks the product-data during import. If you import it manually, it can be that you have corrupted data in your Excel / CSV. Also, don't forget to check the character encoding in Excel / CSV.

If you want to import manually, i suggest that you create a product manually in the Virtuemart backend and use some of the fields as default values.

This is the very basic product structure you need to get vm working. All other additional features (attributes, product types ...) are not needed for the basic setup.

#__vm_product: basic product table
Important:
unique fields: product_id, product_sku
vendor_id: must exist in #__vm_vendor (normally 1)
product_tax_id: must exist in #__vm_tax_rate
all other fields should be self-explaining

#__vm_product_category: basic category-table
#__vm_category_xref: category-hierarchy

#___vm_product_category_xref: relation between products and categories
product_id and category_id should exist in the basic-tables. If you have multiple category references for one product, the ordering is stored in product_list (normally 1)

#__vm_product_price: holds the prices in a 1:n relation to products (1 product can have multiple price entries)
Important: set up a price for the DEFAULT shopper-group-id (normally 5)! Set date and quantity ranges to 0 if you don't need this.

#__vm_manufacturer and #__vm_prduct_mf_xref
You should at least add one default manufacturer in #__vm_manufacturer and add the manufacturer_id for each product_id in #__vm_prduct_mf_xref

You menttioned the product-types. This is a little more complex. Here i would really suggest that you set up am example in the backend manually and see what happened in the corresponding tables.

Did this help you?

Permalink Reply by Alexis Marks on January 14, 2010 at 1:04am

Thank you VERY much!! It was the vendor table that was causing the problem. Somehow the default value was not matching the rest of the values.

Alexis

Permalink Reply by Youjoomla on October 19, 2010 at 10:37am

here is our sql from YJ VM Latest it might come in handy



SELECT #__vm_product.product_id,category_id,product_thumb_image,product_name,product_price,product_desc FROM #__vm_product INNER JOIN #__vm_product_price ON #__vm_product.product_id = #__vm_product_price.product_id INNER JOIN #__vm_product_category_xref ON #__vm_product.product_id = #__vm_product_category_xref.product_id WHERE product_publish = 'Y' AND #__vm_product.product_id IN (2) ORDER BY RAND() LIMIT 1