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.