Solved NPC with SQL query, so difficult to me...

[vejin]

Hello.
I'd like to place an NPC in my server who gives to us a new character:
if you already have a druid on acc, you won't get new druid or sorc,
if you already have a sorc on acc, you won't also get new druid or sorc.
you need to have some storage value >= 1000 to get this converstation with npc started.

I could actually do it myself, but it definitely needs knowledge of sql which I don't know at all... (except phpmyadmin )

Is there anyone who could just make this NPC or eventually give me an advice how should I make a query in NPC which checks characters on account?
I'd be grateful

I'm using TFS 1.0 and mysql through phpmyadmin.

@EDIT solved! at last!

query should look like this:

local acc = getAccountNumberByPlayerName(cid)
local vocid = 2 --druid
local check = db.storeQuery("SELECT * FROM players WHERE `account_id` = ".. db.escapeString(acc) .." AND `vocation` = " .. vocid)
if check ~= false then
       selfSay("You already have druid!", cid)
end

 

[EvulMastah]

db.query("SELECT `vocation` FROM `players` WHERE `account_id` = "..getPlayerAccountId(cid))

 

[vejin]

Thank you so much!
Another one yet too hard to me, how should look query which adds a character? only this one and I am saved

 

[Limos]

If you go to phpmyadmin then click table players, SQL and INSERT, then you get the query how to create a character, just change the values then.

 

[vejin]

@UP Got it, works.

db.query("SELECT `vocation` FROM `players` WHERE `account_id` = "..getPlayerAccountId(cid))

What kind of value does it return? is it bool? How to insert there e.g. 1 or 2 voc id to check it out?
And how to check name if it is already used? Kill me, but I really do not get sql.

@edit
Found out that name thing:

local result_plr = db.getResult("SELECT * FROM players WHERE `name` = '"..myname.."';")
if(result_plr:getID() ~= -1) then
'already in use blah blah'

but still don't get the vocation thing. Tried already to check values returned at phpmyadmin but it returns 0 lines...

@edit 'bout the vocation thing:
finally made a right syntax in pma and it returned me 6 lines with all available vocations in my account. I checked how it returns in lua:

db.storeQuery("SELECT `vocation` FROM `players` WHERE `account_id` = 1")

it returned value: 10 then 11, next time 12, and another time 13... so I changed db.storeQuery to db.query and now it returns 'true'

another, and I hope final question is how to take out of this information about existing druids and sorcs in my acc?