Products showing in store, but not in admin

What version of Zen Cart are you using? 1.5.5e
What version of PHP and MySQL is your server using? PHP Version 5.5.37, MySQL 5.6.32-78.1
Site URL: https://www.sanefcs.com/catalog

Products like "76001 International Foods Lesson Plans", "76003 What's For Breakfast Lesson Plans", "76004 Great Grains Mini Unit", and others can be found in the store, but I can't find them in the admin area, whether I search by name or number. I don't know a lot about databases, but I can run very basic SQL queries. I can find their products_id in both the products and products_description tables and they match, but I don't know what else to look for or where else to look for what's wrong. I don't like deleting items from the db, and if I ever do so, I do it through the admin interface, NEVER through SQL, so it wouldn't have been done through that.

Where can I check to "recover" the items so they show up again in the admin side of Zen Cart? Thanks for any assistance!

Have you recently changed the database name, or database credentials?
Have you been editing your configure.php files? If so, did you change the DB_PREFIX in one file but not both?

The product names you typed above included numbers, but were those actually your "products_id" numbers?
The search in the Admin->Catalog->Categories/Products (top right search field) screen lets you type in the products_id value to find a product by its database-number.

to be clear, you have no problem logging into the admin? and once logged in, everything looks normal, although you can not find some products, even though they appear on the catalog side? but you do see other products on the admin side?

if that sounds true, i would double check that both configures files point to the same database.

if that is true, then it's possible you might have a problem with the language id associated with those products. you can try executing the following sql statement and provide back the results:

SELECT count(`products_id`), `language_id` FROM `products_description` 
group by language_id

(add you db_prefix if necessary...)

from what i can tell looking at the code, if the db credentials are pointed to the correct database, the language_id may be different from your session, which is why you may see the products.

good luck!

Good point. It could be language-related. But if the store has always only had one language installed then that's less likely to be the cause.

DrByte:

Have you recently changed the database name, or database credentials?
Have you been editing your configure.php files? If so, did you change the DB_PREFIX in one file but not both?

The product names you typed above included numbers, but were those actually your "products_id" numbers?
The search in the Admin->Catalog->Categories/Products (top right search field) screen lets you type in the products_id value to find a product by its database-number.

I have never changed the database name or credentials. I haven't touched the configure.php files in about 2 years. I checked the 2 files and the db sections are exactly the same.

Those numbers are not the products_id values, they are our internal numbers (skus). I tried searching by the products_id value, but they don't show up that way either.

carlwhat:

to be clear, you have no problem logging into the admin? and once logged in, everything looks normal, although you can not find some products, even though they appear on the catalog side? but you do see other products on the admin side?

if that sounds true, i would double check that both configures files point to the same database.

if that is true, then it's possible you might have a problem with the language id associated with those products. you can try executing the following sql statement and provide back the results:

SELECT count(products_id), language_id FROM products_description
group by language_id

> 
> (add you db_prefix if necessary...)
> 
> from what i can tell looking at the code, if the db credentials are pointed to the correct database, the language_id may be different from your session, which is why you may see the products.
> 
> good luck!

Yes, thanks, I can login to admin without issue. Everything looks normal. I cannot find some products there, but they appear fine on the catalog side. I can find most every other products just fine on the admin side.

I ran the query you suggested. Here are the results:
count(`products_id`)    language_id
2711                              1

ok. good information....

so, it looks like you have 23 inactive products (2711- 2688) from the all products page:

Displaying 1 to 20 (of 2688 products)

currently, lets focus on this product:

https://sanefcs.com/catalog/index.php?main_page=product_info&cPath=371&products_id=2431

when you go here:

https://sanefcs.com/catalog/YOUR_ADMIN/categories.php?search=2431

nothing shows up?

ok, i think i have found your problem. it looks like you have deleted category 371... or something else is wrong with category 371.

try going here:

https://sanefcs.com/catalog/YOUR_ADMIN/categories.php?cPath=371

what do you find?

you need to either re-activate or re-create that category number. you might need to go directly to the database and create that....

let me know if that helps ior if you need more help following this path. almost 100% that is your problem.

best.

carlwhat:

currently, lets focus on this product:

https://sanefcs.com/catalog/index.php?main_page=product_info&cPath=371&products_id=2431

when you go here:

https://sanefcs.com/catalog/YOUR_ADMIN/categories.php?search=2431

nothing shows up?

That's correct. Categories 0, products 0. And I did change YOUR_ADMIN. :)

carlwhat:

ok, i think i have found your problem. it looks like you have deleted category 371... or something else is wrong with category 371.

try going here:

https://sanefcs.com/catalog/YOUR_ADMIN/categories.php?cPath=371

what do you find?

you need to either re-activate or re-create that category number. you might need to go directly to the database and create that....

That was also blank. I checked the database and there was no entry for categories_id 371 in the categories or categories_description tables, so I manually added an entry for categories_id 371 in each. I gave it a name of Other and it shows up in the top level of the catalog, but has no products in it (I was hoping it would pick up the missing products).

If I go back and try to search for the product we are focusing on in admin, I still cannot find it by looking in category 371, category Other, searching for 2431, searching by name, or searching by 76001.

ok. AWESOME!

standard SQL disclaimer (quoting shakespeare):

'to err is human, but to really mess things up, you need a computer.... and to mess things up royally, you need SQL'.

i would recommend backing up tables affected prior to doing sql statements and the like.... no warranty here.... all of that stuff....

the products that are NOT appearing can be found via this sql statement:

select products_id, master_categories_id from products where products_id not in
(select products_id from products_to_categories)

to fix this issue, you can issue the following sql statement:

insert into products_to_categories (products_id, categories_id)
select products_id, master_categories_id from products where products_id not in
(select products_id from products_to_categories)

somehow your data got screwed up... but i'm pretty sure this will fix it. the only problem might be a category that does not exist... but even if that category does not exist, you should be able find the item on the admin side after re-inserting the missing records in the products_to_categories table.

let us know!

best.

Perhaps I misspoke in my reply.

The select query you suggest gives 0 results.

we are definitely talking about a data problem. when we look here:

https://sanefcs.com/catalog/index.php?main_page=product_info&products_id=2431

there is no category in the URL, but yet there is a category in the breadcrumbs. and when you click on the breadcrumbs, no product comes up in that category.

we can try the following select, and if that works the insert after that.... i KNOW we are on the right track.

select p.products_id, p.master_categories_id from products p 
left join products_to_categories c on p.products_id = c.products_id and p.master_categories_id = c.categories_id
where c.products_id is NULL and  c.categories_id is null

// if that comes up with the missing products, we can then do:
insert into products_to_categories (products_id, categories_id)
select p.products_id, p.master_categories_id from products p 
left join products_to_categories c on p.products_id = c.products_id and p.master_categories_id = c.categories_id
where c.products_id is NULL and  c.categories_id is null

best.

Woohoo! That worked! 23 records found, 23 records inserted.

When I go back and search for the products I couldn't find before, I can now find them. They weren't all in category 371, but most of them were. Is anything else needed?

Gamethrall:

Woohoo! That worked! 23 records found, 23 records inserted.

When I go back and search for the products I couldn't find before, I can now find them. They weren't all in category 371, but most of them were. Is anything else needed?

no, i think you should be good....

you might want to check that all master_categories_id in the products table exist, and address those if necessary. you can do that with:

select master_categories_id from products
where master_categories_id not in
(select categories_id from categories)

if that comes up with 0 records, no more need be done. if there are some records, you should either change the master_categories_id in the products table, or add the missing category similar to what you did with category 371 somewhere above.

best.

There were 2. I changed the master_categories_id in the products table. Thank you so much for all of your help!