SQL Issue: INSERT won't actually INSERT (tmysql)

[jrj996]

Hey guys, I'm trying to work on a rank system with SQL, the issue I've run into is whenever I try insert into the table it doesn't seem to work, I'm not sure why, but whenever I query to retrieve from the database it works fine(I have manually insert into the DB for this to work.)

Here's the code:
[lua]
function Player:SetRank(rank)
if rKey(rank) then
self.Rank = ranks[rKey(rank)].Rank
db:Query("INSERT INTO admin (steamid, rank) VALUES ("..self:SteamID()..","..rank..")",function(_,_,err) if err then error(err) end end)
end
end
[/lua]

Not that great at SQL so I wouldn't be surprised if it was my syntax. Thanks for any help!

[SeveredSkull]

Ever thought to recall the last error and see if you are doing something wrong?
The first thing I would guess is steamID isn't the correct type.
Next possibility is that you don't have " "s around the SteamID in the string...
if you dont wanna put \"s in the string itself, you can use sql.SQLStr() to convert it to a [B][I][U]safe [/U][/I][/B]string for sql.

[Derek_SM]

AFAIK You have to sql.SQLStr() the player's steamid.

[jrj996]

It's none of those things, I have the steamid saved as a VARCHAR(32), I fixed it though, it was just the way I went about doing it in lua.

[lua]
function Player:SetRank(rank)
if rKey(rank) then
self.Rank = ranks[rKey(rank)].Rank
local id = self:SteamID()
db:Query("SELECT * FROM admin WHERE steamid='"..id.."'",function(r,s,e)
if r then
if r[1] then
db:Query("UPDATE admin SET rank='"..rank.."' WHERE steamid='"..id.."'")
else
db:Query("INSERT INTO admin VALUES('"..id.."','"..rank.."')")
end
end
end)
end
end
[/lua]

By the way, I'm pretty sure SteamID's are already safe for being placed in a DB.

[SeveredSkull]

[QUOTE=jrj996;40236231]It's none of those things, I have the steamid saved as a VARCHAR(32), I fixed it though, it was just the way I went about doing it in lua.

[lua]
function Player:SetRank(rank)
if rKey(rank) then
self.Rank = ranks[rKey(rank)].Rank
local id = self:SteamID()
db:Query("SELECT * FROM admin WHERE steamid='"..id.."'",function(r,s,e)
if r then
if r[1] then
db:Query("UPDATE admin SET rank='"..rank.."' WHERE steamid='"..id.."'")
else
db:Query("INSERT INTO admin VALUES('"..id.."','"..rank.."')")
end
end
end)
end
end
[/lua]

By the way, I'm pretty sure SteamID's are already safe for being placed in a DB.[/QUOTE]

Well, no. You actually did my fix even though you're not acknowledging that I was right. You put ' 's around steamid just like I told you to earlier.

Also, I assumed you were either removing the entries with the same primary keys elsewhere, or you knew it didn't exist. I had no idea rKey didnt do such things, nor could you expect us to know. If this ISN'T the case, you can use the "REPLACE INTO" syntax with your original code to do exactly what you have there on a one-liner:

[lua]
function Player:SetRank(rank)
if rKey(rank) then
self.Rank = ranks[rKey(rank)].Rank
db:Query("REPLACE INTO admin (steamid, rank) VALUES ('"..self:SteamID().."',"..rank..")",function(_,_,err) if err then error(err) end end)
end
end
[/lua]