DATABASE STRUCTURE INFO
Basically I made this database to track/store product order quantity information that I input into our system every day. We get orders from up to 6 sources for any given product. I have this structured so I can see a daily total from every source after I input the data. Say for instance on of our products comes in from all 6 sources and my boss wants to know how many such and such orders we had from any given source I can just pull it up. This system is currently up and running without any flaws. I just want to increase its use and incorporate the ability to sort by product as well... here is the tricky part the table structure I am using has a different table for each source. Each source table has a field pertaining to each product that that particular source gives us. So in order to get a total product count I need to pull from up to 7 different tables and put it into one table.
so I did some SQL research and I think I found code that will let me do this but I am totally unsure if it is possible to use this particular code with PHPRunner.
SELECT aistotals.date,aistotals.ld,livemercialtotals.ld,pitotals.ld,westtotals.ld,webto
tals.ld,ecptotals.ld
INTO ldtotals (date,aisdoctor,livemercialdoctor,pidoctor,westdoctor,webdoctor,ecpdoctor)
FROM aistotals
INNER JOIN livemercialtotals,pitotals,westtotals,webtotals,ecptotals
Tables/fields I need to pull info from are as follows FIELDS =
I need
[color="#FF0000"]date/
[color="#FF0000"]ld from
[color="#FF0000"]ld from
[color="#FF0000"]ld from
[color="#FF0000"]ld from
[color="#FF0000"]ld from ecptotals
This actually needs to be done for all 5 products we support right now, and able to be adapted as more products get added. (some products have alot less sources)
pulling this data and inserting into the product total table (ldtotals) would then give me a total count for each product reguardless of which source it came from. The structure for the table this is being inserted into is |date|aisdoctor|livemercialdoctor|pidoctor|westdoctor|webdoctor|ecpdoctor|
So all this needs to happen after I hit the "ADD" button on the final "ADD NEW RECORD" page I have setup. Since I input these everyday I have the add pages daisy chained (using after add events pointing to the next add page in the sequence). So as soon as I hit "ADD" in the AIS page it brings me to the Livemerical page to add those totals, and so on and so on. So when I hit add on the ECP totals page (final source) I want all this to add into the other tables.
In a nutshell I need 7 different tables data to be rearanged into 5 other tables elsewhere in the database....
I know this is probably not explained well enough and you want to shoot me but I figured I would give it a try.