ViRuZGamiing

Query error

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

Share this post


Link to post

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.

Share this post


Link to post
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?

Share this post


Link to post

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

  • Like 1

Share this post


Link to post
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))

Share this post


Link to post

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

Edited by pa3ck
  • Like 1

Share this post


Link to post

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.

Share this post


Link to post

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

Share this post


Link to post
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

Share this post


Link to post

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.