Some people (myself included) use osCommerce as a stock-keeping program as well as a e-commerce sales program. There are drawbacks to doing it this way (eg, lack of “nice” reports, accountancy etc), but it’s certainly do-able.
A question in the official osCommerce forum went like this;
I need a simple bean counting script that will give me a total cash value of all in stock inventory…I have over 6000 items in stock – so I would rather not do it by hand…
Now, me not being a great whiz at Maths, guessed that the user needed to know the product_quantity multiplied by the product_price (per product of course). This is rather easy and goes like this;
[php]SELECT sum(`products_quantity` * `products_price`)
FROM `products`[/php]
This give the total value of all the product quantities you have in store, as shown;
Then I decided that this would be more useful to only show products that have a quantity greater than zero. After all, depending upon how you have your store set up it’s possible you have negative value quantities (over-sold products) – this would obviously skew the results terribly.
So, the new code looks like this:
[php]SELECT `products_id`, sum(`products_quantity` * `products_price`) AS total
FROM `products`
WHERE `products_quantity` > 0
GROUP BY `products_id`[/php]
Now the output looks like this;
(Note that I cut the graphics off, but it shows the value of each product individually)
From the two pieces of code above, I am sure that you can make different code to suit your needs. MySQL can be complicated to understand, but if you hunt Google there are loads of tutorials showing all sorts of cool code. To actually use the code, use PHPMyAdmin (comes standard almost all hosts), click the SQL tab and paste the code into the “Run SQL Query” input box, then press “GO”…
Easy as 123? Maybe not. But fairly easy, for sure.