Simple database updates

[dkin]

I am trying to perform a few updates when a button is clicked on one of my pages. If a row with the same "type" is "equipped" I would like it unequipped and the new item equipped, if nothing with the same "type" is equipped just equip the new weapon.

This is my code

##Update the database
$eresult = mysql_query("UPDATE user_char set $item_type = '$item' where username = '$username'", $link);

$eresult = mysql_query("SELECT * FROM purchased where username = '$username' AND item_type = '$item_type'", $link) or die ("query 1: " . mysql_error());
$erow = mysql_fetch_array($eresult) or die ("query 1: " . mysql_error());

##Unequip Item
$unequip = mysql_query("UPDATE purchased SET equipped = '0' WHERE username ='$username' AND item_name = '$erow[item_name]'", $link);

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

My variables are $item_type and $item.

Please help.

[dkin]

please someone help me with this, I have 4000 members waiting on it.

[ironik]

We might need a little more information. Can you post something describing your database structure and maybe a little background on your script.

I'm assuming you have some sort of online RPG and if you've got multiple tables containing users, inventories, items etc then it's going to be a little difficult to point you in the right direction without the information.

[ramoneguru]

$eresult = mysql_query("UPDATE user_char set $item_type = '$item' where username = '$username'", $link);

should be this

$eresult = mysql_query("UPDATE user_char set item_type = '$item' where username = '$username'", $link);

I assume item_type is the column nmae of user_char...also try using SET instead of lowercase....I find it more easy to see, but that's me..
--Nick

[ironik]

My variables are $item_type and $item.

Looks like the use of $item_type as a variable is intentional ;D

[dkin]

$Item type is supposed to be there, this is where I am having the problem

$eresult = mysql_query("SELECT * FROM purchased where username = '$username' AND item_type = '$item_type'", $link) or die ("query 1: " . mysql_error());
$erow = mysql_fetch_array($eresult) or die ("query 1: " . mysql_error());

##Unequip Item
$unequip = mysql_query("UPDATE purchased SET equipped = '0' WHERE username ='$username' AND item_name = '$erow[item_name]'", $link);

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

So say I am in my inventory and I have the blunt sword equipped, and I want to equip the sharp sword.

The blunt sword row would look like this

Item Type Name Equipped
weapon blunt sword 1

and the sharp sword would look like this

weapon sharp sword 0

All I want to do is change the "1" to a "0" and the "0" to a "1"

It doesnt look hard but Ive been stumped for 3 days.

I hope this helps.

[ajs83]

Is only one item equipped at a time?

[dkin]

one item of the same type, yes.

[dkin]

anyone?

[ajs83]

Have you checked to see what works in the query and what doesn't?

What I usually do when stuck like this is start with the basic query and then add more to it after you make sure each part works, printing the results each time. When it stops working, you have found your problem.

[ironik]

This query appears to be a problem. I've changed the syntax to something that will properly use the value from the $erow array.

$unequip = mysql_query("UPDATE purchased SET equipped = '0' WHERE username ='$username' AND item_name = '" . $erow['item_name'] . "'", $link);

Just looking how it is set out, the code is a little hard to handle because the database is not normalised and it appears you'll have a fair bit of data duplication (your database will get larger than it should).

Do you have this RPG online? I'd love to check it out (send me a sticky as per board TOS though).