Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 10 of 10
  1. #1
    New Coder
    Join Date
    Apr 2011
    Posts
    59
    Thanks
    8
    Thanked 0 Times in 0 Posts

    Virtual "building" in PHP and MySQL. How to plan?

    Sorry if my subject wasn't descriptive enough, I didn't know what to call it...

    Here is the basic situation:

    I'm coding a game website for someone. And in this game, the person wants there to be virtual "shops" where you buy metals and such for onsite currency. And you can also buy these "blueprints" from the shops. In these blueprints, there is the information saying how many of each metal you need to build thing "x". I've got the shops and onsite currency working fine. Here is the problem.

    I have no idea how to store the "blueprints" in the table and then check and see if the user has bought all the items that they need for thing "x". It needs to be done in such a way that the owner can add plans on the fly without need for recoding. There also need to be an adjustable number of items needed for each blueprint, thought there can be a maximum of possible (so like when the owner adds a new plan, she has up to 10 items she can put in, but she can use less if she wants).

    I'm not asking for code or anything, I know I can write it. I'm just asking what would be the best way to set up the database, and a basic game plan?

    Thanks for any help you can give me, feel free to ask questions/clarification.

    Kuri

    Note: I put this here because I am most comfortable in coding in PHP, and I plan to use it for this. Should it have been in the SQL part?
    Last edited by Kurisvo; 04-30-2011 at 03:30 AM.

  • #2
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,979
    Thanks
    4
    Thanked 2,659 Times in 2,628 Posts
    Yeah, this is SQL so I'll move it to the mysql forums. If its not in mysql, I can move it to the other databases after.
    This is a many to many relationship. You'll need three tables to do this, and by using three you will automatically create a dynamic aspect to the required materials, so no coding or structure modification would be required.
    Code:
    +-------------+         +-------------------+          +-------------+
    | Material    |         | BlueprintMaterial |          | Blueprint   | 
    +-------------+>o-----|<+-------------------+>|------o<+-------------+
    | id [PK][AI] |         | bID [PK][FK]      |          | id [PK][AI] |
    | name        |         | mID [PK][FK]      |          | name        |
    +-------------+         | quantity          |          +-------------+
                            +-------------------+
    Now, lets have 3 materials:
    1 | Iron
    2 | Steel
    3 | Copper

    And two blueprints:
    1 | Dagger
    2 | Rusty Armor

    To create these, the dagger needs 2x iron and 4 x copper. The armor needs 4 x steel. The BlueprintMaterial table would then contain the following entries:
    1 | 1 | 2
    1 | 3 | 4
    2 | 2 | 4

    Makes sense? Now you can query these with simple joins:
    Code:
    SELECT m.name, bm.quantity
    FROM Material m
    INNER JOIN BlueprintMaterial bm ON bm.mid = m.id
    INNER JOIN Blueprint b ON b.id = bm.bid
    WHERE b.name = 'Dagger'
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 

  • Users who have thanked Fou-Lu for this post:

    Kurisvo (05-02-2011)

  • #3
    New Coder
    Join Date
    Apr 2011
    Posts
    59
    Thanks
    8
    Thanked 0 Times in 0 Posts
    Yep, that should work perfectly! Thanks so much! Still trying to plan how I will check and make sure that the user has all the items they need. It's probably something completely obvious, but it is 1 in the morning here and I shouldn't even be coding this late. LOL

    Thank you again!

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,020
    Thanks
    75
    Thanked 4,323 Times in 4,289 Posts
    Oh, that's easy.

    But let's expand FouLu's example a bit. Let's say that to build a widget you need 3 rusty bolts.
    Code:
    Material
    4 | Rusty bolt
    
    Blueprints
    3 | Widget
    
    BlueprintMaterials
    blueprintid | materialid | quantity
              3 |          4 |        3
    I added quantity to the BlueprintMaterials table because it's pretty likely that you'll end up needing that in real scenarios.

    Okay, so on top of this, you also need a table of what materials a given user has accumulated.

    Maybe:
    Code:
    UserMaterials
    userid | materialid | quantity
        17 |          4 |        2
    That is, user 17 only has 2 Rusty Bolts

    So, your query, to find out if user 17 has what is needed to build a widget goes like this:

    Code:
    SELECT M.materialname, UM.quantity - BM.quantity AS shortage
    FROM Materials AS M, UserMaterials AS UM, BlueprintMaterials AS BM, Blueprints AS B
    WHERE M.id = UM.materialid
    AND M.id = BM.materialid
    AND BM.blueprintid = B.id
    AND B.blueprintname = 'Widget'
    And that would get you a set of records that would include:
    Code:
    RustyBolts | 1
    clearly showing that the user is missing at least one item needed for a widget.

    If you only wanted to show those items that he *IS* missing (that is, don't show items where the "Shortage" is zero), just add that as a condition to the WHERE:
    Code:
    SELECT M.materialname, UM.quantity - BM.quantity AS shortage
    FROM Materials AS M, UserMaterials AS UM, BlueprintMaterials AS BM, Blueprints AS B
    WHERE M.id = UM.materialid
    AND M.id = BM.materialid
    AND BM.blueprintid = B.id
    AND B.blueprintname = 'Widget'
    AND UM.quantity - BM.quantity > 0
    
    If you do this, then if you find that the query returns NO records at all, then of course that means the user indeed has all the needed parts! Makes the coding of the web page easy.

    And of course if you already know you are looking for the Blueprint with id=3, the widget, then you can drop it from your query, thus:
    Code:
    SELECT M.materialname, UM.quantity - BM.quantity AS shortage
    FROM Materials AS M, UserMaterials AS UM, BlueprintMaterials AS BM
    WHERE M.id = UM.materialid
    AND M.id = BM.materialid
    AND BM.blueprintid = 3   
    AND UM.quantity - BM.quantity > 0
    I hope this shows how good DB design can make some of even the toughest *looking* problems actually be quite simple to query for.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    Kurisvo (05-02-2011)

  • #5
    New Coder
    Join Date
    Apr 2011
    Posts
    59
    Thanks
    8
    Thanked 0 Times in 0 Posts
    So that sorta works. It works perfectly fine when there is just one specific item needed (quantity doesn't mater). But when you get into building stuff like Fou-Lu's dagger example, it will only check one. Do I have to loop that query?

    Here is what I have:

    PHP Code:
     $check_if_sql "SELECT I.name, UI.quantity - PM.quantity AS shortage
    FROM items AS I, userInventory AS UI, plansMaterial AS PM
    WHERE I.iID = UI.iID
    AND I.iID = PM.pID
    AND PM.pID = '"
    .$get_plan_id."'
    AND UI.quantity - PM.quantity > 0
    AND UI.user_id = '"
    .$user."'
    "
    ;
        
    $check_if_res mysqli_query($mysqli$check_if_sql) or die (mysqli_error($mysqli));

    if(
    mysqli_num_rows($check_if_res) < 1){
        
    //we have everything we need
        
         
    $message "You've built your plan!";
    }else{
         
         
        
    $message "You do not have enough items to build this."



    I'm sure I'm doing something stupid. This is so embarrassing, I sound like so much more of a n00b then I am. I didn't have these problems with the rest of the website. >.>

    Thank you guys for all the help so far, it's been awesome to learn all this. ^^

  • #6
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,020
    Thanks
    75
    Thanked 4,323 Times in 4,289 Posts
    Looks right to me.

    Are you trying to also *show* the items needed???

    There's one mistake that you and, seemingly, every other PHP user loves to make.

    You keep putting apostrophes around values that are *NUMBERS*!!!

    SURELY the fields pID and user_id are integer fields??? IDs should be numbers, 95% of the time.

    So then why do you want to compare a number to the string (example) '4'?? Why not just compare it to 4??

    Code:
    $check_if_sql = "SELECT I.name, UI.quantity - PM.quantity AS shortage
    FROM items AS I, userInventory AS UI, plansMaterial AS PM
    WHERE I.iID = UI.iID
    AND I.iID = PM.pID
    AND PM.pID = ".$get_plan_id."
    AND UI.quantity - PM.quantity > 0
    AND UI.user_id = ".$user ;
    In most other DBs, doing that would get you a nasty error message about "type mismatch". MySQL is sloppy and forgiving, so you get away with it, but it's a bad habit to get into.

    Don't feel lonesome; fully 80% or more of PHP code that I see does this. Whereas I think less than 10% of code I see for other systems (JSP/ASP/etc.) do it.

    **********

    I JUST SAW IT!!! LOL! Fixing your SQL for you made me see it!!

    Look here:
    Code:
    AND I.iID = PM.pID
    AND PM.pID = ".$get_plan_id."
    OOPS! So you will only get items where the itemid matches the PLAN ID!!!

    Almost surely that line should be
    Code:
    AND I.iID = PM.iID
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #7
    New Coder
    Join Date
    Apr 2011
    Posts
    59
    Thanks
    8
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Old Pedant View Post
    Looks right to me.

    Are you trying to also *show* the items needed???
    Nope, all I want to do is make sure that they have enough. Should I has used this query then?

    Code:
    SELECT M.materialname, UM.quantity - BM.quantity AS shortage
    FROM Materials AS M, UserMaterials AS UM, BlueprintMaterials AS BM, Blueprints AS B
    WHERE M.id = UM.materialid
    AND M.id = BM.materialid
    AND BM.blueprintid = B.id
    AND B.blueprintname = 'Widget'
    Quote Originally Posted by Old Pedant View Post
    There's one mistake that you and, seemingly, every other PHP user loves to make.
    --snip--
    Thanks for letting me know that! I'll remember not to do it that way.

    Quote Originally Posted by Old Pedant View Post
    I JUST SAW IT!!! LOL! Fixing your SQL for you made me see it!!

    Look here:
    Code:
    AND I.iID = PM.pID
    AND PM.pID = ".$get_plan_id."
    OOPS! So you will only get items where the itemid matches the PLAN ID!!!

    Almost surely that line should be
    Code:
    AND I.iID = PM.iID
    kk I did that, and it's returning one row. I've triple checked the database and I have enough pets, and it is set up correctly... shouldn't it be returning 0 rows since I have everything I need?

    EDIT:

    Ooooh I see. I get that error when I have /more/ items in my inventory then I need for the blueprint. How should I plan for that? It works perfectly when I have the exact number of what I need.
    Last edited by Kurisvo; 05-01-2011 at 06:02 AM.

  • #8
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,020
    Thanks
    75
    Thanked 4,323 Times in 4,289 Posts
    I am an idiot.

    Look at this:
    Code:
        AND UI.quantity - PM.quantity > 0
    So that says "show the shortage if the UserInventory is *MORE* than the PlanMaterials required".

    It should, of course, be < 0 !!!

    It would have been clearer--and we would have caught the error sooner--if I had written:
    Code:
     
        AND PM.quantity > UI.quantity
    I'd recommend you change to that.

    "show the shortage if the PlanMaterials required quantity is GREATER THAN the UserInventory quantity." So much more sensible a way to express it.

    Sorry!
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #9
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,020
    Thanks
    75
    Thanked 4,323 Times in 4,289 Posts
    And double DOH on me! The SELECT is backasswards, as well:
    Code:
    SELECT I.name, UI.quantity - PM.quantity AS shortage
    If the UI quantity is 1000 and the PM quantity is 3, there sure as heck isn't any shortage.

    Clearly that should be the other way around:
    Code:
    SELECT I.name, PM.quantity - UI.quantity AS shortage
    So, finally:
    Code:
    $check_if_sql = "SELECT I.name, PM.quantity - UI.quantity AS shortage
    FROM items AS I, userInventory AS UI, plansMaterial AS PM
    WHERE I.iID = UI.iID
    AND I.iID = PM.pID
    AND PM.pID = ".$get_plan_id."
    AND PM.quantity > UI.quantity
    AND UI.user_id = ".$user ;
    I sure wish I had actually tested this instead of just trying to write code in this silly little <textarea> window. Sorry.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    Kurisvo (05-02-2011)

  • #10
    New Coder
    Join Date
    Apr 2011
    Posts
    59
    Thanks
    8
    Thanked 0 Times in 0 Posts
    Thank you so much! I had no idea it could be so easy this way!


  •  

    Tags for this Thread

    Posting Permissions

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