Jump to content

database


Recommended Posts

i try to update and set player data from the database but i get confused :(

function loginReguest(player,username,password)
	local check = exports.mysql:_QuerySingle("SELECT * FROM users WHERE username = ? " ,username)

	if check then
		local checkPass = check.password
		if (checkPass == password) then
			logIn(player,getAccount(username),getAccount(password))
			triggerClientEvent(player,"showHide", getRootElement())
			outputDebugString("User Logged In")
			
		end
	end
end
addEvent("logIn",true)
addEventHandler("logIn",getRootElement(), loginReguest)

i dont no how i can be get the data from mysql and spawnPlayer :) please someone give me some information

Link to comment

First of all let's start with some very basic things about a login system. Please for the love of your users, do NOT save passwords in cleartext. MTA provides the sha256 as well as hash functions, make use of them. Also get a random salt when the user registers and also store that in your database. Every other way of storing passwords is completely insecure.

So let's get to your question now that we talked about that.

1. When a player logs in he will select a character, at that point you want to load the characters data from the table

2. When a player logs out you will want to save the data to the database again, also it's probably a good idea to do it when he changes dimensions, ints etc. just in case something unexpected happens

Here is an example:

After selecting a character to spawn the player

function playerSpawn( player )
	-- check if the player is actually logged in
	if not player.account or player.account.guest then
		outputDebugString( "Player is not logged in when trying to spawn.", 1 )
		return
	end

	-- get a list of characters related to the account
	local pdata = databaseQueryRaw( "SELECT * FROM `player` WHERE `account`=? ORDER BY `player_id` ASC;", player.account.name )

	if pdata then
		-- get the selected character
		local c = player.account:getData( "character" )
		
		-- pick the correct character or fall back
		if not c or not pdata[tonumber(c)] then
			pdata = pdata[1]
		else
			pdata = pdata[c]
		end
		
		-- get the players team
		local team = getTeamFromName( LOC_team_player )
		if isObjectInACLGroup( "user.".. player.account.name, aclGetGroup( "Admin" ) ) then		-- ToDo: We might want a better way to find teammember
			team = getTeamFromName( LOC_team_admin )
		end
		
		-- set the players object ID
		player.id = PLAYER_ID_PREFIX .. "-" .. pdata.player_id
		
		-- get the skin based on the character and gender
		local skin = getCharacterSkin( pdata.gender, pdata.char )
		
		if pdata.pos_x and pdata.pos_y and pdata.pos_z and pdata.pos_r and pdata.int and pdata.dim then
			-- spawn the player at his last know position
			spawnPlayer( player, pdata.pos_x, pdata.pos_y, pdata.pos_z, pdata.pos_r, skin, pdata.int, pdata.dim, team )
		else
			local spawnpoints = getElementsByType( "spawnpoint" )
			if spawnpoints and #spawnpoints > 0 then		-- if we found spawnpoints
				-- select a random spawnpoint
				local spawnpoint = spawnpoints[math.random(#spawnpoints)]
				local pos_x, pos_y, pos_z = getElementPosition( spawnpoint )
				local pos_r = getElementRotation(spawnpoint) or 0
				local int = getElementInterior( spawnpoint ) or 0
				local dim = getElementDimension( spawnpoint ) or 0
				
				-- spawn the player at that position
				spawnPlayer( player, pos_x, pos_y, pos_z, pos_r, skin, int, dim, team )
			else
				outputDebugString( "No spawnpoint found, falling back.", 2 )
				spawnPlayer( player, fallback.x, fallback.y, fallback.z, 0, skin, pdata.int, pdata.dim, team )
			end
		end
	else
		-- open the character creation
		clientCall( player, "openCharacterCreation" )
		return
	end

	-- focus the clients camera and show his HUD
	setCameraTarget( player, player )
	setPlayerHudComponentVisible( player, "all", true )
end

Note that in the example I was storing the character in an account data previously.

To save the location:

function playerLocationSave( player, account )
	account = account or player.account

	-- make sure the account is valid
	if not account or account.guest then
		outputDebugString( "'playerLocationSave' called on player with an invalid account.", 1 )
		return
	end

	-- gather all the info about the player that has to be saved
	local pos_x, pos_y, pos_z = getElementPosition( player )
	local rot_x, rot_y, rot_z = getElementRotation( player )
	local dim = getElementDimension( player )
	local int = getElementInterior( player )

	-- get a list of characters related to the account
	local chars = databaseQueryRaw( "SELECT * FROM `player` WHERE `account`=? ORDER BY `player_id` ASC;", player.account.name )
	
	if chars then
		-- try to get the selected character
		local c = player.account:getData( "character" )

		-- pick the correct character or fall back
		if not c or not chars[tonumber(c)] then
			chars = chars[1]
		else
			chars = chars[c]
		end

		-- save the data to the database
		databaseQueryRaw( "UPDATE `player` SET `pos_x`=?, `pos_y`=?, `pos_z`=?, `pos_r`=?, `dim`=?, `int`=? WHERE `player_id`=?", pos_x, pos_y, pos_z, rot_z, dim, int, chars.player_id  )
	end
end

 

Link to comment

When you have two tables and the data is normalized in some way, you have to join the two table, right? You will need a primary key in the users table (for example userId) and a foreign key in the characters table which is same as the userId in the users table. If you have no idea what I'm taking about, go and check out a youtube tutorial about foreign key and primary key joins. Example:

SELECT usr.SomethingFromUserTable, chars.SomethingFromCharacterTable
FROM users as usr
INNER JOIN on characters as chars ON
usr.userId = chars.userId
WHERE usr.userName = guiGetText(txtUsername) // obviously you can't have client side code (guiGetText) on server side, you will need to trigger this value from client

 

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...