Problem with MySQL script

[Xabi]

Hi all, I have the following script to get a registered player:

  
function checkLogin(username, password) 
    dbHandle = dbConnect("mysql", "dbname=mta;host=127.0.0.1", "root", "*****") 
    if isElement(dbHandle) then 
        local result = executeSQLQuery("CREATE TABLE IF NOT EXISTS players (id TEXT, name TEXT, password TEXT)") 
        local result = executeSQLQuery("SELECT `id` FROM `players` WHERE `name` = ? AND `password` = ?", username, password) 
    else 
        outputChatBox("Couldn't connect to database.") 
    end 
end 

So when I use that, it works but it doesn't show the created table into mysql (I use HeidiSQL to access to the database). Also, if i create the database manually and do only that SELECT, it says that table 'players' can't be found.

I searched in registry.db, and it creates the table there as it was an SQLite database so, anybody knows what could be the problem?

Thanks in advance

[xXMADEXx]

executeSQLQuery is for the default MTA database. You need to use dbQuery, dbPoll, and dbExec. Also, you should put the "dbHandle" variable in an onResourceStart event, because your script is connecting to the database every time the checkLogin function is called, which is very un-efficient and will bring major lag to your server.

Your script should be something like this:

addEventHandler ( "onResourceStart", resourceRoot, function ( ) 
    dbHandle = dbConnect("mysql", "dbname=mta;host=127.0.0.1", "root", "*****") 
    dbExec ( dbHandle, "CREATE TABLE IF NOT EXISTS players (id TEXT, name TEXT, password TEXT") 
 end ) 
  
function checkLogin(username, password) 
    if isElement(dbHandle) then 
        local result = dbPoll ( dbQuery ( dbHandle, "SELECT `id` FROM `players` WHERE `name` = ? AND `password` = ?", username, password) ) 
         
    else 
        outputChatBox("Couldn't connect to database.") 
    end 
end 

Edited June 29, 2014 by Guest

[Xabi]

Thanks for the explanation, I will try like that. The handle is just for testing purposes as I'm new to LUA, when I get deep on it, will change.