Get data from dbPoll table and compare

[Quebec]

This could be a very basic question but I don't know where to search the answer so I came here. In the MySQL database I have a table called accounts and I want to get value from hascharacter column (tinyint value, 0 to 1)which is located inside accounts. After that I want to check if it is true but I don't know how. Here is the code:

local hasCharacter = tonumber(hasCharacter)
local verificationQuery = dbQuery(db, "SELECT `hascharacter` FROM `accounts` WHERE `hascharacter` = '" .. hasCharacter .. "'")
local verificationResult = dbPoll(verificationQuery, -1)
if verificationResult > 0 then --this is the line which I don't know how to use properly
	outputChatBox('Verification value is TRUE', thePlayer, 100, 255, 100)
else
	outputChatBox('Verification value is FALSE', thePlayer, 255, 100, 100)
end

 

[Patrick]

Hi.

local hasCharacter = tonumber(hasCharacter)

local verificationQuery  = dbQuery(db, "SELECT `hascharacter` FROM `accounts` WHERE `hascharacter` = '?'", hasCharacter) -- use argument binding, instead of string concatenation (it's always a better way)
local verificationResult = dbPoll(verificationQuery, -1)
local countOfResults     = #verificationResult -- table length

if countOfResults > 0 then --this is the line which I don't know how to use properly
	outputChatBox('Verification value is TRUE', thePlayer, 100, 255, 100)
else
	outputChatBox('Verification value is FALSE', thePlayer, 255, 100, 100)
end

 

[Quebec]

2 hours ago, Patrick said:

Hi.

local hasCharacter = tonumber(hasCharacter)

local verificationQuery  = dbQuery(db, "SELECT `hascharacter` FROM `accounts` WHERE `hascharacter` = '?'", hasCharacter) -- use argument binding, instead of string concatenation (it's always a better way)
local verificationResult = dbPoll(verificationQuery, -1)
local countOfResults     = #verificationResult -- table length

if countOfResults > 0 then --this is the line which I don't know how to use properly
	outputChatBox('Verification value is TRUE', thePlayer, 100, 255, 100)
else
	outputChatBox('Verification value is FALSE', thePlayer, 255, 100, 100)
end

 

I tried it yet it gives me a wrong result. The value in database is set to 1 (true) and in game I get the message from FALSE ouputChatBox. I know that # operator measures the length of the variable but is there a way to identify the actual value of it?

[SpecT]

Well if "hascharacter" can have only 2 states (0 and 1) then you could put in the query at the WHERE part to be like WHERE 'hascharacter' = '1'
And then if the countOfResults is 0 that means the account has this column set to 0; if its 1 it will return you 1 result => countOfResults will be 1.

Btw why isn't there an account id/username specified ?
Currently if there are 2 or more accounts with "hascharacter" set to 1 it will return you the times it was found.

Maybe the query should be:

SELECT `id` FROM `accounts` WHERE `id` = `?` AND `hascharacter` = `1`

You will need to pass the account ID or username in the dbQuery.

[Quebec]

On 06/06/2021 at 20:07, SpecT said:

Well if "hascharacter" can have only 2 states (0 and 1) then you could put in the query at the WHERE part to be like WHERE 'hascharacter' = '1'
And then if the countOfResults is 0 that means the account has this column set to 0; if its 1 it will return you 1 result => countOfResults will be 1.

Btw why isn't there an account id/username specified ?
Currently if there are 2 or more accounts with "hascharacter" set to 1 it will return you the times it was found.

Maybe the query should be:

SELECT `id` FROM `accounts` WHERE `id` = `?` AND `hascharacter` = `1`

You will need to pass the account ID or username in the dbQuery.

Thank you for your answer, it works. I'm pretty new to MySQL so I didn't know how to approach this.

[SpecT]

17 minutes ago, Quebec said:

Thank you for your answer, it works. I'm pretty new to MySQL so I didn't know how to approach this.

You're welcome!
And don't worry we all have been there.