[AndMetal]

Originally Posted by Dispair2

What happens on the back end is that when you click search it goes through and if a value is present then it adds it to the SQL statement. So above is not always how it looks as it is dynamic and when you type in the id field i believe it disables the rest of the box's. I am not at the actual dev machine now so i can not say definatively.

That's how I ended up doing it on mine, otherwise you run into problems.

On a side note, if you already know the item ID (it is a unique value, no 2 items have the same item id), the other fields don't matter. MySQL ignores the rest because they are redundant. However, you don't usually want to make exceptions in your code if at all possible (HUGE PITA to change later down the line), and since it doesn't usually cause problems to leave it in, you should be fine.

I am a little curious about the Select statement:

Select id, name, ac, haste, hp, reqlevel from items where reqlevel >= 1 and id = 25462 and classes like %5% and price >= 1 and name = 'itemname' and itemtype = 'something'

You mentioned that it's buggy but functional. There are a few things to be aware of (unless you already are) about some of the data types:

First of all, classes requires a bitwise operation because of how it is stored in the items table, because multiple classes can use it, and adding a column for each class can be very hard to read. When I went through discovering the information initially, I put my findings in the Wiki:

classes

* The class or classes can use the item. Remember to add up the results.
o 0 = None
o 1 = Warrior
o 2 = Cleric
o 4 = Paladin
o 8 = Ranger
o 16 = Shadow Knight
o 32 = Druid
o 64 = Monk
o 128 = Bard
o 256 = Rogue
o 512 = Shaman
o 1024 = Necromancer
o 2048 = Wizard
o 4096 = Magician
o 8192 = Enchanter
o 16384 = Beastlord
o 32768 = Berserker
o 65535 = Any/All
* IE, Monk (64) + Shaman (512) = 576

So, instead of using LIKE, you should use & (bitwise AND).

Secondly, name should only be = if you know exactly what the name is, and if you're searching for an item, you typically don't. That is why you normally want to use LIKE. I usually put a little spin on it, because running name LIKE '%Cloth%' can be pretty resource intensive (it searches all of the item names in the database to see if they contain the string "Cloth"). However, if you include an option to search using it as either the beginning or end of the name, it speeds up the results substantially (so name LIKE 'Cloth%', name LIKE '%Cap', or name LIKE 'Cloth Cap'). I usually do the same for the ID field, in case I don't know the exact item #.

In conclusion, this is how I would recommend to change the initial query:

SELECT id, name, ac, haste, hp, reqlevel FROM items WHERE reqlevel >= '1' AND id = '25462' AND classes & '5' AND price >= '1' AND name LIKE '%itemname%' AND itemtype = '0'