Trouble with displaying equipped items

[dkin]

I am running a small game on my website where I let users outfit characters with different gear, now what I am trying to do is this.

I have a database called "purchase". In this database is all the items that all characters have purchased, now, they can go into their inventory and equip any item they choose. But what I would like to do is look at the database when they click the button and insert a "1" into the row that was equipped under the column "equipped" but if there is another item which is the same "item_type" and is equipped I would like to insert a 0 under "equipped" in that row.

If I lost you I apologize my head is not with me today but please try to decipher my babbling, I cannot do much until this is figured out.

Thank you

[Salsa]

If I understand correctly, it sounds like you are wanting to do an update of an existing item when a user chooses to add a new item of the same type. To do an UPDATE and INSERT at the same time, I think is going to require two queries, something like:

UPDATE purchase SET equipped=0 WHERE user='$user' AND item_type='$item_type'; 
INSERT INTO purchase (user, item_type, equipped) VALUES('$user', '$item_type', 1);

I hope this helps.

[dkin]

##Update the database
$purchasedresult = mysql_query("UPDATE purchased set equipped = '1' where $purrow[username] = '$username' and $purrow[item_name] = '$item'", $link);

$purresult = mysql_query("SELECT * FROM purchased where item_name = '$item' and username = '$username'", $link) or die ("query 1: " . mysql_error());
$purrow = mysql_fetch_array($purresult) or die ("query 2: " . mysql_error());

if ($purchasedresult == "TRUE")
{
echo 'EQUIPPED';
}
else
{
echo 'NOT EQUIPPED<br>'.$purrow[item_name].'<br>'.$item.'<br>'.$purrow[username].'<br>'.$username;
}

Thats my code, I the output I am getting is

NOT EQUIPPED
item
item
user
user

the 2 items match and the usernames match, this is why I am baffled.

[Salsa]

It looks to me like you're making things overly complicated, using array members to name your columns and all--at least for testing. Given your output, the $purchasedresult query is failing altogether. Why aren't you calling mysql_error() on that result? And even when that is fixed, $purchasedresult will be TRUE any time the query doesn't fail completely. Simply confirming that it's TRUE won't mean that has updated any records. I understand that in many cases in production no rows will need to be updated, but for the time being, maybe echo out mysql_affected_rows($purchasedresult) instead, to see how many rows have been updated. Also, at the top of your script, try setting error reporting to error_reporting(E_ALL). That might show you a little more about what's going on.

I hope this helps.

[dkin]

ok I have turned error reporting on and I have many many errors.

now I am curious.

I am defining my database cells like this

$prow[item_name]

and this is the error I get for it.

Notice: Use of undefined constant item_name - assumed 'item_name' in /home/eqoagui/public_html/game/purchased.php on line 121

How can I output the database without getting these errors?

[jatar_k]

the proper syntax for

$prow[item_name]

is

$prow['item_name']

[dkin]

also there are 2 forms thats I use on this page, named equip and resell.

and I have if statements like this.

if ($equip)
{}
else
{}

and I am getting this error

Notice: Undefined variable: equip in /home/eqoagui/public_html/game/purchased.php on line 49

how can I get rid of that?

[dkin]

I believe Salsa was on the right track but I think I may have explained it a little wrong.

If a user has a weapon equipped I want the weapon to have a 1 under the equipped column. Now if he buys another weapon and equips it I want to insert a 1 under the equipped column of the new weapon and change the old weapons equipped column back to 0.

Does this make sense?

[dkin]

please does anyone know how I can accomplish this?

[jatar_k]

Salsa was not only on the right track but he has the full answer

this will set the okd item to 0
UPDATE purchase SET equipped=0 WHERE user='$user' AND item_type='$item_type';

this inserts the new row for the new item
INSERT INTO purchase (user, item_type, equipped) VALUES('$user', '$item_type', 1);

if the item already exists for that user then you just need to use an update query like the first except setting the new item to 1.

your notice is because of the test if ($equip). I know this is common to use for variable existence but it is a pet peeve of mine. If you want to test if a variable exists then use the function.

if (isset($equip))

[Salsa]

Thanks Jatar. There is one modification I'd make, however. It was my original understanding that Dkin didn't want to allow two weapons of the same type to be equipped. However, if only one weapon of any kind may be equipped, then simply exclude the item_type condition from the WHERE clause, like:

UPDATE purchase SET equipped=0 WHERE user='$user';

The INSERT query would remain the same, and of course, do the UPDATE before the INSERT.

I hope this helps.