Ok, I'm making an online game, where users can have several weapons of one type. They need to be able to equip them obviously. My problem is that I have no way of disdinguishing between two weapons that are the same....

I have created a link_id column in my database, so that every weapon has a seperate id number! However, my new problem is that if a user has 2 of one weapon, then I need to use the link_id column so that when they equip the weapon, that it is the correct weapon, the one with that link_id number

How would I do this ???


Some background info:

My weapons table (called user_weapons) has 4 columns.

1) id

Id is the players unique number, so that the user is shown their own weapons

2) weapon_id

weapon_id tells me which weapon it is. 1 might be stick, 2 might be a super-mega-hyper radiation emmiter, 3 might be my aunty bernice etc.

3) status

status is either 0 or 1, where 0 is unequiped and 1 is equiped

4) link_id

A number that distinguishes between all weapons, useful for similar weapons. Unfortunatly, I have no idea how to use it


Those are my columns

Can anyone help, or do you need more iunformation?

You don't need to specifically record the equipped weapon in the users database. If you have unique IDs for users, and unique IDs for individual weapons, and the id column in the user_weapons table records who owns which weapon, then you have all the information you need. It is the goal of relational database systems such as mySQL to reduce the amount of duplicate information you store since it is easier to maintain and harder to corrupt (eg if two duplicate records get changed to be different).

You just need to think about the problem slightly differently. Instead of building a complex database structure, stick with the simple one and use the power of SQL to query the tables for what you want to know. For this you will need something called a join - this allows you to get info from two tables at once as is the case here.
Something like

SELECT * FROM users u, users_weapons w WHERE u.id=$currentuser AND u.id = w.id AND w.status = 1;

would get you the currently equipped weapon for the user id in $currentuser.

The join is hidden in this case - it's the comma between users u and user_weapons w. It basically pairs up all the rows in one table with all the rows in the other, in every possible combination. The conditions you add such as u.id = w.id mean that only combinations we are interested in (where the user actually owns the weapon) are returned in the result set.

That's a lot to take in all at once probably - let us know if you have further questions.

How does the u.id and w.id work?

And how does this help in how I can equip a certain weapon?

I'm not trying to be rude, but I don't understand....

In that query I wrote above, I assigned some short names to the two tables:
SELECT * FROM users u, users_weapons w WHERE u.id=$currentuser AND u.id = w.id AND w.status = 1;

The join gives us every combination, including those where the user doesn't own the weapon. The u.id = w.id part tells mySQL that we only want the rows where the user DOES own the weapon.

The way this works out for your script is that you can equip weapons just by changing the status flags, then when you want to find out who has what equipped later on, you can run the above query or something similar.

The user will need to see the weapon, and be able to change it themselves aswell as their unused weapon. This'll do that?

Also, the code isn't working for some reason:

$query_3 = "SELECT * FROM login u, user_weapons w WHERE u.id=$currentuser AND u.id = w.id AND w.status = 1";
mysql_query ($query_3) or die ("Error");
echo "$currentuser";

That should work right? What've I done wrong?

As long as you have the link_id for the weapon the user has, you can write code that allows the user to modify their weapons.

The query results above are returned from the mysql_query function, and as it is, you are not assigning the query result to anything - so the query results get thrown away. $currentuser is just a variable that holds the user_id of the current user, and is used here only to tell mySQL which user we are querying for (since all the users weapon info lives in the same table). To get the above example to work, you'll need to stick the query result in a variable and then get the info you want from there:

PHP Code:

$query_3 = "SELECT * FROM login u, user_weapons w WHERE u.id=$currentuser AND u.id = w.id AND w.status = 1";
//Assign the query result to $result
$result = mysql_query ($query_3) or die ("Error");

//Get the info from $result (you can't echo $result directly as it is a piece of a db table, not a string)
while($row = mysql_fetch_array($result)) {
  echo $row['id'];
  echo $row['status'];
  echo $row['link_id'];
} 


I know that might be a bit confusing, but any decent book or tutorial on mySQL ought to cover this stuff.

Ok, thanks 0beron.

I'm still receiving errors from that, but I've got my own script for displaying weapons:

PHP Code:

if ($status == 1)
                {
                echo "You have a $weapon_name equiped";
    $weapon_query = "SELECT * FROM user_weapons WHERE id='$id'" or die ("Problem with selecting from database");
$result = mysql_query($weapon_query) or die ("Couldn't execute query 2");
while ($row = mysql_fetch_array($result))
{
extract ($row);
    if ($status==0){
                    echo "Weapon: $weapon_name";
                    }
} 


However, how can I make this so that I can un-equip and equip weapons properly?

You'll need to run the query 'UPDATE user_weapons SET status = 0 WHERE $link_id = $theweaponyouwanttounequip' at some point (and another the same with status = 1 for the one you want to equip). You'll need to set up some forms/links for the user to use that trigger these things to happen. I'd recommend getting a decent PHP/MySQL book, or hunting around for some tutorials. Most mySQL tutorials, such as this one:
http://www.freewebmasterhelp.com/tutorials/phpmysql/1
Cover insertion, deletion, updates etc, and some basic html forms to get those things to happen.