item db

[rollain]

is there a cross reference for the class, and race fields in the item db ?

i can see that class 1 is war, 2 cleric, ect..
all is 32767, but how can i write a sql query that shows all warrior armor, including armor that is pali, and shd wearable ?



thanks

[Edgar1898]

you will have to play around with the bits of the binary number. IE to represent that a class is able to equip an item SOE uses a 1, otherwise its a 0. I cant remember which order the classes are in, but say every class can wear a bandana except the necro class. The binary value would be 111111111111011. The 0 in that value would have been where the bit for necros is. Note this is just a representation for explaining how this works. But anyways getting back to your question:

1. Convert the decimal number to binary
2. Find which bit corresponds to which class.
3. Write a small if statement that checks whether the value of the bit for the class your looking for is a 0 or a 1.

pretty easy once you know how it works....hopefully I didnt confuse too many ppls

I had to do this when I was writing the code for the search tool, its a pain but pretty easy.

[rollain]

what i am talking about is the item db sql_dump from sourceforge.

i am trying to set up the database on my guild web page.

i can see in the classes fiels the class numbers assigned.

i can tell that warrior only armor / weapons are 1.
cleric 2, and so on through 9.

i can figure that a weapon, or armor with a class # of 32767 is for all classes.

its the multi class items that im having problems with. they are allready assigned numbers. i was just wondering if there was a relational db available that would point to all class numbers that included what ever class i was trying to pull out.
for example:

water sprinkler of nem ankh, cleric only. in the db, classes field numberis 2.

dwarven chain boots is wearable by cleric, war, pal, ect... classes field number is 927.

[Bori]

Use the following formula to determine class:

Assumptions: (not the same as database, making it up on the fly)

1 = Warrior
2 = Rogue
4 = Enchanter
8 = Wizard
16 = ...
32 = ....
(notice the binary increments?)


******************************************
Algorithm: (for the assumed classes, not all of them)

IF TEMP - 8 >= 0
UsableClass = "Wiz"
TEMP = TEMP - 8

ELSEIF TEMP - 4 >= 0
UsableClass = UsableClass & ", " & "Enc"
TEMP = TEMP - 4

ELSEIF TEMP - 2 >= 0
UsableClass = UsableClass & ", " & "Rog"
TEMP = TEMP - 2

ELSEIF TEMP - 1 >= 0
UsableClass = UsableClass & ", " & "War"
TEMP = TEMP - 1


************************************

Let's say the number in the database is 6 so we'll store it as TEMP. We also need a string to hold classes (for this example. You can store it anyway you choose)

So,
TEMP = 6
UsableClass = ""


In the first IF we'll have: 6 - 8 which is -2. This does not satisfy
the statement, so we know Wizard is NOT a usable class.
After Statement:
TEMP = 6
UsableClass = ""

In the second IF we'll have: 6 - 4 which is 2. This does satisfy
the statement, so we know Enchanter IS a usable class.
After Statement:
TEMP = 2
UsableClass = "Enc"

In the third IF we'll have: 2 - 2 which is 0. This does satisfy
the statement, so we know Rogue IS a usable class.
After Statement:
TEMP = 0
UsableClass = "Enc, Rog"

In the fourth IF we'll have: 0 - 1 which is -1. This does not satisfy
the statement, so we know Warrior is NOT a usable class.
After Statement:
TEMP = 0
UsableClass = "Enc, Rog"

(at this point, TEMP will be zero)



So, from this example, the number 6 represents the Enchanter and Rogue class. As you stated, the number 32767 is ALL classes because
1 + 2 + 4 + 8 + 16 + 32 +
64 + 128 + 256 + 512 +
1024 + 2048 + 4096 + 8192 + 16384
= 32767

SOooooo, each class's number is included in the TEMP (32767).


I hope this helps, it's really entertaining to work with such things.

[rollain]

ok. i see what you mean.

classes id 8352.
enchanter is 8192, so enc usable.
bard is 128, so bard usable
druid is 32, so druid usable.


but i still dont see how to write the sql query.

where warrior is classes id 1,

and i want all warrior usable items

[Bori]

urg, after reveiwing my algorithm..

the ELSEIF statements should be IF statements. ELSE would not work properly.



And, as far as SQL statements go. I'm not sure off the top of my head. I'll think about it though.

[Merth]

Hmm, I don't believe that algorithm will work. Go with the post above by LethalEncounter. The field is a bitfield, and you can use bitmask operators to determine the classes that can use it.

I don't feel like writing a novel to explain bitfield operators, so hopefully sample code will do. Bitfield operators differ from language to language, but here they are in c++:

// Class number from classes.h
#define ROGUE        9

// Create a number with only the rogue bit turned on
uint32 rogue_mask = (1 << (ROGUE-1));

// Apply mask to 'classes' field to see if rogue bit is turned on
uint32 val = (rogue_mask & MyClassesValue);

if (val == rogue_mask)
   // .. item can be used by rogues

[Lurker_005]

Hmm I just looked at the latest item dump, and it stores races and classes in decimal format like bori mentioned.

I don't know of (or if there is a way) to directly query for the items usable by a given class or race... Well I guess you could look for all possible combinations but that isn't very pratical. The easiest thing of the top of my head would be to modify the table, or make an additional table to hold the bit fields as seperate values.

So make a seperate table a matching ID field and populate it with the class and race data. Make a script to do that of course

ID,war,nec,enc,wiz...hum,trl,ogr...
1001,1,1,1,1...1,0,0...

Then just query aginst both tables linked on the ID field


Biggest downside I see is having to update your new table for any new items.

[Edgar1898]

no, there is no reason to do all that stuff when you could just read my first post.

...fine I'll write the code for you.....

this is generic code based on php, you might have to make a couple of changes for it to work properly
$warrior=1;
$cleric=2;
...
$class=$cleric; (whatever class your looking for based on your page or whatever)
$result=mysql_query("select *from items where mid(reverse(bin(classes)),".$class.",1)=1");
while ($row = mysql_fetch_array($result)){
....
Whatever code you want here
....
}

note: I dont have access to a mysql server atm so I cant test it properly, but this should work just fine. If you have any problems let me know.

[ksmith]

Don't make things more complicated than they need to be. MySQL lets you use '&' in WHERE statements.

SELECT id,name FROM items WHERE classes & 1 AND itemtype = 10;

To save you time, here is a list of the classes and bitmasks:

WAR 1
CLR 2
PAL 4
RNG 8
SHD 16
DRU 32
MNK 64
BRD 128
ROG 256
SHM 512
NEC 1024
WIZ 2048
MAG 4096
ENC 8192
BST 16384
ALL 32767

[Edgar1898]

How would that allow you to determine whether a class can use a weapon if more than 1 class can use it at a time?

[kai_shadowbane]

Quite simply, it takes the flag #, lets say 42 (for example)
then we subtract the highest fitting number, 32
flag 32 = whatever class can use it
10 is left
next would be 8
flag 8 = class # 2 that could use it
2 is left
next (and last of this example) is 2
flag 2 = class 2 could use it
so
flag classes 32, 8, and 2 could use an item flagged with the code of 42

of course in the most recent above mentioned example, he would need to add in the code for subtracting the #s ouf of the flag total.

[a_Guest03]

Trust me; Lethal's way is the best. I've done only minimal programming, and don't know jack about sockets, modules, OS-specific stuff, etc. But when it comes to adding binary values like 10000000 and 00010000 and 00000100 to get 10010100, instead of 1 + 4 + 16 + 64 + 256 + 1012 to get some number I don't feel like calculating, just do it like the man says.

Bit flags are WAY easier than math.

Examples:

11111111 is everything.
11101111 excludes something.
10000000 is only one class.

It's REALLY easy. When you convert these back to decimal, it does the adding of 1, 2, 4, 8, 16, 32, 64, 128. In this case, mine only add up to 255, but you get my drift. Add more binary and you get bigger numbers.

[Edgar1898]

Why use all that when you could just use my example above?

[ksmith]

Originally Posted by Edgar1898

How would that allow you to determine whether a class can use a weapon if more than 1 class can use it at a time?

If that wasn't directed at my example, ignore this.

mysql> SELECT id,classes,name FROM items WHERE classes & 128 LIMIT 10;
+-------+---------+----------------------------+
| id    | classes | name                       |
+-------+---------+----------------------------+
| 35008 |     128 | Intricate Wooden Figurine  |
| 10154 |     128 | Unicorn Horn               |
| 10176 |     128 | Lute of the Gypsy Princess |
| 10219 |   15488 | Rokyls Channelling Crystal |
| 10653 |   16861 | Black Basalt Band          |
|  1094 |   16777 | Dirk of the Traitor        |
| 10945 |   16861 | Ring of Algae              |
| 10946 |   16861 | Ring of Nobility           |
| 11015 |     128 | Tear Shaped Ring           |
| 11103 |     151 | Bracers of Earthen Energy  |
+-------+---------+----------------------------+