question about linking tables

[I like pie]

Here's the situation:

I have a game, and in this game each player has the oppurtunity to acquire several weapons.

There is a player table, and a weapons table (there's much more, but these are the two that matter right now). How do I go about setting and referencing values for each player? I've been told that linking tables is what i want to do, but i've been googling and i've yet to find out what it is that i need to do, or even where to start.

If I had say... 3 "swords of something or other", how would i store that in the db?

[Paul M]

Can a player have more than one weapon at a time ?

[I like pie]

they can have any number of any weapon in their weapons inventory, but they can only have one weapon equipped at any given time

i've since found the solution btw, had to have a friend explain a few things to me. thanks though, if i have more questions i'll come ask again.

[I like pie]

and i'm back again already... i don't mean to ask for others to write my code but i've been reading about table joins for about an hour or two and i'm just getting more confused

ok...

so i have a players table, let's say for now that a row looks like this:

id, name, cash, equipped, str

now there's a weapons table:

id, name, str, description

so... when a player equips let's say... a staff, it would update his equipped field to "staff" and his str field would be the sum of his own str value plus the value in the staff's str field

the only way i can think to do it is to get the weapon str in one query, and assign the value to a variable, and then use that in another query. but that seems really sloppy

i'm entirely lost as to how this query should look, any ideas?

[WorldwideTrading]

Originally Posted by I like pie

and i'm back again already... i don't mean to ask for others to write my code but i've been reading about table joins for about an hour or two and i'm just getting more confused

ok...
so i have a players table, let's say for now that a row looks like this:
id, name, cash, equipped, str

now there's a weapons table:
id, name, str, description

so... when a player equips let's say... a staff, it would update his equipped field to "staff" and his str field would be the sum of his own str value plus the value in the staff's str field

the only way i can think to do it is to get the weapon str in one query, and assign the value to a variable, and then use that in another query. but that seems really sloppy

i'm entirely lost as to how this query should look, any ideas?

thats simple. Personally I dont use joins. You can do this using basic sql.

select p.id, p.name, p.cash, p.str, w.id as w_id, w.name as w_name, w.description as w_description
from players p, weapons w
where p.equipped = w.id

You will then get these fields:
id, name, cash, str, w_id, w_name, w_description
Returned from a single query.

[I like pie]

so it's necessary to select some type of data in its own query before updating?

in other words... i couldn't use these values as reference and update them in a single update query?

something like... update players set players.str = (players.str + weapons.str) where player.id = x and weapons.id = y. i know that looks horrible but i can't explain it too well in regular english

[I like pie]

well i found the solution:

update users set users.str = users.str + (select weapons.str from weapons where weapons.id = $weaponid) where id = $id;

subselects, i had no idea