SQL Issues.

I’m using this in a “LoadPoints” function:


IF EXISTS SELECT * FROM `cash` WHERE `steam`="steam1" ELSE IF NOT EXISTS INSERT INTO `cash` (`steam`, `cash`) VALUES ("steam1",0);

However this gives errors, what is wrong with it?

Lets start with asking you to show us the error, second of all you need to query it with the function: sql.Query(“QUERY”).


local function LoadPoints()
	local checkPoints = database:query("IF EXISTS SELECT * FROM `cash` WHERE `steam`='"..ply:SteamID().."'; ELSE IF NOT EXISTS INSERT INTO `cash` (`steam`, `cash`) VALUES ('"..ply:SteamID()"',0);")
	checkPoints.onError = function(Q,Err) print("Failed to insert data: " .. Err) end
	checkPoints:start()
end

This is my code, When I query it It gives this out.


 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'EXISTS * FROM `cash` WHERE `steam`="steam1" ELSE INSERT IF NOT EXISTS INTO `cash' at line 1 

Well since its not actuall QUERY code, what are you trying to do?

If the player has never joined before when the function is called it’ll check if the player is in the database and if he is then load his points, however if not it inserts his steamid with a default value of 0.

  • I am dumb -

There are ways to use EXISTS inside a query.

Basic Queries ( advanced only covers pivots at the moment which isn’t what you need ):
https://dl.dropboxusercontent.com/u/26074909/tutoring/database/basic_queries_and_query_formats_used_to_check_for_existing_row.lua

I made a query generator, but it doesn’t have what you need but may be helpful in the future because I am planning on adding support for alternate queries in a query ( Update if exists, insert if not, etc… ): https://bitbucket.org/Acecool/acecooldev_base/src/master/gamemode/shared/classes/class_query_sv.lua?at=master

So, with what you’re doing is actually done quite frequently with table creation.

Depending on your query requirements, you can insert if something doesn’t exist, select if it does, update if it does, etc… Take a look at this, this should show you how to build those queries: http://www.techonthenet.com/sql/exists.php And here are some other examples: http://stackoverflow.com/questions/11906506/sql-server-2008-if-not-exists-insert-else-update