MySQL database for account info

[Solstice.]

Hello.

Basically I've been working on a log-in system and want to use a MySQL database to save account information. I've worked with MySQL before in school and know about queries and used it back when I ran a WoW RP server however I seem to fail in integrating a variable in the query.

So to explain from scratch I have all the MySQL modules installed and have a function allowing me to access the database.

function mysql_con () 
    handler = mysql_connect("HOST", "NAME", "PASS", "DATABASE")  
    if (not handler) then 
        outputChatBox("Unable to connect to the MySQL server") 
    else 
        outputChatBox("Hello",client) 
    end 
end 
addEventHandler ("onServerResourceStart", getRootElement(), mysql_connect ) 

Then I want to make it so if you hit the login button on the login screen which is visible on connecting it checks both the username and password in the database. It then triggers another event spawning the player etc.:

function submitlogin () 
    local username_editbox = guiGetText ( username_box ) 
    local username = mysql_query(handler, "SELECT account_name FROM account_info WHERE account_name = "username_editbox"") 
    if source == login_button then 
        if username then 
            outputChatBox ( "HELLO") 
            triggerServerEvent ("onLogin", getRootElement(), username_editbox) 
            guiSetVisible (GUIlogin, false) 
            guiSetVisible (ariabanner, false) 
            outputChatBox(" ") 
            outputChatBox(" ") 
            outputChatBox(" ") 
            outputChatBox(" ") 
            outputChatBox(" ") 
            outputChatBox(" ") 
            outputChatBox(" ") 
            outputChatBox(" ") 
            outputChatBox(" ") 
            outputChatBox(" ") 
            outputChatBox(" ") 
            outputChatBox(" ") 
            showCursor(false) 
            guiSetInputEnabled(false) 
        else 
            outputChatBox ( "LOLMAN ") 
        end 
    end 
end 
  
addEventHandler ( "onClientGUIClick", getRootElement(), submitlogin, true) 

[Castillo]

You are using mysql in a client side script, mysql is server side only.

[Cadu12]

And please use MD5 for password, its good idea.

[myonlake]

And a salt if possible.

[Solstice.]

You are using mysql in a client side script, mysql is server side only.

That worked, thank you, Castillo. Sort of got me on the right path although I'm having issues realising the setup parsing the client info towards the server script.

So basically I'm stating the variable 'username_editbox' and 'password_editbox' is whatever I filled out in the login GUI and I parsed this to my server-side script using a trigger event. Now I'm checking whether 'username' and 'password' returns true (although I'm not sure whether I did this right and I probably didn't) in order to spawn the player on condition of the username and password matching an existant account so I can bind further details to it. I'm rather new to MySQL integration.

function submitlogin () 
    local username_editbox = guiGetText ( username_box ) 
    local password_editbox = guiGetText ( password_box ) 
    if source == login_button then 
        triggerServerEvent ("onLogin", getRootElement(), username_editbox, password_editbox) 
        guiSetVisible (GUIlogin, false) 
        guiSetVisible (ariabanner, false) 
        showCursor(false) 
        guiSetInputEnabled(false) 
    end 
end 
  
addEventHandler ( "onClientGUIClick", getRootElement(), submitlogin, true) 

function login_seq (thePlayer) 
    local username = mysql_query(handler, "SELECT account_name FROM account_info WHERE account_name = "username_editbox"") 
    local password = mysql_query(handler, "SELECT account_password FROM account_info WHERE account_password = "password_editbox"") 
    if username and password then 
        spawnPlayer (client, 1687.7318115234, 1448.0688476563, 10.768267631531, -90, 0, 0) 
        setCameraTarget (client, client) 
        setElementDimension ( getRootElement(), 0 ) 
    end 
  
addEvent ("onLogin", true) 
addEventHandler ("onLogin", root, login_seq) 

How do I integrate the variable string in the query? I get an error line: ')' expected near 'username_editbox'

[Castillo]

You have to define the arguments server side.

Client side:

triggerServerEvent ( "onLogin", localPlayer, username_editbox, password_editbox ) 

Server side:

function login_seq ( username, password ) 
    local username = mysql_query ( handler, "SELECT account_name FROM account_info WHERE account_name = ".. username .."" ) 
    local password = mysql_query ( handler, "SELECT account_password FROM account_info WHERE account_password = ".. password .."" ) 
    if ( username and password ) then 
        spawnPlayer ( client, 1687.7318115234, 1448.0688476563, 10.768267631531, -90, 0, 0 ) 
        setCameraTarget ( client, client ) 
        setElementDimension ( client, 0 ) 
    end 
end 
addEvent ( "onLogin", true ) 
addEventHandler ( "onLogin", root, login_seq ) 

P.S: You also had a missing 'end' to close the 'if' statement.

[Solstice.]

Seems to work. Got a new error however which is obviously related to the database in itself. Basically can't connect to the database. I allowed my IP for remote SQL control to enter it with third party software. I use Heidi SQL although whenever I try to connect with the correct information I get this error:

Furthermore when calling the function pressing the LOGIN button on the GUI the console outputs the following:

I know the second message is a mere residue of the failed MySQL connection handler though.

[myonlake]

1) Make sure you have all the correct MySQL modules

2) Make sure you have the MySQL library in your MTA San Andreas 1.3/server folder and MTA San Andreas 1.3/server/mods/deathmatch folder

[Solstice.]

1) Make sure you have all the correct MySQL modules

2) Make sure you have the MySQL library in your MTA San Andreas 1.3/server folder and MTA San Andreas 1.3/server/mods/deathmatch folder

Thanks for commenting.

I have all the modules installed to its appropriate locations. This issue is database-side I believe.

[myonlake]

Something I found on Google:

- http://forums.mysql.com/read.php?34,49742,239961

- http://www.heidisql.com/forum.php?t=527

[Solstice.]

Thanks. Failed to fix the issue despite I've tried to for the past 6 hours. This is driving me nuts. ERROR 2003: Can't connect to MySQL server (10060)

Any ideas are welcome.

[Imposter]

I believe that is your db host, before trying it on a host, do it locally, download Winginx or Xampp and use the MySQL and Phpmyadmin from there, I was reading past when I saw your post, I just made a new MySQL stats system using dbConnect etc. So if you need help with the MySQL part, I am here to help.

[Solstice.]

I believe that is your db host, before trying it on a host, do it locally, download Winginx or Xampp and use the MySQL and Phpmyadmin from there, I was reading past when I saw your post, I just made a new MySQL stats system using dbConnect etc. So if you need help with the MySQL part, I am here to help.

Yeah. Locally it didn't work either. I kind of just ditched MySQL and went with SQlite instead. Seems to bring some benefits and MySQL feels a bit overkill for me right now. Thank you regardless.