A bit more time spent with this. Someone more versed with SQL might be able to whip this into something more optimized, but here's what I came up with.
This bit of SQL accomplishes the following:
- Creates a new npc_type entry and merchantlist entries
Given the following:
- Tradeskill number (from post above)
- MerchantID number (check your database and find a number not in use, plug it in)
- Merchant name and associated values to use in the npc_types entry, e.g. race, class, level, hp, etc -- you can customize this as much as you want.
Code:
DROP TABLE IF EXISTS merchantlist_tskills;
CREATE TABLE IF NOT EXISTS
merchantlist_tskills
(slot INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, itemid INTEGER);
INSERT INTO merchantlist_tskills (itemid) (
SELECT
tradeskill_recipe_entries.item_id
FROM
tradeskill_recipe,
tradeskill_recipe_entries
WHERE
tradeskill_recipe.id = tradeskill_recipe_entries.recipe_id AND
tradeskill_recipe.tradeskill = TRADESKILLNUMBER AND
tradeskill_recipe_entries.successcount = 1
);
INSERT INTO merchantlist (merchantid, slot, item)
(SELECT 'MERCHANTID', slot, itemid FROM merchantlist_tskills);
DROP TABLE merchantlist_tskills;
INSERT INTO npc_types (name, level, race, class, gender, texture, merchant_id) VALUES ('MERCHANT_NAME00', 1, 1, 41, 0, 0, MERCHANTID);
When done, you should have a lot of new merchantlist entries and a new npc_type entry. You should then be able to use the #dbspawn command (I believe) to go poop your new merchant out wherever you want and perform other associated world-building magicks to set them up.
To note, I have not tested this in-game. I'm not sure what the ramifications are for having a merchantlist's slot value go so high (prior to this, the max amount of items a merchant has listed in my DB from PEQ is 78.. 80 might be the most the server will recognize).
Let me know how it goes