Database related question

[areoslasher]

So hi guys, I've been lately working on some scripts just for fun to get familiar with this concept of creating my own resources and now I would like to ask you if someone could explain to me how can I get data from a database and then compare that data with let's say something like account name of a player. Yes, I know I'm supposed to use dbQuerry and dbPoll but I just can't get it to work, so if you'd be so kind to show me in a simple example how to compare the two I'd be really grateful. So let's say the problem is how can I compare user account name and maybe a ownerName(of a house perhaps or a car) which is stored in my database

[nxFairlywell]

You should use 'executeSQLQuery' in the beginning. This will be fine for your start

Let's see how it works..

In the following example, We have 4 values to save them in the Database.

1. the user's account name
2. the user's money
3. the user's car ID
4. the user's custom points

Now we going to create a table in local Database and it's name will be "aeroTable" :

addEventHandler("onResourceStart",getThisResource(),
	function()
		executeSQLQuery("CREATE TABLE IF NOT EXIST `aeroTable`")
	end
)

Done! We have created an aeroTable. But, We haven't created the columns that we need to save the values in them!!

Let's make some columns :

addEventHandler("onResourceStart",getThisResource(),
	function()
		executeSQLQuery("CREATE TABLE IF NOT EXIST `aeroTable` (account_name TEXT, money TEXT, carID TEXT, customPoints TEXT)")
	end
)

Okay, Now we have created the columns, and the names have been as we named them before.

Now we going to save the values in this columns ..

function SaveValues(accountName,money,carID,customPoints)
	if accountName and type(accountName)=="string" and getAccount(accountName) then
		local SQL = executeSQLQuery("SELECT * FROM aeroTable WHERE account_name=?",accountName);
		if SQL and type(SQL)=="table" and #SQL > 0 then
			executeSQLQuery("UPDATE aeroTable SET money=?, carID=?, customPoints=?",money,carID,customPoints);
			print(accountName.." In Database updated!");
		else -- There are no values, that means the account_name column is empty and this account we sent it has no values in the Database, So we have to set the values.
			executeSQLQuery("INSERT INTO aeroTable(account_name,money,carID,customPoints) VALUES(?,?,?,?)",accountName,money,carID,customPoints);
			print(accountName.." Has been successfully added to the aeroTable");
		end
		return true;
	end
	return false;
end

And we going to add a command for testing :

addCommandHandler("SaveMe",
	function(player,cmd)
		if not isGuestAccount(getPlayerAccount(player)) then
			local accountName = getAccountName(getPlayerAccount(player));
			if accountName then
				local money = tostring ( getPlayerMoney(player) );
				local carID;
				if getPedOccupiedVehicle(player) then
					carID = tostring( getElementModel(getPedOccupiedVehicle(player)) );
				else
					carID = "N/A";
				end
				local customPoints = getElementData(player,"points") and tostring(getElementData(player,"points")) or "0";
				SaveValues(accountName,money,carID,customPoints);
			end
		end
	end
)

Finally, We going to show the values for that user who just entered this command :  "ShowMe"

addCommandHandler("ShowMe",
	function(player,cmd)
		if not isGuestAccount(getPlayerAccount(player)) then
			local accountName = getAccountName(getPlayerAccount(player));
			if accountName then
				local SQL = executeSQLQuery("SELECT * FROM aeroTable WHERE account_name=?",accountName);
				if SQL and type(SQL)=="table" and #SQL > 0 then
					outputChatBox(SQL[1].account_name,player);
					outputChatBox(SQL[1].money,player);
					outputChatBox(SQL[1].carID,player);
					outputChatBox(SQL[1].customPoints,player);
				else
					outputChatBox("Use SaveMe to save your information",player);
				end
			end
		end
	end
)

Hope that was helpfully  Post your issues here if you don't understand. 

 

Full code :

addEventHandler("onResourceStart",resourceRoot,
	function()
		executeSQLQuery("CREATE TABLE IF NOT EXISTS aeroTable (account_name TEXT, money TEXT, carID TEXT, customPoints TEXT)")
	end
)

function SaveValues(accountName,money,carID,customPoints)
	if accountName and type(accountName)=="string" and getAccount(accountName) then
		local SQL = executeSQLQuery("SELECT * FROM aeroTable WHERE account_name=?",accountName);
		if SQL and type(SQL)=="table" and #SQL > 0 then
			executeSQLQuery("UPDATE aeroTable SET money=?, carID=?, customPoints=?",money,carID,customPoints);
			print(accountName.." In Database updated!");
		else -- There are no values, that means the account_name column is empty and this account we sent it has no values in the Database, So we have to set the values.
			executeSQLQuery("INSERT INTO aeroTable(account_name,money,carID,customPoints) VALUES(?,?,?,?)",accountName,money,carID,customPoints);
			print(accountName.." Has been successfully added to the aeroTable");
		end
		return true;
	end
	return false;
end

addCommandHandler("SaveMe",
	function(player,cmd)
		if not isGuestAccount(getPlayerAccount(player)) then
			local accountName = getAccountName(getPlayerAccount(player));
			if accountName then
				local money = tostring ( getPlayerMoney(player) );
				local carID;
				if getPedOccupiedVehicle(player) then
					carID = tostring( getElementModel(getPedOccupiedVehicle(player)) );
				else
					carID = "N/A";
				end
				local customPoints = getElementData(player,"points") and tostring(getElementData(player,"points")) or "0";
				SaveValues(accountName,money,carID,customPoints);
			end
		end
	end
)

addCommandHandler("ShowMe",
	function(player,cmd)
		if not isGuestAccount(getPlayerAccount(player)) then
			local accountName = getAccountName(getPlayerAccount(player));
			if accountName then
				local SQL = executeSQLQuery("SELECT * FROM aeroTable WHERE account_name=?",accountName);
				if SQL and type(SQL)=="table" and #SQL > 0 then
					outputChatBox(SQL[1].account_name,player);
					outputChatBox(SQL[1].money,player);
					outputChatBox(SQL[1].carID,player);
					outputChatBox(SQL[1].customPoints,player);
				else
					outputChatBox("Use SaveMe to save your information",player);
				end
			end
		end
	end
)

 

Edited July 4, 2020 by VenomNX

[areoslasher]

Wow man that was extremely helpful, I will go through the code and try to let it sink in.. I really appreciate taking your time, thank you very much!

-hope you don't mind if I pm you if I don't understand something? If that's not okay just let me know, I wouldn't want to bother you. You helped me more than I expected!

[nxFairlywell]

27 minutes ago, areoslasher said:

Wow man that was extremely helpful, I will go through the code and try to let it sink in.. I really appreciate taking your time, thank you very much!

-hope you don't mind if I pm you if I don't understand something? If that's not okay just let me know, I wouldn't want to bother you. You helped me more than I expected!

Np, You're welcome