Failed to insert row in MySQL

[Phoenix-Roleplay]

First time using GUI's or working with MySQL and upon using it I get the following error from server/MySQL

INFO: Registration failed. Unknown column `Phoenix` in `field list` 

Where `Phoenix` is the username

Code:

function registrationHandler(username,password) 
    local row = mysql_query(handler, "SELECT * FROM accounts WHERE username=`"..username.."`") 
    if row then 
        outputDebugString("Username already exists") 
        triggerClientEvent(client, "displayLogin", getRootElement()) 
        return 
    end 
    local result = mysql_query(handler, "INSERT INTO accounts (`accname`, `accpassword`) VALUES (`"..username.."`, `"..password.."`)") 
    if not result then 
        outputDebugString("Registration failed. "..mysql_error(handler)) 
        triggerClientEvent(client, "displayLogin", getRootElement()) 
        return 
    end 
    triggerClientEvent(client, "displayLogin", getRootElement()) 
    outputChatBox("Registration successful!", client, 0, 255, 0) 
end 
addEvent("submitRegister",true) 
addEventHandler("submitRegister",root,registrationHandler) 

[NeXTreme]

  
local result = mysql_query(handler, "INSERT INTO accounts (accname, accpassword) VALUES ('"..username.."', '"..password.."')") 
  

[Phoenix-Roleplay]

Registration works perfect now!

Only issue is login does not show any errors in the console only thing it says is "Unable to find username"

function loginHandler(username,password) 
    local row = mysql_query(handler, "SELECT * FROM accounts WHERE accname='"..username.."'") 
    if (username ~= row) then 
        outputDebugString("Unable to find username. " .. mysql_error(handler)) 
        triggerClientEvent(client, "displayLogin", getRootElement()) 
        outputChatBox("Unable to login.") 
        return 
    end 
    if (password ~= row.password) then 
        outputDebugString("Incorrect Pasword Supplied") 
        triggerClientEvent(client, "displayLogin", getRootElement()) 
        return 
    end 
    outputChatBox("You have successfully logged in!", client, 0, 255, 0) 
end 
addEvent("submitLogin",true) 
addEventHandler("submitLogin",root,loginHandler) 

[Anderl]

    local row = mysql_query(handler, "SELECT * FROM accounts WHERE accname='"..username.."'") 
    if (username ~= row) then 

->

    local row = mysql_query( handler, "SELECT * FROM `accounts` WHERE accname = '"..username.."' " ); 
    if( mysql_num_rows( row ) == 1 ) then 
        if( row[1]['accname'] ~= username ) then 

[Phoenix-Roleplay]

ERROR: pf\s_main.lua:22: attempt to index field `?` (a nil value) 

Line 22:

if(row[1]['accname'] ~= username) then 

[Cadu12]

  
local row = mysql_query( handler, "SELECT * FROM `accounts` WHERE accname = '"..username.."' AND password = '" .. password .. "'" ); 
    if( mysql_num_rows( row ) == 1 ) then 
        return true 
    else 
        return false 
    end 
  

[Phoenix-Roleplay]

Um.. I'm not sure what you want me to do with that snippet.

EDIT: Nevermind, I got it but now it shows "Incorrect Password Supplied"

Code:

function loginHandler(username,password,thePlayer) 
    local row = mysql_query( handler, "SELECT * FROM `accounts` WHERE accname = '"..username.."' " ); 
    if( mysql_num_rows( row ) == 1 ) then 
        if (row['password'] == password) then 
            outputChatBox("You have successfully logged in!", client, 0, 255, 0) 
            return 
        end 
    else 
        outputChatBox("Unable to find username", client, 255, 0, 0) 
    end 
    outputDebugString("Incorrect Pasword Supplied") 
    triggerClientEvent(client, "displayLogin", getRootElement()) 
end 
addEvent("submitLogin",true) 
addEventHandler("submitLogin",root,loginHandler) 

[Cadu12]

function loginHandler(username,password) 
    local row = mysql_query(handler, "SELECT * FROM accounts WHERE accname='" .. username .. "' AND password='" .. password .. "'") 
    if row then 
        outputChatBox("You have successfully logged in!", client, 0, 255, 0) 
        mysql_free_result(row) 
        return true 
    else 
        outputChatBox("Your username and/or password is incorrect!", client, 0, 255, 0) 
        triggerClientEvent(client, "displayLogin", getRootElement()) 
        return false 
    end 
end 
addEvent("submitLogin",true) 
addEventHandler("submitLogin",root,loginHandler) 

Edited July 14, 2012 by Guest

[Phoenix-Roleplay]

One error:

pf\s_main.lua:13: bad argument #1 to `mysql_num_rows` (msqlResult expected, got nil) 

[Cadu12]

Try my code, I edited it.

[Phoenix-Roleplay]

No matter what I put it says "username or password is incorrect"

[Cadu12]

Hmm, is there 'password' column in your mysql(?), try check it.

[Phoenix-Roleplay]

*facepalm* this is the second time this has happened to me, it's accpassword. Gonna fix it and I'll let you know if it works.

EDIT: Any password logs you in.

function loginHandler(username,password) 
    local row = mysql_query(handler, "SELECT * FROM accounts WHERE accname='" .. username .. "' AND accpassword='" .. password .. "'") 
    if row then 
        outputChatBox("You have successfully logged in!", client, 0, 255, 0) 
        mysql_free_result(row) 
        return true 
    else 
        outputChatBox("Your username and/or password is incorrect!", client, 255, 0, 0) 
        triggerClientEvent(client, "displayLogin", getRootElement()) 
        return false 
    end 
end 
addEvent("submitLogin",true) 
addEventHandler("submitLogin",root,loginHandler) 
  

EDIT 2:

Switching back to this

function loginHandler(username,password) 
    local row = mysql_query(handler, "SELECT * FROM accounts WHERE accname='" .. username .. "' AND accpassword='" .. password .. "'") 
    if( mysql_num_rows( row ) == 1 ) then 
        outputChatBox("You have successfully logged in!", client, 0, 255, 0) 
        return true 
    else 
        outputChatBox("Your username and/or password is incorrect!", client, 255, 0, 0) 
        triggerClientEvent(client, "displayLogin", getRootElement()) 
        return false 
    end 
end 
addEvent("submitLogin",true) 
addEventHandler("submitLogin",root,loginHandler) 

Works perfectly. Thanks so much.

[robhol]

Also, just so you know, storing passwords in cleartext is an incredibly bad idea. Hash them, MySQL provides SHA1() which is mostly adequate. You might also want to add a salt.

[Phoenix-Roleplay]

I know, its all local right now just trying to get as familiar as I can with MySQL and GUI's

[Cadu12]

I thought it was "password" column, sorry, did it worked?

[Phoenix-Roleplay]

Yes, it works.

Now using your code:

function loginHandler(username,password) 
    local row = mysql_query(handler, "SELECT * FROM accounts WHERE accname='" .. username .. "' AND accpassword='" .. password .. "'") 
    if( mysql_num_rows( row ) == 1 ) then 
        outputChatBox("You have successfully logged in!", client, 0, 255, 0) 
        return true 
    else 
        outputChatBox("Your username and/or password is incorrect!", client, 255, 0, 0) 
        triggerClientEvent(client, "displayLogin", getRootElement()) 
        return false 
    end 
end 
addEvent("submitLogin",true) 
addEventHandler("submitLogin",root,loginHandler) 

Will I still be able to pull data using row.x or row[x]? Or how would I go about doing this?

[Cadu12]

row[1]["accname"] or row["accname"]

Not tested it.

[Phoenix-Roleplay]

setElementData(client, "logged", 1) 
        setElementData(client, "account:admin", row["admin"]) 
        if (getElementData(client,"account:admin") > 0) then 
            AStatus = getElementData(client,"account:admin") 
            if (AStatus == 1) then 
                outputChatBox("You are playing as a PR Moderator.", client, 0, 0, 255) 
            elseif (AStatus == 2) then 
                outputChatBox("You are playing as a PR Administrator.", client, 0, 0, 255) 
            elseif (AStatus == 3) then 
                outputChatBox("Welcome back, Phoenix.", client, 0, 0, 255) 
            end 
        end 

ERROR: pf\s_main.lua:17: attempt to compare number with nil 

[Cadu12]

setElementData(client, "logged", 1) 
setElementData(client, "account:admin", tonumber(row["admin"]) or 3) 
local AStatus = tonumber(row["admin"]) or 3 
if (AStatus == 1) then 
    outputChatBox("You are playing as a PR Moderator.", client, 0, 0, 255) 
elseif (AStatus == 2) then 
    outputChatBox("You are playing as a PR Administrator.", client, 0, 0, 255) 
elseif (AStatus == 3) then 
    outputChatBox("Welcome back, Phoenix.", client, 0, 0, 255) 
end 

Did you forget about tonumber? ;/

Edited July 14, 2012 by Guest

[Phoenix-Roleplay]

Well paint me blue and call me betsy! ...I'm serious.

All joking aside, I guess I was completely oblivious of it

EDIT: tonumber doesn't seem to have fixed anything although I am no longer geting the error message... Hmmm..

[Cadu12]

Hmm, try my code, if it didnt worked, try debug with outputChatLog

[Phoenix-Roleplay]

I tried yours, it works without a doubt due to the fact that if account:admin was not set to anything above 0 (at least that we know of) it sets it to 3, so I changed it and switched it to 2 instead and whenever I join it says "PF Administrator" the issue it seems to be having is pulling the data from the row, as before we we're having this issue.

Anyways, not getting any errors at all.

[Cadu12]

Im not sure, but maybe use "row[1]["admin"]" at 2 and 3 lines.

[Phoenix-Roleplay]

For some reason whenever I use row[1]["insert-block-here"] I get the following:

ERROR: pf\s_main.lua:16: attempt to index field `?` (a nil value)