Multiply Several Rows of One Table by Specific Values of Another
I am by no means an expert in php. I've been able to get to this point of my project without much help. The code I'm currently working on is by far the most ambitious challenge I've faced. It's also, kind of the key to my project. Therefore, I figured I'd ask for some assistance to guide me before I head too far down the wrong path. I have the concept worked out, (I think) just how to code it I'm really not sure where to start.
A quick overview of what I'm trying to accomplish. (Sorry, no actual examples as this is still in the concept stage.) I'll have the following 6 tables:
The items tables will store the current stats for each individual item with a unique id. These will change periodically and the final table will need to be updated accordingly so users can view their current order totals.
The order table will have an item id from each item selected from each of the items tables. As well as the group id that the order belongs to.
The group_setup table will contain the unique id of the group and the values of the multipliers that the item stats need to be multiplied by for that particular goup.
The final_table will have the three items id and an associated value which is the item's stat multiplied by the stat_mult associated with it in the group_setup table.
On an administrative page, I need to build code that after updating the item stats, I can multiply the stat by the stat_mult and insert that value into the final table. My problem lays with the variable values the different group's stat mult will have. I can get it to work with a fixed value, but the variable values would really make the application a lot more marketable. I'd rather challenge myself and do it right with a little more work.
I'm not quite sure where to begin. If the row count was equal throughout the tables, I think I could work it out. But the group_setup only having one row for multiple order rows is what's throwing me off. If anyone has any suggestions as to how to proceed, I would greatly appreciate the advice.
I hope my explanation wasn't too long winded!
Thank you in advance,