TFS 1.X+ Action Item is not giving Store points if account has 0 points.

[Awesomedudei]

I have an item with this Code on my server

local points = 15

function onUse(player, item, fromPosition, target, toPosition, isHotkey)
  db.query("UPDATE `znote_accounts` SET `points` = `points` + '15' WHERE `id` = '" .. player:getAccountId() .. "';")
  player:sendTextMessage(MESSAGE_EVENT_ADVANCE, "You have received 15 store credit.")
  item:remove(1)
  return true
end

People who have 0 points and use this item doesn't gain any points
but if you already have more than 1 point it gets added
Why is this ?:/

 

[Nekiro]

If your table „znote_points” can have NULL values then if player has no points they are NULL, if you try to do anything with NULL in sql it will always return NULL.

 

[River KA]

I have an item with this Code on my server

local points = 15

function onUse(player, item, fromPosition, target, toPosition, isHotkey)
  db.query("UPDATE `znote_accounts` SET `points` = `points` + '15' WHERE `id` = '" .. player:getAccountId() .. "';")
  player:sendTextMessage(MESSAGE_EVENT_ADVANCE, "You have received 15 store credit.")
  item:remove(1)
  return true
end

People who have 0 points and use this item doesn't gain any points
but if you already have more than 1 point it gets added
Why is this ?:/

Try this:

db.query(string.format("UPDATE `znote_accounts` SET `points` = `points` + '%d' WHERE `account_id` = '%d'", 15, player:getAccountId()))

 

[Awesomedudei]

Try this:

db.query(string.format("UPDATE `znote_accounts` SET `points` = `points` + '%d' WHERE `account_id` = '%d'", 15, player:getAccountId()))

Sorry if you saw my post first i forgot to /reload actions ... facepalm

Thank you alot @River KA , this worked wonders <3

 

[kor]

@Awesomedudei but if you have NULL as your value in DB it's not enough. Here is detailed explanation SQL increment integer column even if null (https://stackoverflow.com/a/18085617) so query in your case will look like

db.query("UPDATE `znote_accounts` SET `points` = Coalesce(`points`, 0) + 15 WHERE `id` = '" .. player:getAccountId() .. "'")

or using solution posted by @River KA

db.query(string.format("UPDATE `znote_accounts` SET `points` = Coalesce(`points`, 0) + '%d' WHERE `account_id` = '%d'", 15, player:getAccountId()))

 

[River KA]

@Awesomedudei but if you have NULL as your value in DB it's not enough. Here is detailed explanation SQL increment integer column even if null (https://stackoverflow.com/a/18085617) so query in your case will look like

db.query("UPDATE `znote_accounts` SET `points` = Coalesce(`points`, 0) + 15 WHERE `id` = '" .. player:getAccountId() .. "'")

or using solution posted by @River KA

db.query(string.format("UPDATE `znote_accounts` SET `points` = Coalesce(`points`, 0) + '%d' WHERE `account_id` = '%d'", 15, player:getAccountId()))

Znote does not uses NULL values. Mine is enough.

 

[kor]

Yes, it is, that's why your answer is Best Answer and I linked universal solution "if you have NULL as your value".