[c++] Fetch database entries and show it in a gossip menu?

Hello,

i want to create a gossip menu which show some database entries. For example i have the item_template table which contains some items (id range: 50000-50010). I want that my gossip menu execute a command like these: “SELECT entry, name FROM item_template WHERE entry >= 50000 AND entry < =50010” and fetch all results into different menu entries like these:

Item 50000 Testname1

Item 50001 Testname2

Item 50002 Testname3

If i click on an gossip entry then i add me the item. For example i click on the first entry so i get the item 50000 (how i can add items is not a problem for me!).

My final question is: Is it possible to fetch entries and display it in a gossip menu like gossip entries?

Note that item template is already loaded into the core.

Are you looking for an example of querying the database?
Or just getting the items?

For the first you can do this:

#include “ScriptPCH.h”

class ExampleScript : public CreatureScript
{
public:
ExampleScript() : CreatureScript(“ExampleScript”)
{
// load item data on “startup” from DB
QueryResult result = WorldDatabase.Query(“SELECT entry, name FROM item_template WHERE entry BETWEEN 50000 AND 50010”);

    if (result) // check that query returned something
    {
        do // loop results
        {
            // get data from query
            Field* field = result->Fetch();
            uint32 entry = field[0].GetUInt32(); // index of columns starts at 0
            std::string name = field[1].GetString();

            if (!sObjectMgr->GetItemTemplate(entry)) // Just to be sure, check that the item is loaded to core
                continue; // skip row if not

            ItemInfo& data = items[entry]; // [] operator creates a new ItemInfo to the map for the entry key. We temporarily save a reference to it
            data.entry = entry; // add the item info to the data saved
            data.name = name;
        } while (result->NextRow()); // advance to next row as long as one exists
    }
}

bool OnGossipHello(Player* player, Creature* creature) OVERRIDE
{
    for (ItemData::const_iterator it = items.begin(); it != items.end(); ++it) // loop items
    {
        std::ostringstream oss; // use stringstream to concatenate the label pieces, example: Item 50000 Testname1
        oss << "Item " << it->second.entry << " " << it->second.name;
        player->ADD_GOSSIP_ITEM(GOSSIP_ICON_CHAT, oss.str(), GOSSIP_SENDER_MAIN, it->second.entry); // use item entry as action
    }
    player->SEND_GOSSIP_MENU(DEFAULT_GOSSIP_MESSAGE, creature->GetGUID()); // send gossip menu
    return true; // stop default menu from showing
}

bool OnGossipSelect(Player* player, Creature* creature, uint32 sender, uint32 action) OVERRIDE
{
    player->PlayerTalkClass->ClearMenus();
    if (sender == GOSSIP_SENDER_MAIN && action) // check that sender matches and that action is not 0
        player->AddItem(action, 1); // action was set to item entry earlier, so this should work
    OnGossipHello(player, creature); // show main menu again
    return true; // stop default gossip actions
}

struct ItemInfo
{
    uint32 entry;
    std::string name;
};
typedef std::map<uint32, ItemInfo> ItemData;
ItemData items;

};

void AddSC_ExampleScript()
{
new ExampleScript();
}

For the latter you can do this:

#include "ScriptPCH.h"

class ExampleScript : public CreatureScript
{
public:
    ExampleScript() : CreatureScript("ExampleScript")
    {
    }

    bool OnGossipHello(Player* player, Creature* creature) OVERRIDE
    {
        for (uint32 i = 50000; i <= 50010; ++i) // loop item entries
        {
            if (const ItemTemplate* temp = sObjectMgr->GetItemTemplate(i)) // get template and check if the item exists
            {
                std::ostringstream oss; // use stringstream to concatenate the label pieces, example: Item 50000 Testname1
                oss << "Item " << temp->ItemId << " " << temp->Name1;
                player->ADD_GOSSIP_ITEM(GOSSIP_ICON_CHAT, oss.str(), GOSSIP_SENDER_MAIN, temp->ItemId); // use item entry as action
            }
        }
        player->SEND_GOSSIP_MENU(DEFAULT_GOSSIP_MESSAGE, creature->GetGUID()); // send gossip menu
        return true; // stop default menu from showing
    }

    bool OnGossipSelect(Player* player, Creature* creature, uint32 sender, uint32 action) OVERRIDE
    {
        player->PlayerTalkClass->ClearMenus();
        if (sender == GOSSIP_SENDER_MAIN && action) // check that sender matches and that action is not 0
            player->AddItem(action, 1); // action was set to item entry earlier, so this should work
        OnGossipHello(player, creature); // show main menu again
        return true; // stop default gossip actions
    }
};

void AddSC_ExampleScript()
{
    new ExampleScript();
}

Thanks for your reply! The item fetch was only an example. I create a new table in my database which contains three rows (id, name, cost). So i fetch all entries which has cost lower than 10: SELECT * FROM testtable WHERE cost < 10

If i click on the first menu entrie in the gossip i get for example a message like “You click on entrie %d with the name %s”.

So… are the examples above enough?..

Or…?

In both scripts the item entry (action) basically gives you access to all the item’s info through item template.

The first script also shows how to query the database

Yes your examples is enough. If i understand it right then your second example works without an sql query and your first example use a query. So i orientate me at your first example.

Okay i try to understand your code but this area

for (ItemData::const_iterator it = items.begin(); it != items.end(); ++it) // loop items
{
std::ostringstream oss; // use stringstream to concatenate the label pieces, example: Item 50000 Testname1
oss << "Item " << it->second.entry << " " << it->second.name;
player->ADD_GOSSIP_ITEM(GOSSIP_ICON_CHAT, oss.str(), GOSSIP_SENDER_MAIN, it->second.entry); // use item entry as action
}

i do not understand. You save the values in a struct, right? But what is this const_iterator? Is it necessary to save the values in a struct?

Yes, I save the values in a map with uint32 (item entry) as key and a struct with data in it as value.

const_iterator is the same as iterator. (except its const…, so you cant modify it).

Iterator is a way of accessing an element in a map or other containers.

for example as in my example I used std::map<uint32, ItemInfo> items; Then it->first would be the uint32 (key) and it->second would be the ItemInfo (value)

Iterators are a great way of looping through a map or other container. You cant use something like uint32 i = 0; i < items.size(); ++i;

since there are 2 items, but the keys are not 0 and 1.

Google c++ iterator /emoticons/default_smile.png

http://www.cplusplus.com/reference/map/map/begin/

Basically you dont need to understand it more than that items.begin() gets iterator to the first element in a container, items.end() means an invalid iterator, ++it goes to the next iterator.

And of course how to access the key and value. (it->first, it->second)

ItemData::const_iterator == std::map<uint32, ItemInfo>::const_iterator
Its not /necessary/ but why not?

The data is static in DB. Its not going to change.

Instead of for example querying the database every time someone clicks the NPC or an option, the data could be in the memory, as it doesnt change over time.

Okay i try to write something but now i have a little problem. I need for my Query the player coordinates so i write this:

#include “ScriptPCH.h”
#include “GameEventMgr.h”
#include “ObjectMgr.h”
#include “PoolMgr.h”
#include “MapManager.h”
#include “Chat.h”
#include “Language.h”
#include “Player.h”
#include “Opcodes.h”

class Example : public CreatureScript
{
public:
Example() : CreatureScript(“Example”)
{
Player* player = handler->GetSession()->GetPlayer();
float x = float(player->GetPositionX());
float y = float(player->GetPositionY());
float z = float(player->GetPositionZ());
float o = float(player->GetOrientation());
Map* map = player->GetMap();

Now i get compiler error that he doesnt find “handler”. I think he has no connection to the ChatHandler but how i can connect them?

I see you have no idea what you are doing.

Could you tell what you are trying to do? For example show the query and explain why you need it etc…

One key part is to know when you want the query be run exactly.

For one, you cant get a chat handler in that place of the code. Script objects shouldnt really be created after startup…

And on startup there are no players → no player coordinates & no chat handler.

Okay i tell you what i try to create.

I want an nsc or objekt which have a gossip menu. This gossip menu crawl rows from a custom mysql table which contains an id, name, mapid, x, y, z, point_mapid, point_x, point_y, point_z coordinate. So if the user click on an entry then the player will be teleport to the x, y, z coordinate from the entry. The player see only the rows which point coords are close to the player coordinates.

Table structure:

id | name | mapid | x | y | z | point_mapid | point_x | point_y | point_z

My code:

#include "ScriptPCH.h"
#include "GameEventMgr.h"
#include "ObjectMgr.h"
#include "PoolMgr.h"
#include "MapManager.h"
#include "Chat.h"
#include "Language.h"
#include "Player.h"
#include "Opcodes.h"

class ExampleScript : public CreatureScript
{
public:
    ExampleScript() : CreatureScript("ExampleScript")
    {
		Player* player = handler->GetSession()->GetPlayer();
		float x = float(player->GetPositionX());
		float y = float(player->GetPositionY());
		float z = float(player->GetPositionZ());
		float o = float(player->GetOrientation());
		Map* map = player->GetMap(); 
	
		//crawl all rows --> close to the players location
        QueryResult result = WorldDatabase.PQuery("SELECT id, name, mapid, x, y, z FROM teleport_system WHERE point_mapid = %u AND point_x BETWEEN %f AND %f AND point_y BETWEEN %f AND %f AND point_z BETWEEN %f AND %f", map->GetId(), x-10, x+10, y-10, y+10, z-5, z+5);
        
        if (result) 
        {
            do 
            {
                //crawl data
                Field* field			= result->Fetch();
                uint32 id			= field[0].GetUInt32();
                std::string name		= field[1].GetString();
                uint32 mapid                    = field[2].GetUint32();
                float telex                     = field[3].GetFloat();
                float teley                     = field[4].GetFloat();
                float telez                     = field[5].GetFloat();


                ItemInfo& data = items[id]; 
                data.id = id; 
                data.name = name;
                data.mapid = mapid;
                data.x = telex;
                data.y = teley;
                data.z = telez;
            } while (result->NextRow()); 
        }
    }

    bool OnGossipHello(Player* player, Creature* creature) OVERRIDE
    {
        for (ItemData::const_iterator it = items.begin(); it != items.end(); ++it)
        {
            player->ADD_GOSSIP_ITEM(GOSSIP_ICON_CHAT, it->second.name.c_str(), GOSSIP_SENDER_MAIN, it->second.id);
        }
        player->SEND_GOSSIP_MENU(DEFAULT_GOSSIP_MESSAGE, creature->GetGUID());
        return true; 
    }

    bool OnGossipSelect(Player* player, Creature* creature, uint32 sender, uint32 action) OVERRIDE
    {
        player->PlayerTalkClass->ClearMenus();
        if (sender == GOSSIP_SENDER_MAIN && action) 
		{
		
			//player->teleportto() not ready yet
			
		}	
        OnGossipHello(player, creature);
        return true; 
    }

    struct ItemInfo
    {
        uint32 id;
        std::string name;
        uint32 mapid;
        float x;
        float y;
        float z;
    };
    typedef std::map<uint32, ItemInfo> ItemData;
    ItemData items;
};

void AddSC_ExampleScript()
{
    new ExampleScript();
}

There are 2 ways to do this.

Either query the DB every time a player talks to the gameobject, or query it on startup and search the stored data for closest points.

This queries data on startup and then stores it.

Later it loops through the entries when needed and uses a function called IsNear to test the distances.

#include "ScriptPCH.h"
#include "GameEventMgr.h"
#include "ObjectMgr.h"
#include "PoolMgr.h"
#include "MapManager.h"
#include "Chat.h"
#include "Language.h"
#include "Player.h"
#include "Opcodes.h"

class ExampleScript : public CreatureScript
{
public:
    ExampleScript() : CreatureScript("ExampleScript")
    {
        //crawl all rows --> close to the players location
        QueryResult result = WorldDatabase.Query("SELECT id, name, mapid, x, y, z, point_mapid, point_x, point_y, point_z FROM teleport_system");

        if (result)
        {
            do
            {
                Field* field = result->Fetch();

                uint32 id = field[0].GetUInt32();
                std::string name = field[1].GetString();

                uint32 mapid = field[2].GetUInt32();
                float x = field[3].GetFloat();
                float y = field[4].GetFloat();
                float z = field[5].GetFloat();

                uint32 pmapid = field[6].GetUInt32();
                float px = field[7].GetFloat();
                float py = field[8].GetFloat();
                float pz = field[9].GetFloat();

                ItemInfo& data = items[id];
                data.name = name;
                data.tele = WorldLocation(mapid, x, y, z, 0);
                data.point = WorldLocation(pmapid, px, py, pz, 0);
            } while (result->NextRow());
        }
    }

    bool IsNear(WorldLocation* sender, const WorldLocation* point) // I decided to use the gossip sender here instead of player to get more static values
    {
        if (sender->GetMapId() != point->GetMapId()) // check map
            return false;
        if (!point->IsInDist2d(sender, 10.0f)) // check x, y
            return false;
        if (abs(point->GetPositionZ() - sender->GetPositionZ()) > 5.0f) // check z
            return false;
        return true;
    }

    bool OnGossipHello(Player* player, Creature* creature) OVERRIDE
    {
        for (ItemData::const_iterator it = items.begin(); it != items.end(); ++it)
        {
            if (!IsNear(creature, &it->second.point)) // check if can show point
                continue;
            player->ADD_GOSSIP_ITEM(GOSSIP_ICON_CHAT, it->second.name, GOSSIP_SENDER_MAIN, it->first);
        }
        player->SEND_GOSSIP_MENU(DEFAULT_GOSSIP_MESSAGE, creature->GetGUID());
        return true;
    }

    bool OnGossipSelect(Player* player, Creature* creature, uint32 sender, uint32 action) OVERRIDE
    {
        player->PlayerTalkClass->ClearMenus();
        if (sender == GOSSIP_SENDER_MAIN) // dont check action, it can be 0
        {
            ItemData::const_iterator it = items.find(action); // find item data
            if (it != items.end()) // check that data exists
                if (IsNear(creature, &items[action].point)) // check that check location is (still) valid
                    player->TeleportTo(items[action].tele);
        }
        OnGossipHello(player, creature);
        return true;
    }

    struct ItemInfo
    {
        std::string name;
        WorldLocation tele; // location to teleport to
        WorldLocation point; // the check location
    };
    typedef std::map<uint32, ItemInfo> ItemData;
    ItemData items;
};

void AddSC_ExampleScript()
{
    new ExampleScript();
}

Mhm i need Option 1 because i want to change for example the name or tele coords on the fly without a server restart. So i must execute the query every time when a player open the menu (i have <20 players online so its no problem).

So i must move the query and fetch area to

bool OnGossipHello(Player* player, Creature* creature) OVERRIDE

?

Sorry first time with the gossip menu interface…

Or you could just use a reload command … or similar. But ok…

#include "ScriptPCH.h"

class ExampleScript : public CreatureScript
{
public:
    ExampleScript() : CreatureScript("ExampleScript")
    {
    }

    bool OnGossipHello(Player* player, Creature* creature) OVERRIDE
    {
        float x, y, z;
        creature->GetPosition(x, y, z);
        QueryResult result = WorldDatabase.PQuery("SELECT id, name FROM teleport_system WHERE"
            "point_mapid = %u AND point_x BETWEEN %f AND %f AND point_y BETWEEN %f AND %f AND point_z BETWEEN %f AND %f",
            creature->GetMapId(), x - 10, x + 10, y - 10, y + 10, z - 5, z + 5);

        if (!result)
        {
            player->GetSession()->SendNotification("No teleport locations available");
            return true;
        }

        do
        {
            Field* field = result->Fetch();
            uint32 id = field[0].GetUInt32();
            std::string name = field[1].GetString();
            player->ADD_GOSSIP_ITEM(GOSSIP_ICON_CHAT, name, GOSSIP_SENDER_MAIN+1, id);
        } while (result->NextRow());
        player->SEND_GOSSIP_MENU(DEFAULT_GOSSIP_MESSAGE, creature->GetGUID());
        return true;
    }

    bool OnGossipSelect(Player* player, Creature* creature, uint32 sender, uint32 action) OVERRIDE
    {
        player->PlayerTalkClass->ClearMenus();

        if (sender == GOSSIP_SENDER_MAIN+1) // dont check action, it can be 0
        {
            float x, y, z;
            creature->GetPosition(x, y, z);
            QueryResult result = WorldDatabase.PQuery("SELECT mapid, x, y, z FROM teleport_system WHERE"
                "point_mapid = %u AND point_x BETWEEN %f AND %f AND point_y BETWEEN %f AND %f AND point_z BETWEEN %f AND %f AND id = %u LIMIT 1",
                creature->GetMapId(), x - 10, x + 10, y - 10, y + 10, z - 5, z + 5, action);

            if (result)
            {
                Field* field = result->Fetch();
                uint32 map = field[0].GetUInt32();
                float x = field[1].GetFloat();
                float y = field[2].GetFloat();
                float z = field[3].GetFloat();
                player->TeleportTo(map, x, y, z, 0);
            }
        }
        OnGossipHello(player, creature);
        return true;
    }
};

void AddSC_ExampleScript()
{
    new ExampleScript();
}