Welcome to AC Web.
Results 1 to 8 of 8
  1. #1

    Easy MySQL Queries - MySQL Tips and Tricks for WoW Emulation


    REGISTER! (FREE)
    Registered members see less ads
    and also gain access to other great features.
    Hello and welcome to some easy MySQL Queries or rather MySQL Tips and Tricks for WoW Emulation - especially aimed at custom servers.

    I hope these can be of use to some although most are select queries I still think they can be very useful for creating an overview of what you are working with. Please edit these to fit your needs, and note that most of these are very versatile in that they can be used on different tables as well. The idea is to give you some tips and tricks to work with, and then you can edit them to a point where you yourself find them useful.

    I also hope for some requests to be added to the list and I encourage you to share whatever tricks and tips you might have, so that we may add them to the list.

    I - hope - this list will serves as a good outset for new as well as old MySQL developers dealing with WoW Emulation servers!

    Small disclaimer: I am not using the latest trinity core, and if you find something is outdated please speak your mind and I will "upgrade" them to fit the latest core.

    With that said, let's begin..


    Select the highest entry from a coloumn.
    This is usuful when creating new items, to make sure you are using the highest entry and then plus it by 1,2,3 and so on...
    IMPORTANT: please check the table u are using this on first if there is an entry with the highest possible number already as this will lead to errors.


    Example:
    Code:
    set
    @entry := (select max(item_template.entry) from item_template);
    
    insert into item_template (entry) Values 
    (@entry+1),
    (@entry+2),
    (@entry+3);
    Order things randomly when selected

    Code:
    ORDER BY RAND()
    Example:
    This will select all items with entry between 1 and 99999 (including 1 and 99999) and itemlevel = 264 and no class specific items, and then it will order them randomly!

    Code:
    select entry from item_template 
    where entry between (1) and (99999) 
    and itemlevel in (264)  
    and not allowableclass in (1,2,4,8,16,32,64,128,256,512,1024)
    ORDER BY RAND();
    Select all creature_template creatures from a specific map (most likely an instance)
    Note that these can be edited since it is only showing coloumns from 1 table!



    Code:
    set 
    @MapID = 33;
    
    select distinct creature_template.entry as CreatureEntry, creature_template.name, creature_template.subname
    from creature_template
    inner join creature on creature_template.entry = creature.id
    where creature.map = @MapID;

    View a creatures loot with item_template name and creature_template name including important creature_loot_template info
    NOTE This is only for viewing! You cannot edit lines selected from multiple tables! If you want to edit this you need to only have 1 specific table open!
    NOTE as well, this does not select any reference loot!



    Code:
    Set
    @CreatureEntry = ;
    
    select creature_template.entry as CreatureEntry, creature_template.name as CreatureName,
    item_template.entry as ItemEntry, item_template.name as ItemName, 
    creature_loot_template.Chance, creature_loot_template.MinCount, creature_loot_template.MaxCount,
    creature_loot_template.GroupId
    from creature_loot_template
    inner join creature_template on creature_loot_template.Entry = creature_template.entry
    inner join item_template on item_template.entry = creature_loot_template.Item
    
    where creature_template.entry = @CreatureEntry 
    
    order by item_template.entry ASC;
    View all creatures in a specifc map (instance for example) and their loot with item_template name
    note this is a combination of the two above, and again you cannot edit this as long as there are selected coloumns from multiple tables!
    NOTE as well, this does not select any reference loot!



    Code:
    Set
    @MapID = ;
    
    select distinct creature_template.entry as CreatureEntry, creature_template.name as CreatureName,
    item_template.entry as ItemEntry, item_template.name as ItemName, 
    creature_loot_template.Chance, creature_loot_template.MinCount, creature_loot_template.MaxCount,
    creature_loot_template.GroupId
    from creature_loot_template
    inner join creature_template on creature_loot_template.Entry = creature_template.entry
    inner join item_template on item_template.entry = creature_loot_template.Item
    inner join creature on creature_loot_template.Entry = creature.id
    
    where creature.map = @MapID
    
    order by creature_template.entry ASC;


    View a creatures (vendor) items with creature name and item name
    note this cannot be edited, as there are coloumns from more than 1 table!


    Code:
    Set
    @CreatureEntry = ;
    
    select creature_template.entry as CreatureEntry, creature_template.name as CreatureName,
    item_template.entry as ItemEntry, item_template.name as ItemName, npc_vendor.ExtendedCost
    from npc_vendor
    inner join creature_template on npc_vendor.entry = creature_template.entry
    inner join item_template on npc_vendor.item = item_template.entry
    
    where npc_vendor.entry = @CreatureEntry
    
    Order by item_template.entry asc;

    View more than 1 creature (vendor) items with creature name and item name
    note again this cannot be edited, as there are coloumns from more than 1 table!
    Note as well, the bright will notice this can be extended to include more than 3 creatures!



    Code:
    Set
    @CreatureEntry1 = ,
    @CreatureEntry2 = ,
    @CreatureEntry3 = ;
    
    select creature_template.entry as CreatureEntry, creature_template.name as CreatureName,
    item_template.entry as ItemEntry, item_template.name as ItemName, npc_vendor.ExtendedCost
    from npc_vendor
    inner join creature_template on npc_vendor.entry = creature_template.entry
    inner join item_template on npc_vendor.item = item_template.entry
    
    where npc_vendor.entry in (@CreatureEntry1, @CreatureEntry2, @CreatureEntry3)
    
    Order by creature_template.entry asc, item_template.entry asc;


    View Refference loot with item names!
    Note this cannot be edited, due to coloumns being selected from multiple tables



    Code:
    set
    @RefferenceEntry = ;
    
    select reference_loot_template.entry as RefferenceID, reference_loot_template.item as ItemEntry, item_template.name as ItemName
    from reference_loot_template
    inner join item_template on reference_loot_template.item=item_template.entry
    where reference_loot_template.Entry = @RefferenceEntry 
    order by reference_loot_template.Entry asc;

    Thanks for viewing! And note that if this is well recieved I will continue to add more including if someone else has something they want added to the list!

    - - - Updated - - -

    Easy gameobject_template teleporter (or portal).
    This will teleport the player to the destination when clicked.



    Code:
    set
    @GameobjectEntry = (select max(gameobject_template.entry) from gameobject_template)+1,
    @GameobjectDisplay = 1327,
    @GameobjectName = 'Portal to ',
    @PositionMap = ,
    @PositionX = '',
    @PositionY = '',
    @PositionZ = '',
    @PositionO = '',
    @Comment = 'Portal to -- teleport script';
    
    
    
    INSERT INTO `gameobject_template` (`entry`, `type`, `displayId`, `name`, `size`, `AIName`) VALUES 
    (@GameobjectEntry, 2, @GameobjectDisplay, @GameobjectName, 1, 'SmartGameObjectAI');
    
    INSERT INTO `gameobject_template_addon` (`entry`, `faction`) VALUES 
    (@GameobjectEntry, 35);
    
    INSERT INTO `smart_scripts` (`entryorguid`, `source_type`, `id`, `link`, `event_type`, `event_phase_mask`, `event_chance`, `event_flags`, `event_param1`, `event_param2`, `event_param3`, `event_param4`, `event_param5`, `action_type`, `action_param1`, `action_param2`, `action_param3`, `action_param4`, `action_param5`, `action_param6`, `target_type`, `target_param1`, `target_param2`, `target_param3`, `target_x`, `target_y`, `target_z`, `target_o`, `comment`) VALUES 
    (@GameobjectEntry, 1, 0, 0, 64, 0, 100, 0, 0, 0, 0, 0, 0, 62, @PositionMap, 0, 0, 0, 0, 0, 7, 0, 0, 0, @PositionX, @PositionY, @PositionZ, @PositionO, @Comment);

    Update Playercreateinfo (the place where the start position for all race and class combination is).
    Note this will update all to the same location
    To update only a single race remove the comment (--) and edit the where clause.


    Code:
    set
    @PositionMap = ,
    @PositionX = '',
    @PositionY = '',
    @PositionZ = '',
    @PositionO = '';
    
    update playercreateinfo set playercreateinfo.map = @PositionMap, playercreateinfo.position_x = @PositionX,
    playercreateinfo.position_y = @PositionY, playercreateinfo.position_z = @PositionZ,
    playercreateinfo.orientation = @PositionO
    -- where playercreateinfo.race in ()
    ;

    Update player_xp_for_level (in case you have more than 80 levels )
    NOTE I have marked what it increments the levels with in red, which right now increments the xp needed exponentially (*1.15) To mimic what the core does by default if you do not input this data replace "*1.15" with "+100" - which will increase the xp needed for levels by 100 more xp than the previous level.
    HOW TO USE THIS: Input the level 1 level above the already existing highest level - EG: if the highest level in the table is 79, write @PlayerLevel = 80!
    Then when you have run it for level 80, the highest will be 80 and you need to input the level: 81
    Also note that if you have maxlevel 100, you only need data for level 99, as level 99 data will be what is needed for getting to level 100.



    Code:
    set
    @PlayerLevel = 80;
    
    set
    @xpForLevel = ((select player_xp_for_level.Experience from player_xp_for_level where player_xp_for_level.level = ((@PlayerLevel)-1))*1.15);
    
    Insert into player_xp_for_level (level, experience) VALUES
    (@PlayerLevel, @xpForLevel);

    Easy Quest Item Create

    Code:
    set
    @entry = (select max(item_template.entry) from item_template)+1,
    @name = '',
    @description = '',
    @displayID = ,
    @stackable = 1;
    
    
    INSERT INTO `item_template` (`entry`, `class`, `subclass`, `name`, `displayid`, `Quality`, `stackable`, `bonding`, `description`, `BagFamily`) VALUES 
    (@entry, 12, 0, @name, @displayID, 1, @stackable, 1, @description, 16384);
    Easy Trade Skill Item Creation
    Note this will make the item stack up to 200, to change this change the red 200 to what ever you want it to stack up to.


    Code:
    set
    @entry = (select max(item_template.entry) from item_template)+1,
    @name = '',
    @description = '',
    @displayID = ,
    @quality = ,
    @sellPrice = ,
    @subClass = ,
    -- 0 - Trade Goods, 1 - Parts, 2 - Explosives, 3 - Devices, 4 - Jewelcrafting, 5 - Cloth, 6 - Leather, 
    -- 7 - Metal & Stone, 8 - Meat, 9 - Herb, 10 - Elemental, 11 - Other, 12 - Enchanting, 13 - Materials, 
    -- 14 - Armor Enchantment, 15 - Weapon Enchantment
    @bagfamily = ;
    -- 0: None, 1: Arrows, 2: Bullets, 4: Soul Shards, 8: Leatherworking Supplies, 16: Inscription Supplies, 
    -- 32: Herbs, 64: Enchanting Supplies, 128: Engineering Supplies, 256: Keys, 512: Gems, 1024: Mining Supplies, 
    -- 2048: Soulbound Equipment, 4096: Vanity Pets, 8192: Currency Tokens, 16384: Quest Items
    
    
    INSERT INTO `item_template` (`entry`, `class`, `subclass`, `name`, `displayid`, `Quality`, `SellPrice`, `stackable`, `bonding`, `description`, `Material`, `BagFamily`) VALUES 
    (@entry, 7, @subClass, @name, @displayID, @quality, @sellPrice, 200, 0, @description, 0, @bagfamily);

    Post is too long for more So refer to the #4 comment for more queries
    Last edited by frathir; 12-28-2018 at 12:56 PM.

  2. #2


    Join Date
    Jun 2012
    Location
    http://undamed-wow.com/
    Posts
    527
    Nice dude

  3. #3


    Join Date
    Apr 2008
    Location
    Security supervisor
    Posts
    972
    Good share.

    Some may find this useful.

  4. #4
    Thank you for the kind comments! I added some item creation queries, for easy item creation of a specific type.



    Since the post was too long I will continue here .


    Easy Bag Item Creation

    Code:
    set
    @entry = (select max(item_template.entry) from item_template)+1,
    @name = '',
    @description = '',
    @displayID = ,
    @quality = 1,
    @BuyPrice = 0,
    @sellPrice = 0,
    @AllowableClass = '-1',
    -- Note this is a bitmask eg: 256 for warlock item (-1 for all classes)
    @RequiredSkill = 0,
    @RequiredSkillRank = 0,
    @ContainerSlots = 6,
    @bonding = 2,
    -- 0: No bounds, 1: Binds when picked up, 2: Binds when equipped, 3: Binds when used, 
    @subClass = 0,
    -- 0: Bag, 1: Soul Bag, 2: Herb Bag, 3: Enchanting Bag, 4: Engineering Bag, 5: Gem Bag, 6: Mining Bag
    -- 7: Leatherworking Bag, 8: Inscription Bag
    @bagfamily = ;
    -- 0: None, 1: Arrows, 2: Bullets, 4: Soul Shards, 8: Leatherworking Supplies, 16: Inscription Supplies, 
    -- 32: Herbs, 64: Enchanting Supplies, 128: Engineering Supplies, 256: Keys, 512: Gems, 1024: Mining Supplies, 
    -- 2048: Soulbound Equipment, 4096: Vanity Pets, 8192: Currency Tokens, 16384: Quest Items
    
    
    
    INSERT INTO `item_template` (`entry`, `class`, `subclass`, `name`, `displayid`, `Quality`, `BuyPrice`, `SellPrice`, `InventoryType`, `AllowableClass`, `RequiredSkill`, `RequiredSkillRank`, `ContainerSlots`, `bonding`, `description`, `Material`, `BagFamily`) VALUES 
    (@entry, 1, @subClass, @name, @displayID, @quality, @BuyPrice, @sellPrice, 18, @AllowableClass, @RequiredSkill, @RequiredSkillRank, @ContainerSlots, @bonding, @description, 8, @bagfamily);


    Easy Currency Item Creation
    -- Note: remember to add in DBC as well or it wont work


    Code:
    set
    @entry = (select max(item_template.entry) from item_template)+1,
    @name = '',
    @description = '',
    @displayID = ,
    @quality = 1;
    -- 0: Grey, 1: White, 2: Green, 3: Blue, 4: Purple, 5: Orange, 6: Red, 7: Gold
    
    
    INSERT INTO `item_template` (`entry`, `class`, `subclass`, `name`, `displayid`, `Quality`, `Flags`, `AllowableClass`, `AllowableRace`, `stackable`, `bonding`, `description`, `Material`, `BagFamily`) VALUES 
    (@entry, 10, 0, @name, @displayID, @quality, 2048, 262143, 32767, 2147483647, 1, @description, -1, 8192);

    "Easy" Gossip Creator
    Note: I was debating if I should add this but I think some might find it useful although THIS NEEDS HEAVY EDITING.
    Note2: You CANNOT add the normal select max() query since THERE IS ALREADY A MAX ENTRY. This means you have to manually select an entry for both gossip_menu and npc_text!


    Code:
    set
    @gossipMenuID = ,
    @textMenuID = ,
    @creatureEntry = ;
    
    
    insert into gossip_menu (gossip_menu.MenuID, gossip_menu.TextID) Values
    (@gossipMenuID,@textMenuID);
    
    Insert into npc_text (npc_text.ID, npc_text.text0_0) Values
    (@textMenuID,'Hello $C, my name is Phalirial and I am the Jewelcrafting Trainer. What do you need?');
    
    update creature_template set creature_template.gossip_menu_id = @gossipMenuID
    where creature_template.entry = @creatureEntry;
    
    
    Insert into gossip_menu_option (gossip_menu_option.MenuID, gossip_menu_option.OptionID,
    gossip_menu_option.OptionIcon, gossip_menu_option.OptionText, gossip_menu_option.OptionType,
    gossip_menu_option.OptionNpcFlag, gossip_menu_option.ActionMenuID) VALUES 
    (@gossipMenuID, 0, 1, 'Let me browse your Jewelcrafting Items', 3, 128, 0),
    (@gossipMenuID, 1, 3, 'I wish to learn Jewelcrafting Spells', 5, 16, 0);
    
    
    -- (@gossipMenuID, ID, Icon, 'text', optiontype, optionnpcflag, nextmenu),
    -- the above is a help line so that you may add options easily by replacing the words with what you need.
    Last edited by frathir; 12-28-2018 at 01:07 PM.

  5. #5

  6. #6
    Thank you for the share

  7. #7
    BumB, please if you have any ideas or things you need for your server please don't hesitate to suggest them

    Been a while so I thought I would update with some more quiries..


    Update creature.equipment_id:
    This will update all creature spawns between the two entries, to select a random equipment from creature_equip_template.
    Please note that you have to have the equipment details in creature_equip_template, AND if you only have 1 equipment it will always select that.

    Code:
    SET 
    @CreatureEntryMin = 60000,
    @CreatureEntryMax = 69999;
    
    
    UPDATE creature 
    JOIN creature_equip_template ON creature.id = creature_equip_template.CreatureID
    SET creature.equipment_id = -1 
    WHERE creature_equip_template.CreatureID BETWEEN (@CreatureEntryMin) AND (@CreatureEntryMax);


    Create a dummy Creature_template:
    This will creatre a dummy npc, that can be used for various things. Is Smart_AI by default.
    Code:
    set
    @creatureEntry = (select max(creature_template.entry) from creature_template);
    
    set
    @creatureName = '',
    @creatureScale = '1';
    
    set 
    @npcFlags = 0;
    
    
    
    INSERT INTO `world`.`creature_template` (`entry`, `modelid1`, `name`, `subname`, `minlevel`, `maxlevel`, `exp`, `faction`, `scale`, `BaseAttackTime`, `unit_class`, `type`, `AIName`, `flags_extra`, npcflag) VALUES 
    (@creatureEntry+1, '40721', @creatureName, 'Dummy', '100', '100', '2', '14', @creatureScale, '2000', '1', '10', 'SmartAI', '128', @npcFlags);
    
    
    select entry, name from creature_template where entry=(select max(creature_template.entry));

  8. #8

    REGISTER! (FREE)
    Registered members see less ads
    and also gain access to other great features.
    Thanks, nice tutorial

 

 

Posting Permissions

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