Welcome to AC Web.
Results 1 to 7 of 7
  1. #1
    Web Developer

    Join Date
    Oct 2010
    Location
    somewhere between <?php and ?>
    Posts
    3,067

    Inventory Inspector SQL Query


    REGISTER! (FREE)
    Registered members see less ads
    and also gain access to other great features.
    I wrote a query a few days ago to inspect a characters inventory and i figured out that maybe someone else will need a query that can do this so i decided to share it !

    Code:
    SELECT ii.itemEntry, it.name AS ItemName, ch.name AS CharName, COUNT(*) AS amount, CASE WHEN ii.`count` > 1 THEN ii.`count` ELSE NULL END as stackAmount FROM item_instance ii
    INNER JOIN world.item_template it
    ON ii.itemEntry = it.entry
    INNER JOIN characters ch
    ON ch.guid = ii.owner_guid
    WHERE ch.name = "PlayerName" GROUP BY ii.itemEntry, it.name, ch.name;
    The query will return Item Entry, Item Name, Character Name, Amount and Stack Amount.
    The reason why there is amount and stack amount is because lets say you have Rabbit's Foot x10 in a stack it will only show as 1 in amount and 10 in stackAmount but if you separate them it will show as 10 in Amount and (null) in stackAmount

    Run in characters database and do not forget to edit the name in green text, And do NOT remove quotes !

    Also, remember that this is a SELECT query so it wont change anything in your database therefore there is no need to create a backup. If anything goes wrong all that will happen is that it wont show the result. But the query has been tested !

    Let me know if you found it useful !

    Enjoy !

  2. #2
    Dope query, I can't find a use for it atm but indeed this can be useful somehow.
    Thank you.

  3. #3


    Join Date
    Dec 2015
    Location
    Developing the world
    Posts
    1,224
    Nice one friend!

    - - - Updated - - -

    You must spread some Reputation around before giving it to Tok124 again.

  4. #4
    Web Developer

    Join Date
    Oct 2010
    Location
    somewhere between <?php and ?>
    Posts
    3,067
    Quote Originally Posted by molinita View Post
    Dope query, I can't find a use for it atm but indeed this can be useful somehow.
    Thank you.
    Thank you !

    - - - Updated - - -

    Quote Originally Posted by titkata_bg View Post
    Nice one friend!

    - - - Updated - - -

    You must spread some Reputation around before giving it to Tok124 again.
    Hehe thanks, No problem my friend !

  5. #5


    Join Date
    Dec 2019
    Location
    cybermist2.github.io
    Posts
    113
    Quote Originally Posted by Tok124 View Post
    I wrote a query a few days ago to inspect a characters inventory and i figured out that maybe someone else will need a query that can do this so i decided to share it !

    Code:
    SELECT ii.itemEntry, it.name AS ItemName, ch.name AS CharName, COUNT(*) AS amount, CASE WHEN ii.`count` > 1 THEN ii.`count` ELSE NULL END as stackAmount FROM item_instance ii
    INNER JOIN world.item_template it
    ON ii.itemEntry = it.entry
    INNER JOIN characters ch
    ON ch.guid = ii.owner_guid
    WHERE ch.name = "PlayerName" GROUP BY ii.itemEntry, it.name, ch.name;
    The query will return Item Entry, Item Name, Character Name, Amount and Stack Amount.
    The reason why there is amount and stack amount is because lets say you have Rabbit's Foot x10 in a stack it will only show as 1 in amount and 10 in stackAmount but if you separate them it will show as 10 in Amount and (null) in stackAmount

    Run in characters database and do not forget to edit the name in green text, And do NOT remove quotes !

    Also, remember that this is a SELECT query so it wont change anything in your database therefore there is no need to create a backup. If anything goes wrong all that will happen is that it wont show the result. But the query has been tested !

    Let me know if you found it useful !

    Enjoy !
    Thanks works great

  6. #6
    Web Developer

    Join Date
    Oct 2010
    Location
    somewhere between <?php and ?>
    Posts
    3,067
    Quote Originally Posted by Cyber Mist View Post
    Thanks works great
    No problem man ! enjoy

  7. #7

    REGISTER! (FREE)
    Registered members see less ads
    and also gain access to other great features.
    Quote Originally Posted by Cyber Mist View Post
    Thanks works great
    You should not quote an entire main post, it's annoying.

 

 

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •