import query partially working

[Scott_C]

Hi, Im afraid Im a bit stumped by this. I have an sql query that is successfully grabbing my products, categories, subcategories etc from another mysql db and is dumping it all into zen cart.

Its working in the zen cart store except for one thing;

I cant see any products or categories in the category tree menu or in the backend.

But if search on a product, its all there - even the listings work; "new products" etc, etc.

Example here; http://www.scolina.com/sTor3/

If you search on say; 4928 - it will show a 4928 self inking stamp. If you then click on 'listing' it will even take you up into the self inking stamp category successfully.

So I know this query is darn close to working.

[kobra]

What format is this other DB data in?

ZenCart indexes it's DB fairly extensively and data must be iin the correct format and written to ALL the associated tables

[Scott_C]

Hi Kobra, the other db is mySQL. Im grabbing text and numbers (sorry Im not a programmer)

Here's my query;

-- get the rest of the product info

delete from products;

insert into 
  products
  (
   products_id,
   products_type,
   products_quantity,
   products_model,
   products_price,
   products_status,
   products_tax_class_id,
   products_price_sorter,
   master_categories_id
  )
(select 
    productID,
    1,
    1,
    0,
    Price - (Price/11),
    1,
    1,
    Price,
    1
  from 
    OTHER_DB.OTHER_TABLE);

-- get the path for the product images 

UPDATE products SET products_image = ( SELECT filename
FROM OTHER_DB.OTHER_TABLE
WHERE productID = products.products_id 
LIMIT 1) ;
  

-- get the product id, name and description

delete from products_description;

insert into 
  products_description
  (
   products_id,
   language_id,
   products_name,
   products_description
  )
(select 
    productID,
    1,
    name,
    description
  from 
    OTHER_DB.OTHER_TABLE);


-- get the category ids (ignoring the root category)

delete from categories;

insert into 
  categories
  (
  categories_id,
  categories_image,
  parent_id,
  sort_order,
  date_added,
  last_modified,
  categories_status
  )
(select 
    categoryID,
    0,
    parent,
    0,
    NULL,
    NULL,
    1
  from 
    OTHER_DB.OTHER_TABLE
  where
    categoryID not in (1));  

-- get the category ids, parent ids, name and description

delete from categories_description;

insert into 
  categories_description
    (
  categories_id,
  language_id,
  categories_name,
  categories_description
  )
(select 
    categoryID,
    1,
    name,
    description
  from 
    OTHER_DB.OTHER_TABLE
  where
    categoryID not in (1));  

-- get the product and category ids

delete from products_to_categories;

insert into 
  products_to_categories
  (
   products_id,
   categories_id 
  )
(select 
    productID ,
    categoryID
  from 
    OTHER_DB.OTHER_TABLE
  where
    categoryID not in (1));  


-- commit all the above changes to the database
commit;

I thought I probably shouldnt be populating the master categories ID field - but ive tried my query both with and without it and its still the same result.

I also thought i was grabbing all instances of categories - and when they map to products etc, but obviously Im missing something.

[Scott_C]

Oh, and I think im matching data types if that makes sense? As in; text field to a text field, whole number to a numerical field etc...

[kobra]

ZenCart first needs a category and it gets a cat unique ID

Products are indexed in the "products to catergories" table

Products are in the product table with a couple of indexes back to the above

There are more tables depending upon the type of product

Writing a "simple" sql will not get info into all required tables

[Scott_C]

True, but my query addresses all the thing you just mentioned.

Ill keep looking.

[Scott_C]

Problem solved.