Lua Requesting info from mysql database problem

[Tymofek]

Hello, Thx for reading.

am having a problem with mysql, as i've being reading, its a known problem with tfs 0.2 and mysql querys.

i created a new column in the database named "number" in the accounts table.
i need to do something like this:

if msgcontains(msg,"lalala") then
if ("SELECT `number` FROM `accounts` WHERE id="..getPlayerGUID(cid)..";")) == 1 then
blah blah blah.

if am not wrong this would bring the value placed in the table accounts column number right?

then my script would success!

but i dunno how to make the connection with the database so as i was reading i found this
http://otland.net/f163/forgotten-server-0-3-db-class-0-2-a-26225/

but still cannot connect because i dont know how to spell that line to connect.

Please! give me something to try!

btw i also need a script to update that value
something like

if msgcontains(msg,"lelele") then
("UPDATE accounts SET number='1' WHERE id="..getPlayerGUID(cid)..";")
doPlayerSendTextMessage blah blah blah.

I hope you will know everything i dont xD
Thx in advance

 

[Tymofek]

Bump

 

[Tymofek]

bump

 

[Tymofek]

Please! ?

 

[Tymofek]

bump!

 

[Milice]

local dbQ = db.getResult For SELECT

and

db.executeQuery

to Execute a Query, obviously ^.- (UPDATE, INSERT)

 

[Tymofek]

am sorry, can you be more especific, like writing an example that looks like my script?

i need to send 1 value to the table and then get it back.

something like this.

if player say "white" then
insert "white" in the database in the column "number" that is in the table "accounts"
end

if player say "red" then
insert "red" in the database in the column "number" that is in the table "accounts"
end

and then...

if (get the value previosly inserted in the column "number" table "accounts") == white then
blah blah blah
end

if (get the value previosly inserted in the column "number" table "accounts") == red then
blah blah blah
end

please! could someone! !?!?!?

 

[Milice]

if player say "white" then
db.executeQuery("INSERT INTO `accounts` ('number') VALUES ('1')")
end

if player say "red" then
db.executeQuery("INSERT INTO `accounts` ('number') VALUES ('2')")
end

and then...

local color = db.getResult("SELECT `number` FROM `accounts` where `id` = '" .. getPlayerAccountId(cid) .. "'")
local cR = color:getDataInt()

if (cR == 1) then
blah blah blah
end

if (color == 2) then
blah blah blah
end

Theres the MySQL part atleast x'D im no good with lua sql so i made it with int 1 = white, 2 = red.. hope its okay :ninja:

 

[Tymofek]

well this is what i was talking about xD i remember you that i am using TFS 0.2.10
and as i was reading there is a problem with mysql

look

[13/06/2011 17:30:51] Lua Script Error: [Npc interface]
[13/06/2011 17:30:51] data/npc/scripts/anciano.luanCreatureSay
[13/06/2011 17:30:51] data/npc/scripts/anciano.lua:41: attempt to index global 'db' (a nil value)
[13/06/2011 17:30:51] stack traceback:
[13/06/2011 17:30:51] [C]: in function '__index'
[13/06/2011 17:30:51] data/npc/scripts/anciano.lua:41: in function 'callback'
[13/06/2011 17:30:51] data/npc/lib/npcsystem/npchandler.lua:379: in function 'onCreatureSay'
[13/06/2011 17:30:51] data/npc/scripts/anciano.lua:8: in function <data/npc/scripts/anciano.lua:8>

this is the script in the line "41"

db.executeQuery("INSERT INTO `accounts` ('number') VALUES ('1')")

is just like you sayed xD

Please HELP! xD

 

[Milice]

If u send me the full script maybe i can help xP cuz im pretty sure i didn't make 41 lines lol xD

 

[Tymofek]

look the script i am writing is for a private server and its rules are that what i am asked to do are top secret stuff, thats why i cant share the rest of the script, but i can assure you that the rest of the script is nothing more that sentences with "if then" that has nothing to do with it.

the thing is that i need to store a value in a new column i created, and that it doesnt work!.

am sure this is a problem with the TFS 0.2.10, i readed that cykotitan sayed that this version only worked with luasql, and am not sure what does means xD.

this is the line i add from you to test and didnt work. maybe i missed something.

if (msgcontains(msg, 'asd')) then
db.executeQuery("INSERT INTO `accounts` ('bando') VALUES ('1')")
doPlayerSendTextMessage(cid,19,"IT WORKED!")
end

if this stores a "1" without any console errors then i will configure it to my needs xD.
did i forget to add something to that line? maybe some local function to make that sentence work?
Thx in advance!

 

[Tymofek]

Ok i made it to this!
i installed this system
http://otland.net/f163/forgotten-server-0-3-db-class-0-2-a-26225/

and suddenly que querys started to work with no errors, but just the UPDATE type like this

local name = getPlayerName(cid)
sql:execute("UPDATE accounts SET number = 1 WHERE id = "..getAccountNumberByPlayerName(name).." ;")
				sql:close()
				env:close()

ok, That works, i checked the DB and it was changed but....

when i needed to do a SELECT i wrote this:

local data = db.getResult("SELECT number FROM accounts WHERE id = "..getAccountNumberByPlayerName(name).." ;")
local test = data:getDataInt("number")
if (msgcontains(msg, 'test')) then
doPlayerSendTextMessage(cid, 19, test)
end

that looks right for me xD

but i get this error in the console:

Lua Script Error: [Npc interface]
[15/06/2011 14:32:22] data/npc/scripts/anciano.luanCreatureSay
[15/06/2011 14:32:22] data/db.lua:37: attempt to call method 'numrows' (a nil value)
[15/06/2011 14:32:22] stack traceback:
[15/06/2011 14:32:22] [C]: in function 'numrows'
[15/06/2011 14:32:22] data/db.lua:37: in function 'getResult'
[15/06/2011 14:32:22] data/npc/scripts/anciano.lua:56: in function 'callback'
[15/06/2011 14:32:22] data/npc/lib/npcsystem/npchandler.lua:379: in function 'onCreatureSay'
[15/06/2011 14:32:22] data/npc/scripts/anciano.lua:8: in function <data/npc/scripts/anciano.lua:8>

so i checked the db.lua file that i get from that post.
and found this:

31.function db.getResult(sql)
32. local mt = {}
33.
34. mt.env, mt.con = getConnection()
35. mt.cur = assert(mt.con:execute(sql))
36.
37. if(mt.cur:numrows() > 0) then
38. mt.val = mt.cur:fetch({}, "a")
39. end
40.
41. setmetatable(mt, db)
42. return mt
43.end


so... that doesnt mean anything to me ! xD, but am getting close!!!

Please help!

its useless to insert a value into a table if i cant get it back! xD

 

[Tymofek]

Solved now!

already solved alone -.-?

ill post the solution so anyone can find it

well.

first this lines has to be there

dofile("./config.lua")
                 if sqlType == "mysql" then
					env = luasql.mysql()
					con = env:connect(mysqlDatabase, mysqlUser, mysqlPass, mysqlHost)
				else -- sqlite
					env = luasql.sqlite3()
					con = env:connect(sqliteDatabase)
				end

then a function like onplayersay, onuse etc

then to insert a new value in the DB

con:execute("UPDATE [tablename] SET [columnname] = [value betwen '' if is a varchar] WHERE id = ["..getAccountNumberByPlayerName(getPlayerName(cid)).."] ;")

(this example is based on insert a value on the "accounts" table)

and then to call back the value from that column in that table:

cur = assert(con:execute("SELECT `[column name]` FROM `[table name]` WHERE `id` == "..getAccountNumberByPlayerName(getPlayerName(cid))..";"))
    row = cur:fetch({}, "a")

(in that lines @up you are calling back the value and naming it "row" so when you need to use it in any condition you have to call it back this way "row.columnname")

like this:

if row.columname == "value" then
doPlayerSendTextMessage(cid, 19, "yay!")
elseif row.bando == "another value" then
doPlayerSendTextMessage(cid, 19, "wow!")
end

and thats all!!!, but only if is not easy enough to understand then here is the full example of inserting a value into a table and calling it back to use it in a condition :

dofile("./config.lua")
                 if sqlType == "mysql" then
					env = luasql.mysql()
					con = env:connect(mysqlDatabase, mysqlUser, mysqlPass, mysqlHost)
				else -- sqlite
					env = luasql.sqlite3()
					con = env:connect(sqliteDatabase)
				end
			
if (msgcontains(msg, 'lol')) then
con:execute("UPDATE accounts SET word = 'lol' WHERE id = "..getAccountNumberByPlayerName(getPlayerName(cid)).." ;")
npcHandler:say('DONE!, the column value in the table accounts has changed to "lol"',cid)
end	


if (msgcontains(msg, 'word')) then
    cur = assert(con:execute("SELECT `word` FROM `accounts` WHERE `id` == "..getAccountNumberByPlayerName(getPlayerName(cid))..";"))
row = cur:fetch({}, "a")

if row.word == "lol" then
doPlayerSendTextMessage(cid, 19, "the word its lol")
elseif row.word == "lal" then
doPlayerSendTextMessage(cid, 19, "the word is lal")    
else
doPlayerSendTextMessage(cid, 19, " you dont have any value stored")    
end
end

And thats it! am sure many people has this f***ing problem am sure this has to help

now my THREAD is SOLVED! please close it DOWN!