merchant idea if possible?

Trackye · #10 11-08-2012, 03:26 PM

Sorry I cannot seem to Edit my post above so i did not have to double post....

Well i figured some stuff out and this works well enough for me until I get some more work done on it.

At the moment What is required is to create 3 Npcs with MerchantIds of 22, 23, 24 ( You can change this in the SQL if you want to)

Then you dump your items table to a SQL and rename it Bazlist. ( Can also be changed in the SQL if you want to )

Then you run this SQL

Code:

Delete from Merchantlist where merchantid =22;
Delete from Merchantlist where merchantid =23;
Delete from Merchantlist where merchantid =24; 
INSERT into Merchantlist(merchantid, slot, item, faction_required, level_required, alt_currency_cost) VALUES (22, 0,(SELECT id FROM bazlist ORDER BY RAND() LIMIT 1), -100, 0, 0);
INSERT into Merchantlist(merchantid, slot, item, faction_required, level_required, alt_currency_cost) VALUES (22, 1,(SELECT id FROM bazlist ORDER BY RAND() LIMIT 1), -100, 0, 0);
INSERT into Merchantlist(merchantid, slot, item, faction_required, level_required, alt_currency_cost) VALUES (22, 2,(SELECT id FROM bazlist ORDER BY RAND() LIMIT 1), -100, 0, 0);
INSERT into Merchantlist(merchantid, slot, item, faction_required, level_required, alt_currency_cost) VALUES (22, 3,(SELECT id FROM bazlist ORDER BY RAND() LIMIT 1), -100, 0, 0);
INSERT into Merchantlist(merchantid, slot, item, faction_required, level_required, alt_currency_cost) VALUES (22, 4,(SELECT id FROM bazlist ORDER BY RAND() LIMIT 1), -100, 0, 0);
INSERT into Merchantlist(merchantid, slot, item, faction_required, level_required, alt_currency_cost) VALUES (22, 5,(SELECT id FROM bazlist ORDER BY RAND() LIMIT 1), -100, 0, 0);
INSERT into Merchantlist(merchantid, slot, item, faction_required, level_required, alt_currency_cost) VALUES (22, 6,(SELECT id FROM bazlist ORDER BY RAND() LIMIT 1), -100, 0, 0);
INSERT into Merchantlist(merchantid, slot, item, faction_required, level_required, alt_currency_cost) VALUES (22, 7,(SELECT id FROM bazlist ORDER BY RAND() LIMIT 1), -100, 0, 0);
INSERT into Merchantlist(merchantid, slot, item, faction_required, level_required, alt_currency_cost) VALUES (22, 8,(SELECT id FROM bazlist ORDER BY RAND() LIMIT 1), -100, 0, 0);
INSERT into Merchantlist(merchantid, slot, item, faction_required, level_required, alt_currency_cost) VALUES (22, 9,(SELECT id FROM bazlist ORDER BY RAND() LIMIT 1), -100, 0, 0);
INSERT into Merchantlist(merchantid, slot, item, faction_required, level_required, alt_currency_cost) VALUES (22, 10,(SELECT id FROM bazlist ORDER BY RAND() LIMIT 1), -100, 0, 0);
INSERT into Merchantlist(merchantid, slot, item, faction_required, level_required, alt_currency_cost) VALUES (22, 11,(SELECT id FROM bazlist ORDER BY RAND() LIMIT 1), -100, 0, 0);
INSERT into Merchantlist(merchantid, slot, item, faction_required, level_required, alt_currency_cost) VALUES (22, 12,(SELECT id FROM bazlist ORDER BY RAND() LIMIT 1), -100, 0, 0);
INSERT into Merchantlist(merchantid, slot, item, faction_required, level_required, alt_currency_cost) VALUES (22, 13,(SELECT id FROM bazlist ORDER BY RAND() LIMIT 1), -100, 0, 0);
INSERT into Merchantlist(merchantid, slot, item, faction_required, level_required, alt_currency_cost) VALUES (22, 14,(SELECT id FROM bazlist ORDER BY RAND() LIMIT 1), -100, 0, 0);
INSERT into Merchantlist(merchantid, slot, item, faction_required, level_required, alt_currency_cost) VALUES (22, 15,(SELECT id FROM bazlist ORDER BY RAND() LIMIT 1), -100, 0, 0);
INSERT into Merchantlist(merchantid, slot, item, faction_required, level_required, alt_currency_cost) VALUES (22, 16,(SELECT id FROM bazlist ORDER BY RAND() LIMIT 1), -100, 0, 0);
INSERT into Merchantlist(merchantid, slot, item, faction_required, level_required, alt_currency_cost) VALUES (22, 17,(SELECT id FROM bazlist ORDER BY RAND() LIMIT 1), -100, 0, 0);
INSERT into Merchantlist(merchantid, slot, item, faction_required, level_required, alt_currency_cost) VALUES (22, 18,(SELECT id FROM bazlist ORDER BY RAND() LIMIT 1), -100, 0, 0);

INSERT into Merchantlist(merchantid, slot, item, faction_required, level_required, alt_currency_cost) VALUES (23, 0,(SELECT id FROM bazlist ORDER BY RAND() LIMIT 1), -100, 0, 0);
INSERT into Merchantlist(merchantid, slot, item, faction_required, level_required, alt_currency_cost) VALUES (23, 1,(SELECT id FROM bazlist ORDER BY RAND() LIMIT 1), -100, 0, 0);
INSERT into Merchantlist(merchantid, slot, item, faction_required, level_required, alt_currency_cost) VALUES (23, 2,(SELECT id FROM bazlist ORDER BY RAND() LIMIT 1), -100, 0, 0);
INSERT into Merchantlist(merchantid, slot, item, faction_required, level_required, alt_currency_cost) VALUES (23, 3,(SELECT id FROM bazlist ORDER BY RAND() LIMIT 1), -100, 0, 0);
INSERT into Merchantlist(merchantid, slot, item, faction_required, level_required, alt_currency_cost) VALUES (23, 4,(SELECT id FROM bazlist ORDER BY RAND() LIMIT 1), -100, 0, 0);
INSERT into Merchantlist(merchantid, slot, item, faction_required, level_required, alt_currency_cost) VALUES (23, 5,(SELECT id FROM bazlist ORDER BY RAND() LIMIT 1), -100, 0, 0);
INSERT into Merchantlist(merchantid, slot, item, faction_required, level_required, alt_currency_cost) VALUES (23, 6,(SELECT id FROM bazlist ORDER BY RAND() LIMIT 1), -100, 0, 0);
INSERT into Merchantlist(merchantid, slot, item, faction_required, level_required, alt_currency_cost) VALUES (23, 7,(SELECT id FROM bazlist ORDER BY RAND() LIMIT 1), -100, 0, 0);
INSERT into Merchantlist(merchantid, slot, item, faction_required, level_required, alt_currency_cost) VALUES (23, 8,(SELECT id FROM bazlist ORDER BY RAND() LIMIT 1), -100, 0, 0);
INSERT into Merchantlist(merchantid, slot, item, faction_required, level_required, alt_currency_cost) VALUES (23, 9,(SELECT id FROM bazlist ORDER BY RAND() LIMIT 1), -100, 0, 0);
INSERT into Merchantlist(merchantid, slot, item, faction_required, level_required, alt_currency_cost) VALUES (23, 10,(SELECT id FROM bazlist ORDER BY RAND() LIMIT 1), -100, 0, 0);
INSERT into Merchantlist(merchantid, slot, item, faction_required, level_required, alt_currency_cost) VALUES (23, 11,(SELECT id FROM bazlist ORDER BY RAND() LIMIT 1), -100, 0, 0);
INSERT into Merchantlist(merchantid, slot, item, faction_required, level_required, alt_currency_cost) VALUES (23, 12,(SELECT id FROM bazlist ORDER BY RAND() LIMIT 1), -100, 0, 0);
INSERT into Merchantlist(merchantid, slot, item, faction_required, level_required, alt_currency_cost) VALUES (23, 13,(SELECT id FROM bazlist ORDER BY RAND() LIMIT 1), -100, 0, 0);
INSERT into Merchantlist(merchantid, slot, item, faction_required, level_required, alt_currency_cost) VALUES (23, 14,(SELECT id FROM bazlist ORDER BY RAND() LIMIT 1), -100, 0, 0);
INSERT into Merchantlist(merchantid, slot, item, faction_required, level_required, alt_currency_cost) VALUES (23, 15,(SELECT id FROM bazlist ORDER BY RAND() LIMIT 1), -100, 0, 0);
INSERT into Merchantlist(merchantid, slot, item, faction_required, level_required, alt_currency_cost) VALUES (23, 16,(SELECT id FROM bazlist ORDER BY RAND() LIMIT 1), -100, 0, 0);
INSERT into Merchantlist(merchantid, slot, item, faction_required, level_required, alt_currency_cost) VALUES (23, 17,(SELECT id FROM bazlist ORDER BY RAND() LIMIT 1), -100, 0, 0);
INSERT into Merchantlist(merchantid, slot, item, faction_required, level_required, alt_currency_cost) VALUES (23, 18,(SELECT id FROM bazlist ORDER BY RAND() LIMIT 1), -100, 0, 0);

INSERT into Merchantlist(merchantid, slot, item, faction_required, level_required, alt_currency_cost) VALUES (24, 0,(SELECT id FROM bazlist ORDER BY RAND() LIMIT 1), -100, 0, 0);
INSERT into Merchantlist(merchantid, slot, item, faction_required, level_required, alt_currency_cost) VALUES (24, 1,(SELECT id FROM bazlist ORDER BY RAND() LIMIT 1), -100, 0, 0);
INSERT into Merchantlist(merchantid, slot, item, faction_required, level_required, alt_currency_cost) VALUES (24, 2,(SELECT id FROM bazlist ORDER BY RAND() LIMIT 1), -100, 0, 0);
INSERT into Merchantlist(merchantid, slot, item, faction_required, level_required, alt_currency_cost) VALUES (24, 3,(SELECT id FROM bazlist ORDER BY RAND() LIMIT 1), -100, 0, 0);
INSERT into Merchantlist(merchantid, slot, item, faction_required, level_required, alt_currency_cost) VALUES (24, 4,(SELECT id FROM bazlist ORDER BY RAND() LIMIT 1), -100, 0, 0);
INSERT into Merchantlist(merchantid, slot, item, faction_required, level_required, alt_currency_cost) VALUES (24, 5,(SELECT id FROM bazlist ORDER BY RAND() LIMIT 1), -100, 0, 0);
INSERT into Merchantlist(merchantid, slot, item, faction_required, level_required, alt_currency_cost) VALUES (24, 6,(SELECT id FROM bazlist ORDER BY RAND() LIMIT 1), -100, 0, 0);
INSERT into Merchantlist(merchantid, slot, item, faction_required, level_required, alt_currency_cost) VALUES (24, 7,(SELECT id FROM bazlist ORDER BY RAND() LIMIT 1), -100, 0, 0);
INSERT into Merchantlist(merchantid, slot, item, faction_required, level_required, alt_currency_cost) VALUES (24, 8,(SELECT id FROM bazlist ORDER BY RAND() LIMIT 1), -100, 0, 0);
INSERT into Merchantlist(merchantid, slot, item, faction_required, level_required, alt_currency_cost) VALUES (24, 9,(SELECT id FROM bazlist ORDER BY RAND() LIMIT 1), -100, 0, 0);
INSERT into Merchantlist(merchantid, slot, item, faction_required, level_required, alt_currency_cost) VALUES (24, 10,(SELECT id FROM bazlist ORDER BY RAND() LIMIT 1), -100, 0, 0);
INSERT into Merchantlist(merchantid, slot, item, faction_required, level_required, alt_currency_cost) VALUES (24, 11,(SELECT id FROM bazlist ORDER BY RAND() LIMIT 1), -100, 0, 0);
INSERT into Merchantlist(merchantid, slot, item, faction_required, level_required, alt_currency_cost) VALUES (24, 12,(SELECT id FROM bazlist ORDER BY RAND() LIMIT 1), -100, 0, 0);
INSERT into Merchantlist(merchantid, slot, item, faction_required, level_required, alt_currency_cost) VALUES (24, 13,(SELECT id FROM bazlist ORDER BY RAND() LIMIT 1), -100, 0, 0);
INSERT into Merchantlist(merchantid, slot, item, faction_required, level_required, alt_currency_cost) VALUES (24, 14,(SELECT id FROM bazlist ORDER BY RAND() LIMIT 1), -100, 0, 0);
INSERT into Merchantlist(merchantid, slot, item, faction_required, level_required, alt_currency_cost) VALUES (24, 15,(SELECT id FROM bazlist ORDER BY RAND() LIMIT 1), -100, 0, 0);
INSERT into Merchantlist(merchantid, slot, item, faction_required, level_required, alt_currency_cost) VALUES (24, 16,(SELECT id FROM bazlist ORDER BY RAND() LIMIT 1), -100, 0, 0);
INSERT into Merchantlist(merchantid, slot, item, faction_required, level_required, alt_currency_cost) VALUES (24, 17,(SELECT id FROM bazlist ORDER BY RAND() LIMIT 1), -100, 0, 0);
INSERT into Merchantlist(merchantid, slot, item, faction_required, level_required, alt_currency_cost) VALUES (24, 18,(SELECT id FROM bazlist ORDER BY RAND() LIMIT 1), -100, 0, 0);

Running that SQL will populate your 3 vendors with 18 items each RANDOMLY chosen from the bazlist table.

The reason I did this by creating a custom table is that you can add or remove items to this list so that they will also randomly appear or NOT appear on the vendor without editing or changing your items table itself.

The benefit of this for instance is that I removed all no drop, all items over reqlevel 65, all LDON reward items, all no rent items from the Bazlist table which will ensure they do not appear on these merchants however they are not removed from the game.

Please feel free to offer and suggestions and improvements to this idea that are you may have.

You can make a DB trigger to run this query every day or so and it will automatically on the fly update their inventories ( players logged in while they query is ran for some reason seem to have to log out to see changes)

I was attempting to make this into a command that can be used ingame but I cannot atm seem to get a command to run a basic SQL query. I will repost here if and when I get that part figured out.