Jump to content

mySQL | insert data into a table with info


Cronoss

Recommended Posts

 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)

 

Link to comment

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 by Burak5312
  • Thanks 1
Link to comment

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
  
Link to comment
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 by Burak5312
  • Thanks 1
Link to comment

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 by Cronoss
Link to comment

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 by Burak5312
  • Like 1
Link to comment

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 by Burak5312
Link to comment

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 by Cronoss
  • Like 1
Link to comment
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 by Burak5312
  • Like 1
Link to comment

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...