[SOLVED] Gossip hearthstone

Unknown96 · May 25, 2015

Hello so I was working on this idea I had but ran into issues when I encountered something that I actually never learned, the issue is this.

I want to get a column value from a sql table I made but [Eluna Query] does not say much to me at all since I'm not that deep in knowledge within sql. However I think I got the rest figured out. I can really use some assistance with this since I myself have no clue in this department.

My table

Code:

CREATE TABLE `hearthstone` (
	`guid` SMALLINT(6) NOT NULL,
	`map` SMALLINT(6) NOT NULL,
	`x` BIGINT(20) NOT NULL,
	`y` BIGINT(20) NOT NULL,
	`z` BIGINT(20) NOT NULL,
	`o` BIGINT(20) NOT NULL
)
COMMENT='Second Hearthstone'
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB

[Eluna Script] - (I struggle at line 32!)

Thanks in beforehand, best regards, Unknown96!

Tommy · May 25, 2015

character_homebind table already exists for this purpose. Why not use SetBindPoint( x, y, z, mapId, areaId )? It does exactly what you want, but without you having to make a separate table because hearthstone location is already saved in character_homebind.

Unknown96 · May 25, 2015

Tommy said:
character_homebind table already exists for this purpose. Why not use SetBindPoint( x, y, z, mapId, areaId )? It does exactly what you want, but without you having to make a separate table because hearthstone location is already saved in character_homebind.

That would be an great idea and I actually didn't even know about that command but however I do not wish to overwrite the original Hearthstone, just add another because I know for a fact from many years of wow experience that I always wish I had another hearhtstone so I'm kinda just trying to make a second one

Rochet2 · May 25, 2015

You may run into problems with using gossip menus with a single clickable option.
Try using more options in menus if you are having issues.

See this:

Code:

CREATE TABLE `hearthstone` (
	`guid` INT UNSIGNED NOT NULL,
	`map` INT UNSIGNED NOT NULL,
	`x` FLOAT NOT NULL,
	`y` FLOAT NOT NULL,
	`z` FLOAT NOT NULL,
	`o` FLOAT NOT NULL,
	PRIMARY KEY (`guid`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB;

Code:

local command = "menu" -- Usage: .menu

local function Hello(event, player)
    player:GossipClearMenu()
    player:GossipMenuAddItem(0, "Hearthstone", 0, 1)
    player:GossipSendMenu(1, player, 100)
end

local function OnSelect(event, player, _, _, intid, code)
    player:GossipClearMenu()

    local guid = player:GetGUIDLow()
    local Q = CharDBQuery("SELECT * FROM hearthstone WHERE `guid` = "..guid)

    if (intid == 1) then
        if (not Q) then
            player:GossipMenuAddItem(0, "Save my location", 0, 9)
        else
            player:GossipMenuAddItem(0, "Teleport me", 0, 10)
        end
        player:GossipSendMenu(1, player, 100)
    elseif (intid == 9) then
        local map,x,y,z,o = player:GetMapId(), player:GetLocation()
        CharDBExecute("REPLACE INTO hearthstone (`guid`, `map`, `x`, `y`, `z`, `o`) VALUES ("..guid..", "..string.format("%u, %f, %f, %f, %f", map,x,y,z,o)..")")
        player:SendAreaTriggerMessage("Your location have been stored!")
        player:GossipComplete()
    elseif (intid == 10) then
        player:GossipComplete()
        if (Q) then
            local map,x,y,z,o = Q:GetUInt32(1), Q:GetFloat(2), Q:GetFloat(3), Q:GetFloat(4), Q:GetFloat(5)
            player:Teleport(map,x,y,z,o)
        end
    end
end

local function OnCommand(event, player, cmd)
    if (cmd:lower() == command) then
        Hello(event, player)
    end
end

RegisterPlayerEvent(42, OnCommand)
RegisterPlayerGossipEvent(100, 2, OnSelect)

I suggest to query the data on startup from DB and store it to a lua table.
Then update the data to the lua table and to the DB with DB execute.

Code:

local command = "menu" -- Usage: .menu

local hearthstone = {}

do
    local Q = CharDBQuery("SELECT * FROM hearthstone")
    if (Q) then
        repeat
            local data = Q:GetRow()
            hearthstone[data.guid] = data
        until not Q:NextRow()
    end
end

local function Hello(event, player)
    player:GossipClearMenu()
    player:GossipMenuAddItem(0, "Hearthstone", 0, 1)
    player:GossipSendMenu(1, player, 100)
end

local function OnSelect(event, player, _, _, intid, code)
    player:GossipClearMenu()

    local guid = player:GetGUIDLow()

    if (intid == 1) then
        if (not hearthstone[guid]) then
            player:GossipMenuAddItem(0, "Save my location", 0, 9)
        else
            player:GossipMenuAddItem(0, "Teleport me", 0, 10)
        end
        player:GossipSendMenu(1, player, 100)
    elseif (intid == 9) then
        local map,x,y,z,o = player:GetMapId(), player:GetLocation()
        hearthstone[guid] = {guid=guid,map=map,x=x,y=y,z=z,o=o}
        CharDBExecute("REPLACE INTO hearthstone (`guid`, `map`, `x`, `y`, `z`, `o`) VALUES ("..guid..", "..string.format("%u, %f, %f, %f, %f", map,x,y,z,o)..")")
        player:SendAreaTriggerMessage("Your location have been stored!")
        player:GossipComplete()
    elseif (intid == 10) then
        player:GossipComplete()
        local data = hearthstone[guid]
        if (data) then
            player:Teleport(data.map,data.x,data.y,data.z,data.o)
        end
    end
end

local function OnCommand(event, player, cmd)
    if (cmd:lower() == command) then
        Hello(event, player)
    end
end

RegisterPlayerEvent(42, OnCommand)
RegisterPlayerGossipEvent(100, 2, OnSelect)

slp13at420 · May 26, 2015

basically you want to be able to store 2 homebinds for players, using a chat command for the second one like this:

Code:

[COLOR="#808080"]
local ChatMsg1 = "#Menu";
local ChatMsg2 = "#menu";
local HearthStone = {};

local function LoadLocations()

local Q = CharDBQuery("SELECT * FROM characters.hearthstone;") -- gathers the data from each row of the sql table

	if(Q)then
		
		repeat
			HearthStone[Q:GetUInt32(0)] = {
						map = Q:GetUInt32(1),
						x = Q:GetFloat(2),
						y = Q:GetFloat(3),
						z = Q:GetFloat(4),
						o = Q:GetFloat(5),
			};
			
		until not Q:NextRow();
	end
end

LoadLocations(); -- starts the loading function

local function UpdateLoc(key,...)

local KEY = {
		[1] = "INSERT INTO characters.hearthstone SET `guid`='%s';", -- creates a new entry
		[2] = "UPDATE characters.hearthstone SET `%s`='%s' WHERE `%s`='%s';", -- updates an existing entry
	};
	
	if(key == 1)then -- used for new entry creation
	
		local guid = ...;
		local qs = string.format(KEY[key], guid);
		WorldDBQuery(qs);
		HearthStone[guid] = {map = nil, x = nil, y = nil, z = nil, o = nil,};
	end

	if(key == 2)then -- used for updating an existing entry
	
		local a,b,c,d,e = ...;

		local qs = string.format(KEY[key],a,b,c,d);
		WorldDBQuery(qs);
		HearthStone[d][a] = b;
	end
end

local function FirstLogin(event, player)

local guid = player:GetGUIDLow();

	if not(HearthStone[guid])then
	
		local map,x,y,z,o = player:GetMapId(),player:GetLocation();
		
		UpdateLoc(1,guid); -- key 1 pushes new entry data to UpdateLoc function
		UpdateLoc(2,"map",map,"guid",guid); -- key 2 Pushes update data to UpdateLoc function
		UpdateLoc(2,"x",x,"guid",guid); -- key 2 Pushes update data to UpdateLoc function
		UpdateLoc(2,"y",y,"guid",guid); -- key 2 Pushes update data to UpdateLoc function
		UpdateLoc(2,"z",z,"guid",guid); -- key 2 Pushes update data to UpdateLoc function
		UpdateLoc(2,"o",o,"guid",guid); -- key 2 Pushes update data to UpdateLoc function
	end
end

local function Login(event, player) -- normal login

local guid = player:GetGUIDLow();

	if not(HearthStone[guid].map)then -- to verify no null data
		
		FirstLogin(30, player); -- if null data then re-push the data
	end
end

local function Hello(event, player)
 player:GossipClearMenu();
 player:GossipMenuAddItem(0, "Hearhstone", 0, 1);
 player:GossipSendMenu(1, player, 100);
end


local function OnSelect(event, player, unit, sender, intid, code)

local guid = player:GetGUIDLow();
local map,x,y,z,o = player:GetMapId(),player:GetLocation();


 if(intid == 1)then -- HearthStone sub-menu
 	player:GossipClearMenu();
	player:GossipMenuAddItem(0, "Save my location", 0, 9)  
	player:GossipMenuAddItem(0, "Teleport me", 0, 10)
	player:GossipSendMenu(1, player, 100);
 end
 
	if(intid == 9) then -- stores current coordinates
		UpdateLoc(2,"map",map,"guid",guid);
		UpdateLoc(2,"x",x,"guid",guid);
		UpdateLoc(2,"y",y,"guid",guid);
		UpdateLoc(2,"z",z,"guid",guid);
		UpdateLoc(2,"o",o,"guid",guid);
	                player:GossipComplete();
		player:SendAreaTriggerMessage("Your location has been stored.")
		player:SendBroadcastMessage("Home Bind location updated.")
	end

	if(intid == 10) then -- teleports to stored coordinates
		player:Teleport(HearthStone[guid].map, HearthStone[guid].x, HearthStone[guid].y, HearthStone[guid].z, HearthStone[guid].o)
		player:GossipComplete();
	end
end

local function Chat(event, player, msg, lang, type)

	if(msg == ChatMsg1)or(msg == ChatMsg2)then
	
		Hello(event, player)
	end
end


RegisterPlayerEvent(30, FirstLogin) 
RegisterPlayerEvent(3, FirstLogin) 
RegisterPlayerEvent(18, Chat)
RegisterPlayerGossipEvent(100, 2, OnSelect)
[/COLOR]

on first Logon it will create a new entry using the chars guid as an unique key in the db.
it will update the gps when selected and teleport when selected.

-- updated --
ugh Rochet2 beat me too it lol

oh yea you need to change your x,y,z,o columns from BIGINT to float in the DB table.

Rochet2 said:

Code:

CREATE TABLE `hearthstone` (
	`guid` INT UNSIGNED NOT NULL,
	`map` INT UNSIGNED NULL,
	`x` FLOAT NULL,
	`y` FLOAT NULL,
	`z` FLOAT NULL,
	`o` FLOAT NULL,
	PRIMARY KEY (`guid`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB;

Code:

local command = "menu" -- Usage: .menu
[/QUOTE]
I left them allowing null so I could allow the error check during normal login.

n mine uses a PreparedStatement type query :P

what this one does is loads the rows during startup and then only updates when needed with a PreparedStatement type function.
with the values loaded into a table at startup , it doesn't need to query the sql DB every time the player uses the command causing lag.

Rochet2 · May 26, 2015

[MENTION=717]slp13at420[/MENTION] The bad thing about your script now is that you do 5~6 times SQL queries.
Btw, have you tested what happens with calling GetFloat on a NULL column? I actually dont know and I suspect it returns 0, junk or crashes.
Interesting that %s works with numbers etc on string format O,o

PS. Could also load the player's data on login and discard it on logout to avoid loading extra data for nothing.

Unknown96 · May 26, 2015

Thank you all for the responses, I am very happy with the assistance I was given!

[SOLVED] Gossip hearthstone

Unknown96

Exalted Member

Tommy

Founder

Unknown96

Exalted Member

Rochet2

Moderator / Eluna Dev

slp13at420

Mad Scientist

Rochet2

Moderator / Eluna Dev

Unknown96

Exalted Member