database

[2013martin1212]

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

[pa3ck]

How do you save the position in the table? Show us your table structure

[2013martin1212]

there is my db

[3aGl3]

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

 

[2013martin1212]

nice script but i get confused  my script is diffrent and i am new scripter but i really want to learn and learn to be a pro programer  can you help me ?? dont say it mta wiki because i try it but i dont no which goes which :*( I need a teacher

[pa3ck]

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