SQL HELP : Recipes and Plat

thepoetwarrior · #1 12-05-2010, 12:49 AM

As a quick fix, I'd like to make any item that is a reward from successful combine from a trade skill to be edited to 0 price to prevent any pp profits in tradeskills. This could probably be done with a mysql query cross referencing tradeskill_recipe_entries table with items table. Any experts can help?

**Akkadius** · #2 12-05-2010, 01:06 AM

Quote:

Originally Posted by thepoetwarrior

As a quick fix, I'd like to make any item that is a reward from successful combine from a trade skill to be edited to 0 price to prevent any pp profits in tradeskills. This could probably be done with a mysql query cross referencing tradeskill_recipe_entries table with items table. Any experts can help?

Code:

UPDATE `items`, `tradeskill_recipe_entries` SET `items.price` = '0' WHERE `items.id` = `tradeskill_recipe_entries.item_id` AND `tradeskill_recipe_entries.successcount` = '1';

**trevius** · #3 12-05-2010, 01:31 AM

Instead of just setting price to 0, here are some examples for setting the price to 1/10th it's current price and then setting sellrate to 10, which should mean the vendors pay 1/10th what they did before, but sell them for the same rate as before.

I haven't tested the UPDATE query below, but it is based on the SELECT query, which seems to work fine. Feel free to mess around with them to have them do what you want. Probably want to test them on a non-production database before you update your real one though.

Code:

SELECT tradeskill_recipe_entries.item_id, tradeskill_recipe_entries.successcount, items.id, items.sellrate, items.price FROM tradeskill_recipe_entries, items WHERE tradeskill_recipe_entries.successcount > 0 AND tradeskill_recipe_entries.item_id = items.id AND items.sellrate < 10;

Code:

UPDATE tradeskill_recipe_entries, items SET items.sellrate = 10, items.price = (items.price / 10) WHERE tradeskill_recipe_entries.successcount > 0 AND tradeskill_recipe_entries.item_id = items.id AND items.sellrate < 10;

**Akkadius** · #4 12-05-2010, 01:42 AM

Quote:

Originally Posted by trevius

Instead of just setting price to 0, here are some examples for setting the price to 1/10th it's current price and then setting sellrate to 10, which should mean the vendors pay 1/10th what they did before, but sell them for the same rate as before.

I haven't tested the UPDATE query below, but it is based on the SELECT query, which seems to work fine. Feel free to mess around with them to have them do what you want. Probably want to test them on a non-production database before you update your real one though.

Code:

SELECT tradeskill_recipe_entries.item_id, tradeskill_recipe_entries.successcount, items.id, items.sellrate, items.price FROM tradeskill_recipe_entries, items WHERE tradeskill_recipe_entries.successcount > 0 AND tradeskill_recipe_entries.item_id = items.id AND items.sellrate < 10;

Code:

UPDATE tradeskill_recipe_entries, items SET items.sellrate = 10, items.price = (items.price / 10) WHERE tradeskill_recipe_entries.successcount > 0 AND tradeskill_recipe_entries.item_id = items.id AND items.sellrate < 10;

That's not what he asked for lol. You could design any pricing scheme depending on what you want to do. Or even based on the triviality of the tradeskill etc.

thepoetwarrior · #5 12-05-2010, 02:56 AM

Thanks, thats the type of stuff I was looking for. Editing prices can have undesired side effects, such as a with sub combines, a success item used to create a different item, etc. Plat is so trivial on the server anyways, so I wouldn't mind making the rewards worth 0 plat.

thepoetwarrior · #6 12-05-2010, 03:08 AM

How does the sellrate value work? Is it 100 / Sell rate? So 100 / sellrate(1) = 100% value? And 100 / sellrate(10) = 10% sell back value? Just a guess, please confirm =)

thepoetwarrior · #7 12-05-2010, 03:32 AM

Worked, thank you very much!

**trevius** · #8 12-05-2010, 03:58 AM

Sellrate is a multiplier for the price of the item that the merchant will sell. So, if a merchant is selling something with a price of 10000 (10 plat), and the sellrate is 1, the cost to buy it would be about 1 plat. But, if that same item had the sellrate changed to 15, the cost to buy it would now be 10 plat X 15 = about 150 plat (give or take depending on bonuses like CHA and Faction). You can also make the sellrate lower than the price by setting it to something like 0.5, where the item would then cost about half of what it sold to the merchant for (in this case, about 5 plat). You never want to set sellrate below 1 for this reason; so you don't run into issues where things are worth more to sell than they cost to buy.

Also, sellrate only affects how much an item costs from a merchant. It does not affect how much the merchant will buy the item for. So, you can have something cost 50 times what the merchant will pay for it if you wanted (by setting sellrate to 50).

thepoetwarrior · #9 12-05-2010, 05:48 AM

Thanks, and a semi related question which I know has been some what tackled before. How can I check who's exploited plat? I have several different tools, some of which I haven't figured out how to install. The server stat 1.1c seems to work, but loads player profiles so slowly. I assume plat is in the players blob field, and I have about 80,000 characters in the database now which is a huge amount of data to pull. Any easy way I can view and sort plat locally due to bandwidth, etc?