sql Query error

[ViRuZGamiing]

Hi guys,

 

I'm trying to make a execute query function, this is what I got. But I seem to be doing something wrong.

Server sided

addEventHandler("onResourceStart", getResourceRootElement(getThisResource()), function()
  connection = dbConnect(dbInfo["databaseType"], "dbname="..dbInfo["dbname"]..";host="..dbInfo["host"], dbInfo["username"], dbInfo[password])
  if connection then
	   outputDebugString("Connection with database was successfully established.")
  else
    outputDebugString("Connection with database couldn't be established.")
  end
end)

function execQuery (queryString)
  local query = dbQuery(connection, tostring(queryString))
  if (string.find(queryString, "SELECT")) then
    local result = dbPoll(query, -1 )
    if not result == nil then
      return result
    end
  end
  dbFree(query)
end

This I have server sided in another file for testing my db

addEventHandler ("onPlayerJoin", getRootElement(),
  function ()
    local query = execQuery("SELECT username FROM users WHERE username = '"..getPlayerName(source) .."'")
    if (query) then
      outputChatBox("found username: "..tostring(query[0]))
    else
      outputChatBox("didn't found username, inserting!")
      execQuery("INSERT INTO users VALUES('', '"..getPlayerName(source).."')")
    end
  end
)

Also I'm not quite sure how to get the value since I'm returning a table atm.

 

Kind regards

P.S. my database looks like this

Edited December 6, 2016 by ViRuZGamiing

[pa3ck]

To check if there's actually something returned from SQL you should check "#result > 0", so you will know that there's something in the table. When checking if a username exist or just getting only 1 row from SQL you should always use "LIMIT 1" at the end, just like you would break out of the loop when you find what you are looking for.

Now, for your problem, MySQL returns rows as table, so it looks like this:

results = {
 	{
    	["username"] = "SomeBooodys username", 
    	["password"] = "SomeBooodys password",
   	}
}

So in order to get the first and only row, you don't need a loop, but you do it this way:

outputChatBox("found username: "..tostring(query[1]["username"]))

BTW, in LUA, the first index is not 0 but 1.

[ViRuZGamiing]

37 minutes ago, pa3ck said:

BTW, in LUA, the first index is not 0 but 1.

I do know  I'm a programmer in multiple languages, just not that good in LUA  (C#, Java, VB.NET, HTML, Javascript + jQuery, PHP, and a bit of python if you're wondering)

I just thought that would be my result if i'd do a SELECT * but since I did SELECT username I thought I'd just return 1 value. So it'd always returns an entire row?

[pa3ck]

No, it will only return that one column, but it's still structured the same way.

[ViRuZGamiing]

2 minutes ago, pa3ck said:

No, it will only return that one column, but it's still structured the same way.

Thanks!

[ViRuZGamiing]

5 hours ago, ViRuZGamiing said:

local query = execQuery("SELECT username FROM users WHERE username = '"..getPlayerName(source) .."'")

appearantly I'm not able to concate my query and I need to use:

5 hours ago, ViRuZGamiing said:

local query = execQuery("SELECT username FROM users WHERE username = ?", getPlayerName(source))

[ViRuZGamiing]

16 hours ago, pa3ck said:

outputChatBox("found username: "..tostring(query[1]["username"]))

attempt to index field '?' (a nil value)

[pa3ck]

Try to get the length of the query (#query) and see 

Edited December 7, 2016 by pa3ck

[ViRuZGamiing]

8 hours ago, pa3ck said:

Try to get the length of the query (#query) and see 

returns 0 strangely

[pa3ck]

Do you have HEX code in your name, by any chance? Are you sure there actually is something in the table with that name? You should also consider using id's (primary key with auto_increment) and you wouldn't need to worry about names.

[ViRuZGamiing]

So I got it to work by changing the if state to check if instead of query that query[1] returns true. Appearantly Query did return true although it was empty.

I think it's default like this:

local query = {

{}

} 

which makes that query isn't nil cause query returns a table but that table is empty.

addEventHandler ("onPlayerJoin", getRootElement(),
  function ()
    local query = execQuery("SELECT username FROM users WHERE username = ?", {getPlayerName(source)})
    if (query[1]) then
      outputChatBox("found! "..query[1]["username"])
    else
      outputChatBox("didn't found username, inserting!")
      execQuery("INSERT INTO users (username) VALUES(?)", {getPlayerName(source)})
    end
  end
)

But I guess this isn't clean code probably

[pa3ck]

On 12/6/2016 at 5:34 PM, pa3ck said:

you should check "#result > 0", so you will know that there's something in the table

It will always be true, unless there's no connection, check the results by getting the table length returned from dbPoll as I said.