mySQL | insert data into a table with info

[Cronoss]

 I've been trying to save the position of the player's character and it saves... but in a new table, not in the main one where its the account's name, the character's name and the rest.

-> This is what happens<-

 

This is the code I'm using for the "character money, position, and health" // i translated some concepts to english so you can understand easly what I'm trying to make:

(server-side)

function saveDataCharacter()
		local x, y, z = getElementPosition(source)
		local money = getPlayerMoney(source)
		local health = getElementHealth(source)
		local sendInfo = exports.mysql:_Exec("INSERT INTO characters (x,y,z, money, health) VALUES (?,?,?,?,?)", x, y, z, money, health)
		if (sendInfo) then
			iprint("Data saved")
		else
			iprint("Error saving")
		end
	end
end
addEventHandler("onPlayerQuit", getRootElement(), saveDataCharacter)

 

[Burak5312]

You can use UPDATE for this, but make sure the player always has a column in the database. You can use insert once before.

do it with a function in your code that uses dbQuery instead of exec because you're using exports I'm not sure

function saveDataCharacter()
	local playerAccount = getPlayerAccount(source) -- get account of player who exited game
	if(playerAccount) then
       if(isGuestAccount(playerAccount)) then return end -- if guest account don't continue
	end
	local x, y, z = getElementPosition(source)
	local money = getPlayerMoney(source)
	local health = getElementHealth(source)
	                               --Use dbQuery instead of _Exec                                      --name I'm not sure of the player's account name?
	local sendInfo = exports.mysql:_Exec("UPDATE characters SET x=?, y=?, z=?, money=?, health=? WHERE name=?", x, y, z, money, health, playerAccount)
	if (sendInfo) then
		iprint("Data saved")
	else
		iprint("Error saving")
	end
end
addEventHandler("onPlayerQuit", getRootElement(), saveDataCharacter)

 

Edited February 13, 2022 by Burak5312

[Cronoss]

This is the code in "mysql" resource, I followed a tutorial, so I don't know if this works for something like this: 

function _Query( ... )
	if connection then
		local query = dbQuery(connection, ... )
		local result = dbPoll(query,-1)
		return result
	else
		return false
	end
end

function _QuerySingle(str, ...)
	if connection then
		local result = _Query(str, ...)
		if type(result) == 'table' then
			return result[1]
		end
	else
		return false
	end
end

 

 

I get it what you did, but now...how I could call the data from that table later? and set all the parameters that I put in it? something like this:

function setData()
local playerAccount = getPlayerAccount(source) --
if(playerAccount) then
     if(isGuestAccount(playerAccount)) then 
      	return
     end
end
	local setData = exports.mysql:_Query( ? )
  	setElementHealth = blabla
  	setPlayerMoney = blabla2
  

[Burak5312]

I'm not sure, but you can do it this way, the onPlayerLogin event is 
suitable for this job, you can transfer the data to the game when the player logs in

function setData()
     local playerAccount = getPlayerAccount(source)
     if(playerAccount) then
        if(isGuestAccount(playerAccount)) then 
      	return
        end
     end
     local accName = getAccountName(playerAccount) -- get player account name
	local setData = exports.mysql:_Query("SELECT * FROM characters WHERE name=? LIMIT 1", accName) -- Retrieve all column data with account name accName
     if(setData) then --if successful?
        if(#setData > 0) then -- if table size is greater than 0
           for _,column in ipairs(setData) do
               setPlayerMoney(source, column["money"]) --get player's money from mysql and set Using: column["column name"]
               break --leave the for loop immediately when it has set all the data
           end
        end
     end
end

Edited February 13, 2022 by Burak5312

[Cronoss]

I think It worked, but how I could set the player position if I put x, y, z as separated parameters in the table and I want to callback the position?

This is what I made and didn't work

setElementPosition(source, column["x"], column["y"], column["z"])

 

[Burak5312]

hmm can you try to delete the break command

for _,column in ipairs(setData) do
    setPlayerMoney(source, column["money"]) --get player's money from mysql and set Using: column["column name"]
end

 

[Cronoss]

I just figured out that the table doesn't uptade the data, that's why x,y,z doesn't work correctly

 

I think the problem it's here:

function recordarData()
local playerAccount = getPlayerAccount(source)
	if(playerAccount) then
       if(isGuestAccount(playerAccount)) then return end 
	end
	local x, y, z = getElementPosition(source)
	local money = getPlayerMoney(source)
	local health = getElementHealth(source)
	local dimension = getElementDimension(source) 
	local interior = getElementInterior(source)
	                               --Use dbQuery instead of _Exec                                     
	local sendInfo = exports.mysql:_Query("UPDATE characters SET x=?, y=?, z=?, money=?, health=?, dimension=?, interior=?, WHERE cuenta=?", x, y, z, money, health, dimension, interior, playerAccount)
	if (sendInfo) then
		iprint("Data saved")
	else
		iprint("Error saving")
	end
end
addEventHandler("onPlayerQuit", getRootElement(), recordarData)

Because I noticed the table get info but from the previus function:

 

And not from the "uptade" one

function datosAgregar(player, nombre, sexo, edadPJ2, nacionalidadPJ2)
	local account = getPlayerAccount(source)
	local accname = getAccountName(account)
	local cuenta = tostring(consulta)
	setPlayerMoney(source, 9000)
	setPlayerName(source, tostring(nombre))
	local x, y, z = getElementPosition(source)
	local money = getPlayerMoney(source)
	local health = getElementHealth(source)
	local dimension = getElementDimension(source) 
	local interior = getElementInterior(source)
	local addCharacter = exports.mysql:_Exec("insert into characters (name, gender, age, nation, cuenta, x, y, z, money, health, dimension, interior) values (?,?,?,?,?,?,?,?,?,?,?,?)", nombre, sexo, edadPJ2, nacionalidadPJ2, accname, x, y, z, money, health, dimension, interior)
	if (addCharacter) then
		iprint(" data saved ")

 

Edited February 13, 2022 by Cronoss

[Burak5312]

you don't get the account name, you compare it directly with the player's account, for this, get the account name with getAccountName

in the same way, change the playerAccount to accName in the query you sent.

function recordarData()
local playerAccount = getPlayerAccount(source)
     if(playerAccount) then
       if(isGuestAccount(playerAccount)) then return end 
     end
     local x, y, z = getElementPosition(source)
     local money = getPlayerMoney(source)
     local health = getElementHealth(source)
     local dimension = getElementDimension(source) 
     local interior = getElementInterior(source)
     local accName = getAccountName(playerAccount) --get player's account name to compare in mysql
                                    --Use dbQuery instead of _Exec                                                                                                                        --replace this with account name
     local sendInfo = exports.mysql:_Query("UPDATE characters SET x=?, y=?, z=?, money=?, health=?, dimension=?, interior=?, WHERE cuenta=?", x, y, z, money, health, dimension, interior, accName)
     if (sendInfo) then
          iprint("Data saved")
     else
          iprint("Error saving")
     end
end
addEventHandler("onPlayerQuit", getRootElement(), recordarData)

 

Edited February 13, 2022 by Burak5312

[Cronoss]

Now the console send me an error "dbPoll failed" when I quit to save the data

mysql resource:

function _Query( ... )
	if connection then
		local query = dbQuery(connection, ... )
		local result = dbPoll(query,-1) -------here
		return result
	else
		return false
	end
end

 

[Burak5312]

I'm not sure, but can you try to delete the comma at the end of the interior in the query?

                                                                                                               --here
local sendInfo = exports.mysql:_Query("UPDATE characters SET x=?, y=?, z=?, money=?, health=?, dimension=?, interior=? WHERE cuenta=?", x, y, z, money, health, dimension, interior, accName)

 

Edited February 13, 2022 by Burak5312

[Cronoss]

That was the problem, thank you Burak. And also... what's the difference between making the connection like this way: 

 exports.mysql:_Query("INSERT blablablalba")

And this way:

dbQuery(connection, "INSERT blabla")

I'm just asking because I don't know if I'm using correctly the function

Edited February 13, 2022 by Cronoss

[Burak5312]

9 hours ago, Cronoss said:

That was the problem, thank you Burak. And also... what's the difference between making the connection like this way: 

 exports.mysql:_Query("INSERT blablablalba")

And this way:

dbQuery(connection, "INSERT blabla")

I'm just asking because I don't know if I'm using correctly the function

INSERT creates a column from scratch in mysql, you can add a new column using this when the player is new registration

The _Query function can be a special function where the result is returned in a single function to write less code. There is no difference between the two.

Edited February 14, 2022 by Burak5312