sql stuff??

[h4rDm0d]

im making like a rank thing for sandbox when you kill npcs

this table gets created but the index for new players doesnt so it just wont update the kills at all cause the index isnt there
[lua]
function GM:Initialize()
sql.Query( "CREATE TABLE IF NOT EXISTS npc_kills (player TEXT, kills INTIGER );" )
--timer.Create("NPC", 60, 0 , NPC_spawns())
NPC_spawns()
end

function GM:PlayerInitialSpawn( ply )
local playerSteam = ply:SteamID()
sql.Begin()
local check = sql.Query("SELECT kills FROM npc_kills WHERE player = '" ..playerSteam .."'")
if(!check) then
sql.Query( "INSERT INTO npc_kills VALUES ('" ..playerSteam .."',0)")
end
end

function GM:OnNPCKilled(attacker)
sql.Begin()
local playerSteam = attacker:SteamID()
local curKills = sql.Query("SELECT kills FROM npc_kills WHERE player = '" ..playerSteam .."'")

if(attacker:IsPlayer()) then
sql.Query("UPDATE npc_kills SET kills = " ..curKills + 1 .." WHERE player = '" ..playerSteam .."'")
end
sql.Commit()
end
[/lua]

Thats all in player.lua i would love to get this working plz help meh :P :D

[Entoros]

[b][url=http://wiki.garrysmod.com/?title=Sql.SQLStr]Sql.SQLStr [img]http://wiki.garrysmod.com/favicon.ico[/img][/url][/b]

Learn it, love it.

You need to make your strings safe, particularly SteamIDs.

Just say [lua]local playerSteam = sql.SQLStr(attacker:SteamID());[/lua]

Also, you should enclose expressions when you're concatenating.

[lua]"kills = " .. ( curKills + 1 ) .. " WHERE player = "[/lua]

[h4rDm0d]

what do you mean by safe ?

also it doesnt even make the index like a blank record like for myself
steamId and 0 kills for the first time a player joins the server
and so it wont reset its value when i go to add 1 when they kill, also will this be laggy?

[_nonSENSE]

Strings (especially names) can have characters in them that 'break' the query, for example quotes. You need to escape those to avoid problems with the query. That's what SQLStr does for you.

[h4rDm0d]

still doesnt work :(

[MegaJohnny]

On your CREATE TABLE query I'm pretty sure it's integer, not intiger.

[Entoros]

You also might need to add in your CREATE TABLE statement

PRIMARY KEY(player)

[h4rDm0d]

[QUOTE=MegaJohnny;23387711]On your CREATE TABLE query I'm pretty sure it's integer, not intiger.[/QUOTE]

ye i fixed that :P and nothing wrong with the table being created just none of the other stuff works :S

[MegaJohnny]

On line 19, you're putting the query results into curKills. sql.Query returns a table of tables, and you're treating it as a number on line 22. Try

[lua]local curKills = sql.Query("SELECT kills FROM npc_kills WHERE player = '" ..playerSteam .."'")[1]["kills"][/lua]
I think. That finds the kills field of the first record of the query result.

Also you're checking if he's a player before you write the new value, but before that you get the killer's SteamID, which needs to be run on a player as well. I suppose I'd put [lua]if not attacker:IsPlayer() then return end[/lua] at the top of the function.

[Entoros]

Oh, that's a good point. To get values, you generally use sql.QueryValue instead of sql.Query.

[h4rDm0d]

ok so this is what i have so far yet it still doenst create a new index for new players
[lua]
function GM:Initialize()
sql.Query("CREATE TABLE IF NOT EXISTS npc_kills('steam' TEXT NOT NULL, 'amount' INTEGER NOT NULL, PRIMARY KEY('steam'));")
end

local indexSteam = {}
function GM:PlayerInitialSpawn( ply )
local steamID = ply:SteamID()
local indexSteam = sql.Query("SELECT steam FROM npc_kills;")
if (table.HasValue(indexSteam, steamID)) then
return
else
sql.Query("INSERT INTO npc_kills VALUES (" .. sql.SQLStr(steamID) .. ", 0);")
end
end

function GM:OnNPCKilled(attacker)
local steamID = ply:SteamID()
local curKills = sql.QueryValue("SELECT kills FROM npc_kills WHERE steam = " ..sql.SQLStr(steamID) ..";")

if(!attacker:IsPlayer()) then
return
else
sql.Query("UPDATE npc_kills SET amount = " .. (curKills + 1) .. " WHERE steam = " .. sql.SQLStr(attacker:SteamID()) .. ";")
end
end
[/lua]

i found this on the w3 website but still dont work
[lua]
sql.Query("INSERT INTO npc_kills (steam,amount) VALUES (" .. sql.SQLStr(steamID) .. ", 0);")
[/lua]
:(

[CombineGuru]

use REPLACE INTO instead of INSERT INTO

[MegaJohnny]

Are you sure the field names in your CREATE TABLE need quotes?

Try putting Msg(sql.LastError().."\n") after any SQL to find out if there's anything wrong with the SQL itself.

You've also not changed your line 18 to use amount instead of kills.

[h4rDm0d]

positive its how every other table is made i was looking at the data.lua file from darkrp and the code i have should work perfect, but wont

[lua]
function GM:Initialize()
sql.Query("CREATE TABLE IF NOT EXISTS npc_kills('steam' TEXT NOT NULL, 'amount' INTEGER NOT NULL, PRIMARY KEY('steam'));")
end

function GM:PlayerInitialSpawn( ply )
sql.Begin()
local steamID = ply:SteamID()
local index = sql.QueryValue("SELECT amount FROM npc_kills WHERE steam = " .. sql.SQLStr(steamID))
if(!index) then
sql.Query("INSERT INTO npc_kills VALUES(" .. sql.SQLStr(steamID) .. ", 0);")
end
sql.Commit()
end

function GM:OnNPCKilled(attacker)
sql.Begin()
local steamID = attacker:SteamID()
local curKills = sql.QueryValue("SELECT amount FROM npc_kills WHERE steam = " .. sql.SQLStr(steamID) .. ";")
if(!attacker:IsPlayer()) then
return
else
sql.Query("UPDATE npc_kills SET amount = " .. (curKills + 1) .. " WHERE steam = " .. sql.SQLStr(steamID) .. ";")
end
sql.Commit()
end
[/lua]

[MegaJohnny]

Just noticed, the attacker is the second argument of GM:OnNPCKilled. So you can just replace line 15 with
[lua]
function GM:OnNPCKilled(npc, attacker)
[/lua]
and that might help.

[h4rDm0d]

im not to worried about that, it wont even make a new index for new players so changing that code is pointless for now the initial spawn function is the one im mainly worried about atm

[MegaJohnny]

I know, I'm just pointing out things I see that are wrong. Have you added the LastError lines to see if there's an error in the SQL?

[h4rDm0d]

ye i did and their were no errors :S

[h4rDm0d]

this works :P

[lua]

function GM:Initialize()
sql.Query("CREATE TABLE IF NOT EXISTS npc_kills ('steam' TEXT NOT NULL, 'amount' INTEGER NOT NULL, PRIMARY KEY('steam'));")
end

function GM:OnNPCKilled( victim, killer, weapon )
if(killer:IsPlayer()) then
local curKills = sql.QueryValue("SELECT amount FROM 'npc_kills' WHERE steam = " ..sql.SQLStr(killer:SteamID()) ..";")
if (!curKills) then
curKills = 0
sql.Query("INSERT INTO npc_kills VALUES(" .. sql.SQLStr(killer:SteamID()) .. ",1);")
end
local NewKills = curKills + 1
sql.Query("UPDATE npc_kills SET amount = " ..NewKills .." WHERE steam = " ..sql.SQLStr(killer:SteamID()) ..";")
end
end
[/lua]

[MegaJohnny]

It seems like it'd be more efficient to do

if !curkills then
     make new npc_kills record
else
     add 1 to the existing kills
end