Search for an item on whole server

[Xtremist]

Incase I want to see if the item for eg.icepick exists on the server and get his charid or accid

In which all tables can the item go?

inventory

cart_inventory

storage

mail

char

what will be sql query?

I turned up with something like this,

but it ain't working :|

SELECT c.char_id,c.account_id,c.name
FROM `char` c,`auction` a,`cart_inventory` ci,`guild_storage` g,`inventory` i,`mail` m,`storage` s
WHERE a.nameid=4035 OR ci.nameid=4035 OR g.nameid=4035 OR i.nameid=4035 OR s.nameid=4035;

Edited July 20, 2012 by Xtremist

[JoWei]

This query should work fine for your case, just change 1201 for your item ID

select `account_id` from `char` where `char_id` in
(
select `char_id` from `inventory`  where `nameid`=1201
union
select `char_id` from `cart_inventory` where `nameid`=1201
)
or `account_id` in
(
select `account_id` from `storage` where `nameid`=1201
)
or `guild_id` in
(
select `guild_id` from `guild_storage` where `nameid`=1201
)

[Xtremist]

I noticed that my query is working but taking too much of shit load

Will try yours too later.

But have you tried it JoWei?

[JoWei]

Yes I have tried my query on my own RO DB but population is low which is why I used knife[3] since they are the most common among newly created characters.

It returns the account ID if it finds the item specified.

select `account_id`, `char_id` from `char` where `char_id` in
(
select `char_id` from `inventory`  where `nameid`=1201
union
select `char_id` from `cart_inventory` where `nameid`=1201
union
select `dest_id` from `mail` where `nameid`=1201
)
or `account_id` in
(
select `account_id` from `storage` where `nameid`=1201
)
or `guild_id` in
(
select `guild_id` from `guild_storage` where `nameid`=1201
)

Edit: Noticed I forgot the mail check and returns the char_id.

Edited July 20, 2012 by JoWei

[HeikoS]

There a two more places, were items can be: auction and guild_storage.

Also it will be possible that cards are in equipment. I will check every table with a single query.

For example (inventory):

SELECT `char`.name, nameid, amount, refine, attribute, card0, card1, card2, card3
FROM `inventory` INNER JOIN `char` ON (`inventory`.char_id = `char`.char_id)
WHERE `nameid` = '/*[VARIABLE]*/' OR card0 = '/*[VARIABLE]*/' OR card1 = '/*[VARIABLE]*/'
OR card2 = '/*[VARIABLE]*/' OR card3 = '/*[VARIABLE]*/' ORDER BY `char`.`name` ASC

Replace /*[VARIABLE]*/ with the item-id.

[Xtremist]

There a two more places, were items can be: auction and guild_storage.

Also it will be possible that cards are in equipment. I will check every table with a single query.

For example (inventory):

SELECT `char`.name, nameid, amount, refine, attribute, card0, card1, card2, card3
FROM `inventory` INNER JOIN `char` ON (`inventory`.char_id = `char`.char_id)
WHERE `nameid` = '/*[VARIABLE]*/' OR card0 = '/*[VARIABLE]*/' OR card1 = '/*[VARIABLE]*/'
OR card2 = '/*[VARIABLE]*/' OR card3 = '/*[VARIABLE]*/' ORDER BY `char`.`name` ASC

Replace /*[VARIABLE]*/ with the item-id.

This is only for inventory

I want the whole thingy and in my post I have already added auction and guild storage.

Edited July 20, 2012 by Xtremist

[HeikoS]

Yes, it is an example. The other queries will be similar.

I did`t finish the complete script right now. It will show the items in the different places separatly, not all together. But you can show, where the items are.

My queries will show more informations about the owner and the item itself (refine, cards, etc.)

It was just an suggestion. Use the queries which more fits your desires. ^^