Check BD

[Mr. Maxilian]

Hey, guys. The question arose: I have a nickname (we get it with guiCreateEdit), how do I create a query in mysql and check if it is registered?
Help me, pleas!

[Addlibs]

Depending on whether you're using an external MySQL, local SQLite database or built-in SQLite registry.db. For the first two, you need to create a connection via dbConnect. The last one only requires the use of executeSQLQuery. You'll need to do all of it on the server side, using events to send the nickname from the GUI to the server. The query string is pretty much the same between MySQL and SQLite: SELECT [columns1, column2, ...] FROM [table] WHERE [column1] = ? AND [column2] = ?

So if you want to check for a nickname being registered, you'll want to do something like this:

-- for external MySQL or local SQLite
dbQuery(
  function(queryHandle) -- callback func
    local result = dbPoll(queryHandle, 0)
    if #result >= 1 then
      -- record with such nickname exists
    else
      -- no record for this nickname
    end
  end, 
  dbConnection, "SELECT nicknane FROM accounts WHERE nickname = ? LIMIT 1", strPlayerNickname
)


-- for internal registry.db
local result = executeSQLQuery(dbConnection, "SELECT nickname FROM accounts WHERE nickname = ? LIMIT 1", strPlayerNickname)
if #result >= 1 then
  -- record with such nickname exists
else
  -- no record for this nickname
end

The '?' are used for parameter binding, which is the easiest and probably the safest way to prevent SQL injection.

I only select 'nickname' as we don't need any extra information.

"LIMIT 1" prevents the SQL server from looking for more that one record, since that already falsifies the proposition that there is no record with that nickname.

 

Note: I am not sure if the given code works out of the box -- not tested. I haven't scripted on MTA for a while and I am unsure of the exact structure of the SQL query return. You might need to do some debugging with iprints if it doesn't work.

Edited June 17, 2019 by MrTasty

[Mr. Maxilian]

19 hours ago, MrTasty said:

Depending on whether you're using an external MySQL, local SQLite database or built-in SQLite registry.db. For the first two, you need to create a connection via dbConnect. The last one only requires the use of executeSQLQuery. You'll need to do all of it on the server side, using events to send the nickname from the GUI to the server. The query string is pretty much the same between MySQL and SQLite: SELECT [columns1, column2, ...] FROM [table] WHERE [column1] = ? AND [column2] = ?

So if you want to check for a nickname being registered, you'll want to do something like this:

-- for external MySQL or local SQLite
dbQuery(
  function(queryHandle) -- callback func
    local result = dbPoll(queryHandle, 0)
    if #result >= 1 then
      -- record with such nickname exists
    else
      -- no record for this nickname
    end
  end, 
  dbConnection, "SELECT nicknane FROM accounts WHERE nickname = ? LIMIT 1", strPlayerNickname
)


-- for internal registry.db
local result = executeSQLQuery(dbConnection, "SELECT nickname FROM accounts WHERE nickname = ? LIMIT 1", strPlayerNickname)
if #result >= 1 then
  -- record with such nickname exists
else
  -- no record for this nickname
end

The '?' are used for parameter binding, which is the easiest and probably the safest way to prevent SQL injection.

I only select 'nickname' as we don't need any extra information.

"LIMIT 1" prevents the SQL server from looking for more that one record, since that already falsifies the proposition that there is no record with that nickname.

 

Note: I am not sure if the given code works out of the box -- not tested. I haven't scripted on MTA for a while and I am unsure of the exact structure of the SQL query return. You might need to do some debugging with iprints if it doesn't work.

And what better "MySQL" or "XML" to save accounts?