mysql question

[CygnusX]

"Select * from UserItems join Items on (UserItems.ItemID = Items.ItemID) "

Does anything see anything wrong with this query?  I believe Items might be a reserved keyword.

For UserItems, I have:

UserID  |   ItemID   | Qty   |   isEquipped

1  |   1   |  1   |  0
1  |   2   |  0   |  0

for Items, I have

ItemID | ...... etc
1  | etc
2  |  etc

The results show ItemID 2 having a Qty of 1, which was making me pull my hair out last night.

[Chris]

Standard debug tips:
- don't use * but select fields manually
- put echo mysql_error(); after each query
- use capital letters for SQL reserved stuff, it will make your code more readable: "SELECT * FROM UserItems JOIN Items ON (UserItems.ItemID = Items.ItemID)"

Also you violated a database design rule (dunno how that rule was exactly called ) UserItems does not have primary key (or you used ItemID as primary key in which case you messed it up totally since it is a foreign key). Try giving the person 2 identical swords and next try to delete 1 sword, you will see what I mean

[saljutin]

mysql_query("YOUR QUERY") or die(mysql_error());
i think this is better then echo

so why dont use * but select fields manually...what if he wants to select everything?

[CygnusX]

UserItem.Qty can be 0 to int

So... if I have 2 swords, I increase qty to 2, not add another row to the table.  I fail to see how this is a db design failure.

Also, I have an abstraction layer that adds the or die(mysql_error()) to the end.

This is just a query.  It is not returning the expected results.  Any help on what could be the problem, or are we critiquing my coding style?

[Chris]

These were standard debug tips, not coding tips (I'm not insane enough to check mysql_error after each query or remove all * in production code) In case of trouble I follow these and it sometimes helps.

I believe Items might be a reserved keyword.

If you did mysql_error() already then why you ask about this? If this was a reserved keyword it would generate an error due to invalid syntax...

So... if I have 2 swords, I increase qty to 2, not add another row to the table.  I fail to see how this is a db design failure.

I know, some do not believe in the "unique ID for each row" rule, but I do (which is rare since I tend to break as many rules as possible ). Anyway, I have a feeling you will pay for this later (like when you decide to make item enchantments), not that I wish you pay for this, not at all

Any help on what could be the problem, or are we critiquing my coding style?

a) well, if you have trouble with rather simple join it means it has something to do with coding style or some bigger mistake upon database schema design step
b) yes, critiquing someone's coding style is much more fun than providing a simple answer and you should know it by now

[CygnusX]

Yeah, my system will get messy with enchantments.  I guess i need to drop Qty and add an InventoryID instead.

[Chris]

Don't drop it. Make only non equipment stackable. Works perfect (I did it that way in both Samurai and Moonstone).