Simple MySQL query.

[TrickyTommy]

Hi. I wan't to make a mini account system, but don't really know, how. I already can insert stuff to my database, but can't check if they exist from game, but php my admin.

This is how my script looks like:

Connection = dbConnect( "mysql", "dbname=texas;host=127.0.0.1", "root", "", "share=1" )

if Connection then
	outputServerLog ("Connection succeeded!")
else
	outputServerLog ("Connection failure!")
end

function Insert(Client, Command, Username, Password)
	if Password then
		local Query = dbQuery(Connection, "INSERT INTO users (username, password) VALUES (?, ?)", Username, Password)
		if Query then
			outputDebugString ("Succesful query!")
		else
			outputDebugString ("Unsuccesful query!")
		end
	end
end
addCommandHandler ("insert", Insert)

function Call(Client, Command, Username, Password)
	if Password then
		--If username and password exists in database, notify the user that given Username and Password parameter is correct.
	end
end
addCommandHandler ("call", Call)

In function named "Call" i want a very simple thing: there are 2 parameters inside, Username and Password. If there is a row, in wich the password and username is the same as the parameter, i want it to like output a message, else make the user aware that the given details are wrong.

Please, be so kind and help me, because i have no idea how should i do it, + add comments if possible.

[GodOfPenguins]

function Call(Client, Command, Username, Password)
	local query = dbQuery(Connection, "SELECT `username`, `password` FROM users WHERE username = '"..Username.."' AND password = '"..Password.."';")
	local result = dbPoll(query, -1)
	if result > 0 then
		-- Match found success
	end
end

 

Edited October 14, 2017 by Taka

[TrickyTommy]

and for example how can i output all the data, that exists in the username coloumn?

[GodOfPenguins]

function Call(Client, Command, Username, Password)
	local query = dbQuery(Connection, "SELECT * FROM users WHERE username = '"..Username.."' AND password = '"..Password.."';")
	local result = dbPoll(query, -1)
	if result > 0 then
		-- Match found success
		for row, rowData in ipairs(result) do
			-- rowData['column name']
			outputChatBox(rowData['username'])
			outputChatBox(rowData['password'])
		end
	end
end

 

[TrickyTommy]

i'll try it out asap, big thanks.

[TrickyTommy]

if Result > 0 then

Debugscript said that "attempt to compare number with table" what is wrong?

Edited October 15, 2017 by TrickyTommy

[GodOfPenguins]

2 hours ago, TrickyTommy said:

if Result > 0 then

Debugscript said that "attempt to compare number with table" what is wrong?

Oops.

function Call(Client, Command, Username, Password)
	local query = dbQuery(Connection, "SELECT * FROM users WHERE username = '"..Username.."' AND password = '"..Password.."';")
	local result, num_rows = dbPoll(query, -1)
	if num_rows > 0 then
		-- Match found success
		for row, rowData in ipairs(result) do
			-- rowData['column name']
			outputChatBox(rowData['username'])
			outputChatBox(rowData['password'])
		end
	end
end

 

[aka Blue]

#result to get length of table.

[TrickyTommy]

and where should i put the # before result? could you please write it into the code and post it here?

[aka Blue]

Just use if #result > 0 

 

[TrickyTommy]

thank you guys, i think i have one last question. what method should i use for getting the player's account id from database, and after set it as his element data?

[GodOfPenguins]

1 hour ago, TrickyTommy said:

thank you guys, i think i have one last question. what method should i use for getting the player's account id from database, and after set it as his element data?

function Call(Client, Command, Username, Password)
	local query = dbQuery(Connection, "SELECT * FROM users WHERE username = '"..Username.."' AND password = '"..Password.."';")
	local result = dbPoll(query, -1)
	if #result > 0 then
		-- Match found success
		for row, rowData in ipairs(result) do
			setElementData(Client, "id", tonumber(rowData['id']))
		end
	end
end

 

[TrickyTommy]

you guys helped me to make my very first account system with gui! i am very thankful.