SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Enthusiast
    Join Date
    Mar 2010
    Location
    Boston
    Posts
    50
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Multiply Several Rows of One Table by Specific Values of Another

    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:
    Code:
    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

  2. #2
    SitePoint Enthusiast
    Join Date
    Mar 2010
    Location
    Boston
    Posts
    50
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  3. #3
    SitePoint Enthusiast
    Join Date
    Mar 2010
    Location
    Boston
    Posts
    50
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  4. #4
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    69 Post(s)
    Tagged
    0 Thread(s)
    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?

  5. #5
    SitePoint Enthusiast
    Join Date
    Mar 2010
    Location
    Boston
    Posts
    50
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •