...

View Full Version : Virtual "building" in PHP and MySQL. How to plan?



Kurisvo
04-30-2011, 03:26 AM
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?

Fou-Lu
04-30-2011, 04:59 AM
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.


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


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'

Kurisvo
04-30-2011, 06:13 AM
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!

Old Pedant
04-30-2011, 07:44 AM
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.


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:


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:



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:


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:


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:


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.

Kurisvo
05-01-2011, 04:32 AM
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:


$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. ^^

Old Pedant
05-01-2011, 04:53 AM
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??



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

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

AND I.iID = PM.iID

Kurisvo
05-01-2011, 05:58 AM
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?


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'



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. :D



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

Look here:

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

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.

Old Pedant
05-01-2011, 08:04 PM
I am an idiot.

Look at this:


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:


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!

Old Pedant
05-01-2011, 08:09 PM
And double DOH on me! The SELECT is backasswards, as well:


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:


SELECT I.name, PM.quantity - UI.quantity AS shortage

So, finally:


$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.

Kurisvo
05-02-2011, 03:25 AM
Thank you so much! I had no idea it could be so easy this way!



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum