Multiply Several Rows of One Table by Specific Values of Another

[Cfennell]

Hello all,

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:

item1
  id
  stat1
  stat2
  stat3

item2
  id
  stat1
  stat2
  stat3

item3
  id
  stat1
  stat2
  stat3

order
  id
  group_id
  item1_id
  item2_id
  item3_id

group_setup
  group_id
  stat1_mult
  stat2_mult
  stat3_mult

final_table
  order_id
  item1_id
  item1_value
  item2_id
  item2_value
  item3_id
  item3_value

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,
Craig

[Cfennell]

I'm making some headway using multidimensional arrays and array_walk. Hopefully after a good nights sleep, I'll be able to make some progress. I'll keep the thread updated as I go.

Craig

[Cfennell]

No sleep, but I was able to figure it out. I pulled all the info from the order table and put into a multidimensional array. I then put that through an array_walk where in the function, I performed a query to pull the group specific info from the group_setup table to use the proper multiplier for the specific order_id.

I think taking a step back and trying to put into words what I was trying to accomplish allowed me to really break down the logic step by step. I then used what I had in my tool shed along with a new tool to get the job done.

Still have some work to do, to complete it, but I'll be doing it with a smile!

May not be the most efficient way, but it will work for now.

Take care,
Craig

[StarLion]

I'm not really understanding what you want to do. Why are you keeping a final table at all?
Why are there 3 tables for items?

[Cfennell]

I actually don't need the final table.

The multiple items tables came about from inexperience and poor planning. I had thought about combining them into one, but that may throw a lot of the script off. Right now, I just want to get a completed model I can start testing and concentrate on tightening up the code after that.