How can I print a report showing total inventory on hand?

I am trying to deal with my daughter's e-commerce site, https://opalessenceshop.com, following her unexpected death. I have no web site background whatsoever. In the report module I can print a report showing low stock, and I have changed the quantities of product I was unable to take from my daughter's home in another state. I was also able, through the catalog categories/products tab to take screen shots of each page of each category, which collectively let me know how much product I had of the inventory I was able to take with me. But since then we have made sales through the web site, reducing the stock. It was very time consuming to have to go through each page, and I don't want to have to do that every time there are sales made. What I would like to do is print out one report, maybe weekly, showing all of the existing inventory. I have looked at every tab in the back office and cannot find anything that will do this. When her existing stock has been sold, I will take down the website, because she was the artisan and entrepreneur. If I can have the list of inventory, I may be able to sell some of the stock locally, off site. But I would need a printed list to help me do that.

[I apologize that I cannot answer the other required questions, because my daughter was also a web designer, and she set up the web site herself.] I would appreciate any assistance.

Sorry to hear about your loss ...
You would have to download and FTP the files to your site so you can get an inventory count of the entire site ...

http://www.zen-cart.com/downloads.php?do=file&id=173

So sorry for your loss.. Most of the reports or solutions you will find are going to require you "installing" or editing some files. While it's usually a pretty simple process, you might not be interested in learning how to do this for a short term need. I would suggest you could run a query right from the database to get the report you need. Normally I wouldn't recommend doing it this way, but it would be a quick solution that wouldn't involve you modifying the website. It won't look fancy, but will give you the info you need. Basically you would need access to her web hosting account - hopefully you have that? If so, I can give you a few (fairly) simple steps to generate a report that will show: products_id, products_model, products_name, and products_quantity

All you would need is the username/password for the hosting account, and the name of the database that the store uses. (this can be found by downloading the configure.php file from the /includes directory of the shop. there is a field towards the bottom that lists the database name.

From there, (in a nutshell) you would
-log into hosting account "cpanel"
-open phpmyadmin
-open correct database
-run the following query

select p.products_id, p.products_model, p.products_quantity, pd.products_name from products p, products_description pd where p.products_id = pd.products_id

After the results show, you can choose to display them all on screen and then print, or you could export into an excel file.

If you have any trouble along the way, please let me know. (or, if you call your hosting company - i'm sure they could do this for you for little to no fee - especially considering the circumstances)

I do have access to the hosting account and will try this.

one word of caution - not at all meant to scare you away. be careful in phpmyadmin. what you are doing is just a simple "select" query, which WILL NOT modify, add, or delete any information from the database. However, if someone starts mucking around in that program and doesn't know what they're doing - you can potentially do lots of damage to the database. (again - not meant to scare you.. running this report will NOT wipe anything out. just in general, tread carefully in this program).

I got stuck at the point in phpmyadmin, where it is asking for my SQL password, which I don't know. I got into everything else by resetting the passwords, but this reset screen warns "PLEASE BE AWARE that any scripts using the old password will not be automatically updated. These applications will break until you update them to use the new password." I don't know what else uses that or how to update the applications, so I am loathe to do this.

yes, DO NOT change that password right now..

sorry, when I login to most cpanels, then access phpmyadmin, it automatically logs in. if you're being asked for a username/password at that point - go into the files of the website, look in the includes folder, there's a file called configure.php

download it and view it with notepad or similar (or you can probably view it from your "file manager" or similar in cpanel).. you're looking for the mysql username and password info towards the bottom. (don't share these here obviously, but take them and use them to login)

I don't know where on the website to go to access the files

You can use the "File Manager" in cPanel to see your file/folder structure, and there is a button in the top right if I recall correctly that will let you look at a file's contents. Don't try editing anything that way, though.

Thanks to everyone. I was able to get the information I needed. Is there a way to frame the query that would omit items where the quantity is 0?

Opal's Mom:

Thanks to everyone. I was able to get the information I needed. Is there a way to frame the query that would omit items where the quantity is 0?

this should work:

select p.products_id, p.products_model, p.products_quantity, pd.products_name from products p, products_description pd where p.products_id = pd.products_id [I]and p.products_quantity > 0[/I]

I actually just compared my list to the inventory showing in the Categories / Products tab on Zen Cart (and what I actually have on hand), and the report I generated through the query is missing a number of items.

Thanks so much--it did work, and the report included everything, even what had been omitted the first time around.

jc8125:

this should work:

select p.products_id, p.products_model, p.products_quantity, pd.products_name from products p, products_description pd where p.products_id = pd.products_id [I]and p.products_quantity > 0[/I]

Glad to hear! Don't hesitate to ask if you need anything else.

Hi, sorry to intrude to the thread:
I have tried running the SQL query in PHPmyadmin on the database for my store and get the following error message:
#1146 - Table 'beautyg1_test.products' doesn't exist
(where beautyg1_test is the database name and all tables in the db start with "zen_", e.g. zen_products)

Can you offer me any assistance/advice about how to fix this?
I am surprised that the ability to produce a full product inventory isn't built in to zencart?

Thanks in advance.

change in that query beautyg1_test.products with your_database_name.zen_products

Thanks for the fast reply solo_400.
Please can you clarify what I should change in the script:

select p.products_id, p.products_model, p.products_quantity, pd.products_name from products p, products_description pd where p.products_id = pd.products_id and p.products_quantity > 0

to get it run on my database?
Also, in phpmyadmin/query, should I select a specific table (e.g. zen_products)?

In phpmyadmin select your database name ( left side up )
Once selected , run the query using sql tab :

select
p.products_id, p.products_model, p.products_quantity, pd.products_name
from zen_products p, zen_products_description pd
where p.products_id = pd.products_id and
p.products_quantity > 0

Thank you very much Solo_400 that worked perfectly!