Help Needed.

[snoopy85]

Hi all,

My database consists of tables named "purchases_0108" to "purchases_0708". For the 4 digits of the tables' name, the first 2 digits stands for the month and the last 2 digits stands for the year.

The tables consist of fields :

1) "name" (Company name that I bought the item from)
2) "date" (Date I bought the item)
3) "rate" (Currency rate when I bought the item)
4) "invoice" (Invoice number of the item)
5) "article" (Article number of the item)
6) "description" (Description of the item)
7) "size" (Size of the item)
8) "material" (Material of the item)
9) "finishing" (Finishing of the item)
10) "quantity" (Quantity of the item)
11) "price" (Price of the item)

I also have the same set of tables named "sales_0108" to "sales_0708". Both "purchases" and "sales" tables have the same fields.

What I wish to achieve is have a code which can output my stock balance. There is similar items purchased at different dates in "purchases" tables and also same items sold at different dates in "sales" table. Which means that I have to add up the quantity for each item in each month of the "purchases" table, and take that to minus the quantity for the same item, if I have sold them, in the "sales" table.

I only know basic php programming like mysql_query and mysql_fetch_array, using the while ($row = mysql_fetch_array($result)) to output the records.

Any help from you guys is greatly appreciated. Thanks in advance.

[Jordan]

Do you have any code you can post? Where exactly are you stuck at?

I'm also wondering why you name the tables that way? Why not add two more fields to the table - one for month, one for year. With the two fields added you will be able to filter your results the same as if each year/month had a different table.

[snoopy85]

Hi Jordan,

Yup I got people asking me the same questions. Why would I create different months of each table and not just have 1 table for the purchases and 1 table for the sales. It would be much easier to output the balance.

What I was thinking at first was I wouldn't want too many records in a single table and having different months' table for each purchases and sales table would make it much neaty.

And at the end of each year I would create a table for the year end balance of each item.

I apologize if my structure of database is not good at all. Kindly give your guidance and advice. Thank you!

[Orjan]

to get a stock balance should be a script first adding up all purchases (# items times price) for each row, then subtracting all sales in the same way.

but ofcourse, then you couldn't have any waste products and so on. then you need to sell them for $0...

that's the main idea atleast.

[Jordan]

I believe you should restructure your database table names. You shouldn't worry about how many columns are in a table because you will hardly ever look at the raw tables (you'll use some sort of script, like the one you are trying to build, to view the table). With a rename of your table you can find the stock price with one simple SQL query and sort the results just as easy.