Lua Database script?

[mayel]

Does anyone know how I, simply as possible can edit columns in tables in my mysql database with scripts in the game.
Like u press a lever and a column in the "players" table called vipdays changes to 30.
And if you know how to make it to change to +30,(add 30 days to current value)
I'm supposed to use this right? ---db.executeQuery(query)---
it's even better.
Btw how do i make random numbers in lua?
Thanks in advance...
I'm sure theres a lot of errors in this code but please tell me what's wrong:

function onUse(cid, item, frompos, itemEx, topos)
db.executeQuery(UPDATE players SET level = 8; WHERE name = "Test"; )
return true
end

 

[Evil Puncker]

UPDATE `players` SET `vipdays` = `vipdays` + '30'....... something like that, and just a tip, wouldn't be better to have the vip for the whole acc instead of the character?

 

[mayel]

Haha, ye but atm i'm just trying to understand the scripting
It didn't work though...

 

[Doggynub]

db.executeQuery("UPDATE `players` SET `level` = '8' WHERE `name` = 'test' ;")

 

[JDB]

Here is the correct query you need to execute in an action script.

local days = 10
db.executeQuery("UPDATE `accounts` SET `vipdays` = `vipdays` + " .. days .. " WHERE `id` = " .. getPlayerAccountId(cid) .. ";")

Database:

EACH Account VIP:

ALTER TABLE `accounts` ADD `vipdays` INT NOT NULL DEFAULT 0;

To put it in an action script (use item and get days) you simply change it to this.

function onUse(cid, item, fromPosition, itemEx, toPosition)
	local days = 10
	return db.executeQuery("UPDATE `accounts` SET `vipdays` = `vipdays` + " .. days .. " WHERE `id` = " .. getPlayerAccountId(cid) .. ";") and doRemoveItem(item.uid)
end

Adding Magic Effects:

Change:

return db.executeQuery("UPDATE `accounts` SET `vipdays` = `vipdays` + " .. days .. " WHERE `id` = " .. getPlayerAccountId(cid) .. ";") [COLOR="red"]and doRemoveItem(item.uid)[/COLOR]

To:

return db.executeQuery("UPDATE `accounts` SET `vipdays` = `vipdays` + " .. days .. " WHERE `id` = " .. getPlayerAccountId(cid) .. ";") [COLOR="red"]and doSendMagicEffect(getThingPos(cid), CONST_ME_GIFT_WRAPS) and doRemoveItem(item.uid)[/COLOR]

Adding a Message or Orange Letters:

Change:

return db.executeQuery("UPDATE `accounts` SET `vipdays` = `vipdays` + " .. days .. " WHERE `id` = " .. getPlayerAccountId(cid) .. ";") [COLOR="red"]and doSendMagicEffect(getThingPos(cid), CONST_ME_GIFT_WRAPS) and doRemoveItem(item.uid)[/COLOR]

To:

return db.executeQuery("UPDATE `accounts` SET `vipdays` = `vipdays` + " .. days .. " WHERE `id` = " .. getPlayerAccountId(cid) .. ";") [COLOR="red"]and doSendMagicEffect(getThingPos(cid), CONST_ME_GIFT_WRAPS) and doCreatureSay(cid, "You have received " .. days .. " VIP days!", TALKTYPE_ORANGE_1) doRemoveItem(item.uid)[/COLOR]

Final Product:

function onUse(cid, item, fromPosition, itemEx, toPosition)
	local days = 10
	return db.executeQuery("UPDATE `accounts` SET `vipdays` = `vipdays` + " .. days .. " WHERE `id` = " .. getPlayerAccountId(cid) .. ";") and doSendMagicEffect(getThingPos(cid), CONST_ME_GIFT_WRAPS) and doCreatureSay(cid, "You have received " .. days .. " VIP days!", TALKTYPE_ORANGE_1) and doRemoveItem(item.uid)
end

 

[WibbenZ]

rand = math.random(1,2,3,4)
doSendMagicEffect(getThingPos(cid) CONST_ME_rand)
end

 

[Doggynub]

rand = math.random(1,4)

 

[JDB]

rand = math.random(1,2,3,4)
doSendMagicEffect(getThingPos(cid) CONST_ME_rand)
end

No

random = math.random(CONST_ME_LAST)
doSendMagicEffect(getThingPos(cid), random)

 

[Kavvson]

Thx Jdb . Will help me bit