Database Query Question

[NeroMorte]

How would i Search Database for Item ID and Find all Merchant or Mobs that Item will drop or sell off of?

is there any Easy way to do this?

[Angelox]

I'd say the easiest thing to start with on anything EqEmu/Mysql related, would be the PEQ PHP Editor. You can make quick single changes with this very easily. And you can use it as a "learn as you go" thing, because it makes a log of what it does, you'll see the MySql entries on changes you did there.

[VallonTallonZek]

Merchants with Item:

select npc_types.name, spawn2.zone from merchantlist
inner join npc_types on npc_types.merchant_id = merchantlist.merchantid
inner join spawnentry on spawnentry.npcID = npc_types.id
inner join spawn2 on spawn2.spawngroupID = spawnentry.spawngroupID
where item = INSERT ITEM ID;

Mobs that drop an Item:

select npc_types.name, items.name, lootdrop_entries.item_id, lootdrop_entries.lootdrop_id, lootdrop_entries.item_charges, lootdrop_entries.chance, loottable_entries.loottable_id, loottable_entries.multiplier, loottable_entries.probability from lootdrop_entries
inner join loottable_entries on lootdrop_entries.lootdrop_id = loottable_entries.lootdrop_id
inner join npc_types on npc_types.loottable_id = loottable_entries.loottable_id
inner join items on items.id = lootdrop_entries.item_id
where items.id = INSERT ITEM ID;

other useful queries:

Player Corpses by Name:

select player_corpses.charname, zone.short_name, player_corpses.x, player_corpses.y, player_corpses.z from player_corpses
inner join zone on zone.zoneidnumber = player_corpses.zoneid
where charname = 'INSERT PLAYER NAME';

What players have X item and how many:

select inventory.charid, character_.name, account.name, items.name, inventory.slotid, inventory.charges from inventory
inner join character_ on character_.id = inventory.charid
inner join account on account.id = character_.account_id
inner join items on items.id = inventory.itemid
where items.id = INSERT ITEM ID order by account.name;

[NeroMorte]

WoW VallonTallonZek,

Thats 100% what i need'ed Thank you so much

[NeroMorte]

hey Angelox,

also thank you i'm going to try out the PEQ PHP Editor. Might help me Edit Merchants and stuff easyer.

[trevius]

The easiest way to do this is to use GeorgeS' Item Editor from the tools section of the forums. Just search for the item and select it and it will list every spawn that drops it or sells it down in the lower left corner of the window. Very quick and handy