• SQL Issue: INSERT won't actually INSERT (tmysql)
    4 replies, posted
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!
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.
AFAIK You have to sql.SQLStr() the player's steamid.
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=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]
Sorry, you need to Log In to post a reply to this thread.