Jump to content

Query error


ViRuZGamiing

Recommended Posts

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

Clipboardimage2016-12-06165139.png

Edited by ViRuZGamiing
Link to comment

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.

Link to comment
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 :P (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?

Link to comment

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

Link to comment
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.

  • Like 1
Link to comment

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...