[Zengez]

Ok, so you got your merchant up and want to now set new prices for the items you've put on him... or maybe you want items he sells to sell back for roughly the same price he sells them for... or maybe you just want to totally revamp your item DB's cost/sellback values all together.... here's how!

To understand how to do this i'll write a short bit on the conceptual bit (such as it is) to make it clear to even the least code-experienced of us...

Under the "Items" database (the data that contains all the info for all the items of your world) there are a series of collums, each having a value asigned for every item; thus every item has a 'NODROP' collum and every item has 'Damage' collum, even if it's a ration that is both dropable and has no damage value to it.

The way the information is then gathered, is it takes the value from these collumns to judge whether or not that item has relevant parts to draw from... thus if an item is NODROP, it's NODROP collumn will be set to represent as such (255 i believe?), and likewise if it is not, it will be flagged with a different number which says as such (1 is not NODROP) So if you look up the NODROP collumn on a "ration" you will recieve "1" as the answer... because it is not nodrop. Likewise, in the case of damage, the effective damage of the "ration" is 0, because it has no damage.

Ok, now, assuming you understand that, we can now manipulate the database as we see fit. I will give two types of change, one for making a certain item different in cost, and the other to manipulate the entire DB according to some formula.


Ok, first off, if you wish to make a certain item sell for a certain value, you must look up said item to either get the proper full name, or even better the item-id number (some items, such as bone chips, have the same name as a dozen other items, but if you want only the non-lore bone chips, you'd need to use the item-id number.)

Let's assume you're going to use the item "Blighted Robe" (Item-ID 1233 in my DB) and you just put it on your customized merchant and were discouraged when it sold for 2 copper.... so now you want to change the item cost. Well, theres two ways to do this, either set it a flat rate you think is accurate, or devise some type of formula in order to assign it a value based on it's characteristics of your choosing.

So, if you wished to set it a flat rate, it's a simple matter of 'updating' the cost collumn of the item; as followes

UPDATE items SET cost = (item cost in copper) Where id = 1233;

This ensures that the item you just looked up by it's ID (1233, Blighted Robe) is set to the cost you typed in, since it will be the only one to match the item id where clause.... Basically this line says "Find all items where the item id is 1233, and replace the cost value with this number." Since item id numbers are unique (in theory) you will only replace the intended item's cost. It is important to note that this cost is in copper... so changing it to '130' won't mean it's 130 plat, but rather 1 gold and 3 silver (130 copper)

Ok, now lets say you want to update it using a formula... the first step is to devise the formula you wish to create by using the relevant statistics you wish to base it upon.

The easy way to do this is to go to your mysql cmd line and type "Pager less;" (i believe thats correct, might be "Set Pager Less;") and then type

Select * from items;

This will take a minute because you're basically asking it to give you everything in the item table, but if the pager is set to less, it will allow you to scroll through the database, and in this case, allow you to look at the name of every collumn.

Using this you can see the names of every collumn at the begining, then using simple logic and a basic comprehension of everquest you can pull out fields that matter to you, once you've done that, it's time to build the equation!

To build the equation first decide what things you wish to be valued at what level... i suggest doing everything in terms of platinum so you can multiply by a constant.

Let's say you decide all stat points (aagi, acha, asta, astr, adex, awis, aint) are going to be 1 plat each, and ac is 2 plat per ac, 1 plat per resist, and .1 plat per hp or mana, now you need to put it together using the collumn names to yield an equation... you should come out to;

((aagi+acha+asta+astr+adex+awis+aint)+(cr+dr+fr+pr +mr)+(2*ac)+(.1*(hp+mana)))*1000;

I added in extra ()'s to make it clear... at first we add together all the stat points (each multiplied by the 1 plat, which makes it just itself) then we add each of the resist points (also 1 each). After that we add together 2 times the ac of the item, followed by .1 times the mana plus the hp, giving us our desired amount... Multiply this all by 1000 (to change our copper to plat... i.e. if we cranked that equation through and got 5plat as a desired result, we'd actually end up with 5 copper if we didn't multiply by 1000)

Alright, now we have our equation Cheer!

Now it's a simple matter of updating the cost like so;

UPDATE items SET cost = (equation) where id = (item id);

So in our case it would look like

UPDATE items SET cost = ((aagi+acha+asta+astr+adex+awis+aint)+(cr+dr+fr+pr +mr)+(2*ac)+(.1*(hp+mana)))*1000 where id = 1233;

And your done.

Now, if you want to make this change globally (i.e. to your entire DB) there is only 2 things you need to change.

First, you take out the where clause, and replace it with whatever restrictions you may want to implore.... so if you want one formula for weapons, a different one for armor, and a third for bags, use Where bagtype = 1 to specify for bags, where damage > 0 and/or delay >0 to specify weapons, and where damage =0 and/or delay = 0 and bagtype =0 to specify armor

If you wish to string together where clauses to form a restricted domain, such as in the case of specifying armor, use caps to specify... once again not sure if this matters or not but redundancy helps

So, in the case of armor;

... Where damage = 0 AND delay =0 AND bagtype=0;

Finally, the other thing you want to change, is to add in a constant into your equation. This will prevent items that are accidently caught in your equation's where clause from being turned to 0 because they have none of the stats you used to assign a value to... Thus if you used the above equation and applied it globally, things like vellium bars would be free since they have no stats, resist, ac, or hp/mana.

Thus an equation should have +(some constant), take the above equation and manipulate it to give a base value of 1, would yield;

UPDATE items SET cost = (1+((aagi+acha+asta+astr+adex+awis+aint)+(cr+dr+fr +pr+mr)+(2*ac)+(.1*(hp+mana)))*1000) Where Delay>0 AND damage>0;

That makes all weapons (items with damage greater than 0 and delay greater than 0) apply to that equation for cost, and any weapon without stats/resists/hp/mana/ac would have a cost of 1plat (FYI this equation would be poor due to it's total negation of taking into account what dmg/del the weapon actually has... see next post for helpful hints)

Ok, so you have your equation, you have an added constant to make sure items aren't free, and you know how the where clause works... you're all set to rip through your DB and change costs at will... PLEASE DEAR GOD AND FOR THE LORD JEBUS REMEMBER TO BACKUP YOUR DATABASE !!! if you manage to code some great stuff, then screw something up and frag yer db, you'll have to resource the item file from scratch and start all over, and that's a huge headache... make sure to backup your DB after every SUCCESSFUL major code change... and prefferably before every major codeing attempt... but make sure to play-test it, don't just assume it works... bugs happen and mistakes are made.

As a final note, i said i would include how to customize sellback value... it is simply the exact same as the above using the collumn 'merchantprice' instead of cost... thus you could write

UPDATE items SET merchantprice = (1+((aagi+acha+asta+astr+adex+awis+aint)+(cr+dr+fr +pr+mr)+(2*ac)+(.1*(hp+mana)))*1000) Where Delay>0 AND damage>0;

And sell values would be set accordingly... However, since most servers would like for items to be sold at some sort of fraction of the item's cost value... simply do exactly that, and use (for example)

UPDATE items SET merchantprice = cost;

If you want it to be some fraction of the cost, simply put the fraction multiplied against the cost... for the example of .5

UPDATE items SET merchantprice = .5*cost;

Ok all, it's midnight and i gotta get up for class in 6 hours, so i'm gonna stop it here... will check in tomorrow to see if i can add anymore or reply to posts that may need some help... Should get in my helpful hints/FAQ/Common debugging section in tomorrow i hope...

Hope this helps!