Missing Products after upgrade

Hi all,

Please can somebody help me out.

I have upgraded and imported all of the products into the new database, and all the images into the images folder, but when I click in admin>categories, and then go to look at the products, there isn't any there.
Same happens on site itself....the categories have counts next to them but they don't have products in?

have I missed something?

Thanks all

Peek in phpMyAdmin at your database ...

1 browsing the table:
products

do all Products have:
master_categories_id
product_type

2 Browsing the table:
products_description

do you have the same number of Products as in the table:
products

3 browsing the table:
products_to_categories

are all of the fields for:
products_id
categories_id

set to valid Products in the table:
products

and set to valid Categories in the table:
categories

Also take a look at you language id's, in the language table and in your product/category tables, to see if they match up

529 in products....530 in products_to_categories :(

Is there an easy way to find the missing one please?

Thanks for your help Ajeh

Design75:

Also take a look at you language id's, in the language table and in your product/category tables, to see if they match up

Thanks for that.....do you mean utf8_general_ci in the db table structure?

Do you have Linked Products on any of your Products?

Apparently 2, but I only know that by the yellow dot next to the category in admin as the products don't show, even though the categories have product counts in admin and front end

When you say you imported the Products from your old site to the new database ... how did you do that?

By importing the table data.....copied the sql and inserted the old data into the new products table. I had a dangling carrot mod thing as well so I made a field in the new table to accommodate the products_carrot field, imported data, then deleted the field, as I don't use the mod anymore

...also did the same with the categories data

There are multiple tables involved with Products and Categories ...

products
products_description
categories
categories_description
products_to_categories

Do you have any Attributes on your Products?

Do you have any Specials?

Do you have any Featured Products?

No, don't have any of those.

I did import most of the data the same way as I previously mentioned

Did you bring in the first 5 tables?

Do the tables categories and categories_description match up?

How many records are in the table:
product_types

yes, they do match up.....I guess you mean the number of records in each one?
Product types only has 5 records in the table

What Zen Cart version was your old database base?

1.39h the version was

If you run these tests in the SQL in phpMyAdmin for your database what comes up?

#TEST PRODUCTS VS PRODUCTS_DESCRIPTION
select p.products_id, p.products_date_added, p.products_last_modified
from products p
left outer join products_description pd on p.products_id = pd.products_id
where pd.products_id is null;

#TEST PRODUCTS_DESCRIPTION VS PRODUCTS
select pd.products_id, pd.products_name
from products_description pd
left outer join products p on pd.products_id = p.products_id
where p.products_id is null;

#TEST CATEGORIES VS CATEGORIES_DESCRIPTION
select c.categories_id, c.date_added, c.last_modified
from categories c
left outer join categories_description cd on c.categories_id = cd.categories_id
where cd.categories_id is null;

#TEST CATEGORIES_DESCRIPTION VS CATEGORIES
select cd.categories_id, cd.categories_name
from categories_description cd
left outer join categories c on cd.categories_id = c.categories_id
where c.categories_id is null;

#TEST PRODUCTS VS PRODUCTS_TO_CATEGORIES
select p.products_id, ptoc.categories_id 
from products p
left outer join products_to_categories ptoc on p.products_id = ptoc.products_id
where ptoc.products_id is null;

#TEST PRODUCTS_DESCRIPTION VS PRODUCTS_TO_CATEGORIES
select pd.products_id, pd.products_name, ptoc.categories_id 
from products_description pd
left outer join products_to_categories ptoc on pd.products_id = ptoc.products_id
where ptoc.products_id is null;

#TEST CATEGORIES VS PRODUCTS_TO_CATEGORIES
select c.categories_id, c.parent_id, ptoc.categories_id
from categories c
left outer join products_to_categories ptoc on c.categories_id = ptoc.categories_id
where ptoc.categories_id is null;

Ajeh:

If you run these tests in the SQL in phpMyAdmin for your database what comes up?

#TEST PRODUCTS VS PRODUCTS_DESCRIPTION
select p.products_id, p.products_date_added, p.products_last_modified
from products p
left outer join products_description pd on p.products_id = pd.products_id
where pd.products_id is null;

>  12 pages of results
> 
> ```
#TEST PRODUCTS_DESCRIPTION VS PRODUCTS
select pd.products_id, pd.products_name
from products_description pd
left outer join products p on pd.products_id = p.products_id
where p.products_id is null;

0 results

#TEST CATEGORIES VS CATEGORIES_DESCRIPTION
select c.categories_id, c.date_added, c.last_modified
from categories c
left outer join categories_description cd on c.categories_id = cd.categories_id
where cd.categories_id is null;

> 
>   Edit Edit  Copy Copy  Delete Delete 
>  
> 27 2009-08-31 18:19:19 2012-10-14 11:08:37 
>   Edit Edit  Copy Copy  Delete Delete 
>  
> 57 2010-07-18 18:03:50 2013-04-18 11:22:17 
>   Edit Edit  Copy Copy  Delete Delete 
>  
> 58 2010-07-18 18:09:53 2012-05-07 10:16:49 
>   Edit Edit  Copy Copy  Delete Delete 
>  
> 65 2010-08-10 10:23:59 2013-04-18 11:14:55 
>   Edit Edit  Copy Copy  Delete Delete 
>  
> 156 2012-11-02 18:15:52 2012-12-31 07:33:26 
>   Edit Edit  Copy Copy  Delete Delete 
>  
> 147 2012-04-03 16:34:59 2012-10-14 11:06:00 
>   Edit Edit  Copy Copy  Delete Delete 
>  
> 183 2013-07-19 17:34:25 2013-07-19 17:43:35 
>   Edit Edit  Copy Copy  Delete Delete 
>  
> 150 2012-05-05 08:53:37 2013-04-18 11:14:20 
>   Edit Edit  Copy Copy  Delete Delete 
>  
> 177 2013-04-12 10:25:21 NULL 
>   Edit Edit  Copy Copy  Delete Delete 
>  
> 182 2013-07-19 16:50:07 NULL 
>   Edit Edit  Copy Copy  Delete Delete 
>  
> 145 2012-03-30 16:48:02 2013-10-26 07:22:43 
>   Edit Edit  Copy Copy  Delete Delete 
>  
> 173 2013-01-26 12:44:00 2013-09-02 12:17:27 
>   Edit Edit  Copy Copy  Delete Delete 
>  
> 180 2013-05-16 07:04:45 NULL 
>   Edit Edit  Copy Copy  Delete Delete 
>  
> 179 2013-05-16 07:00:31 NULL 
>   Edit Edit  Copy Copy  Delete Delete 
>  
> 166 2013-01-01 12:29:39 2013-01-01 12:52:14 
>   Edit Edit  Copy Copy  Delete Delete 
>  
> 167 2013-01-03 18:45:04 2013-01-28 21:16:54 
>   Edit Edit  Copy Copy  Delete Delete 
>  
> 168 2013-01-05 16:54:15 2013-01-11 09:18:06 
>   Edit Edit  Copy Copy  Delete Delete 
>  
> 169 2013-01-26 12:36:17 NULL 
>   Edit Edit  Copy Copy  Delete Delete 
>  
> 116 2011-05-25 13:13:38 2012-04-03 17:02:19 
>   Edit Edit  Copy Copy  Delete Delete 
>  
> 178 2013-04-18 11:12:10 2013-04-18 11:15:06 
> 
> 
> 
> ```
#TEST CATEGORIES_DESCRIPTION VS CATEGORIES
select cd.categories_id, cd.categories_name
from categories_description cd
left outer join categories c on cd.categories_id = c.categories_id
where c.categories_id is null;

No results

#TEST PRODUCTS VS PRODUCTS_TO_CATEGORIES
select p.products_id, ptoc.categories_id
from products p
left outer join products_to_categories ptoc on p.products_id = ptoc.products_id
where ptoc.products_id is null;

> 
> No results
> 
> 
> ```
#TEST PRODUCTS_DESCRIPTION VS PRODUCTS_TO_CATEGORIES
select pd.products_id, pd.products_name, ptoc.categories_id 
from products_description pd
left outer join products_to_categories ptoc on pd.products_id = ptoc.products_id
where ptoc.products_id is null;

No results

#TEST CATEGORIES VS PRODUCTS_TO_CATEGORIES
select c.categories_id, c.parent_id, ptoc.categories_id
from categories c
left outer join products_to_categories ptoc on c.categories_id = ptoc.categories_id
where ptoc.categories_id is null;

categories_id 

parent_id 

categories_id 
 

116 0 NULL 
169 0 NULL

You said you got 12 pages of results for:

#TEST PRODUCTS VS PRODUCTS_DESCRIPTION

If you write down the first 10 products_id that show, and search the table products for them how many come up?

If you write down the first 10 products_id that show, and search the table products_description for them how many come up?

Then for:
#TEST CATEGORIES VS CATEGORIES_DESCRIPTION

You showed a number of records ...
If you write down the first 10 categories_id that show, and search the table categories for them how many come up?

If you write down the first 10 categories_id that show, and search the table categories_description for them how many come up?