#1
  1. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2003
    Posts
    152
    Rep Power
    15

    PHP and Table Relationship question


    hello all, thanks in advance for your time.

    I haven't yet been able to figure out the proper way to do this so I'm asking here.

    I have tables with fields that share a relationship. They are inventory and tech stock. When a technician places a parts request (which I have no problem with) and that request is pulled up and filled, I would like to deduct the qty filled from the inventory (the part number, which is denoted by a lineID) and append the qty filled to the tech stock table (which is denoted by the same lineId). I'm not sure how to get this done without using multiple querys (which is what I would like to avoid). I'm tying to reduce the amount of querys I use in my code. Thanks again for your time and I look forward to hearing your ideas.
    --------------------------------------------
    Bloody fingers show you mean it
    www.sixdollarshirt.com
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Apr 2003
    Location
    Tacoma, WA
    Posts
    1,353
    Rep Power
    18
    Just to clarify, you have a field with QTY in it. When and item is Shipped you want that transaction to reflect that there is now one less item in stock?

    This is off the cuff, but what about pulling the qty out (with the first query) and at such time as the item needs to be considered no longer in the inventory, use a little php math fuction to take that qty minus whatever qty is being "bought/shipped/whatever" and then Update the qty field for that item with the new lower number.

    Is that basically what you are after?

    Sounds like a reverse counter.

    Either way, you have the Select Statement and then an Update statement.

    Have you tried posting also in the MySql forum?
  4. #3
  5. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2003
    Posts
    152
    Rep Power
    15
    I have not yet. I thank you for your time. I think that I will still need to have several queries. You're right in saying that I just need a simple math function for the new "qty" but I have 3 other tables that need to be updated when all of this happens. It's not a big deal, I was just trying to find a way to type a little less and make my site a little more efficient. Thanks again for taking the time to reply to my question.
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Apr 2003
    Location
    Tacoma, WA
    Posts
    1,353
    Rep Power
    18
    When you say More efficient, what kind of traffic/activity do you expect? I think that the key to a great web app is one that serves the people. Every time you try and do something "nice" for a user (by adding new functionality or something), it will cost you (in terms of coding and queries and everything).

    you are doing the right thing. Plan plan plan. take a break, then plan plan plan. I use large pieces of paper with the workflow on them. One step per page, and spread them out.

    Good luck
  8. #5
  9. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2003
    Posts
    152
    Rep Power
    15
    Thanks for the support. That's why I love this place!

IMN logo majestic logo threadwatch logo seochat tools logo