SQL

[ViRuZGamiing]

There's something I don't seem to find any info about and that's about how to create tables in SQL,

I have a database called 'mta' and a table called 'user' and I have a few things that I know, these things;

user_name is varchar

user_id is integer and A_I for the automatic numbering

But apart from the name and type, what has to be specified?

[tosfera]

The length, that's basically it. Creating an advanced table would require a bit more knowledge like; not null, datatypes, relations and shit. But as long as you validate your script good enough, you don't need all that bullcr*p.

Creating a table is quite easy;

CREATE TABLE `users` ( 
    `id` int NOT NULL AUTO_INCREMENT, 
    `username` varchar(45) NOT_NULL, 
    `password` text 
) 

That'll create the automatical increment for ids too.

[ViRuZGamiing]

Oh so you create it inside lua because I was trying to do this in the XAMPP Phpmyadmin panel.

[tosfera]

That's even easier, go to the "TABLES" place and scroll to the bottom and click; create new table. Fill in the amount of columns and the name. Each column should use the "NOT NULL" thing ( don't check the NULL box ) and check the AI for the id. Besides that, fill in the datatypes, field names and the length.

Since you're speaking Dutch, you don't need any explanation for the image;

full res on; http://puu.sh/mLxMz/a9288c6bd0.png

( left the length of values out for id, level and banned since the default length for int goes to 11. Quite too much for a level and banned. Level can be 3 ( goes up to 999 ) and banned can be 1 ( from 0 to 9 ) ).

[ViRuZGamiing]

Thx exactly what I needed

I did need to put my A_I on PRIMARY tho else I couldn't create it.

[ViRuZGamiing]

connection = dbConnect( "mysql", "dbname=mta;host=127.0.0.1", "mta", "***", "share=1" ) 
  
if (connection) then 
    outputServerLog("Connected!") 
end 
  
function queryDb (query) 
    local qh = dbQuery(connection, tostring(query)) 
    local result = dbPoll(qh, -1) 
    if result == nil then 
        dbFree(qh) 
    else 
        return result 
    end 
end 
  
addEventHandler("onPlayerConnect", getRootElement(), function(playerNick, playerIP, playerUsername, playerSerial, playerVersionNumber) 
    nameSearch = queryDb("SELECT user_name FROM user WHERE user_name='"..playerNick.."'") 
    if (nameSearch) then 
        -- login 
    else 
        queryDb("INSERT INTO user ('user_name') VALUES ('"..playerNick.."')") 
        queryDb("INSERT INTO user ('user_ip') VALUES ('"..playerIP.."') WHERE user_name='"..playerNick.."'") 
    end 
end) 

So I'm trying some basic SQL, the connection works but it seems I can't anything to my database.

Full image: http://prntscr.com/9vjx3b

[tosfera]

 queryDb("INSERT INTO user ('user_name') VALUES ('"..playerNick.."')") 
        queryDb("INSERT INTO user ('user_ip') VALUES ('"..playerIP.."') WHERE user_name='"..playerNick.."'") 

Those will fail, you're not allowed to insert NULL values into every field. So if you want to insert them all, make 1 query out of it:

queryDb ( "INSERT INTO `user` ( `user_level`, `user_name`, `user_password`, `user_ip` ) values ( 1, ?, '', ? )", playerNick, playerIP ) 

[ViRuZGamiing]

 queryDb("INSERT INTO user ('user_name') VALUES ('"..playerNick.."')") 
        queryDb("INSERT INTO user ('user_ip') VALUES ('"..playerIP.."') WHERE user_name='"..playerNick.."'") 

Those will fail, you're not allowed to insert NULL values into every field. So if you want to insert them all, make 1 query out of it:

queryDb ( "INSERT INTO `user` ( `user_level`, `user_name`, `user_password`, `user_ip` ) values ( 1, ?, '', ? )", playerNick, playerIP ) 

Changed it and still didn't add anything to my Phpmyadmin page

[tosfera]

Try an approach like this;

local connection; 
  
addEventHandler ( "onResourceStart", getResourceRootElement ( getThisResource() ), 
    function() 
        connection = dbConnect( "mysql", "dbname=mta;host=127.0.0.1", "mta", "***", "share=1" ); 
        if ( connection ) then 
            outputServerLog ( "MySQL connection has been established." ); 
        else 
            outputServerLog ( "Couldn't connect to the MySQL server." ); 
        end 
    end 
); 
  
function dbSelect ( query ) 
    if ( connection ) then 
        local tmpQuery = dbQuery ( connection, tostring ( query ) ); 
        return dbPoll ( tmpQuery, -1 ); 
    end 
    return false; 
end 
  
function dbInsert ( query ) 
    if ( connection ) then 
        dbQuery ( connection, tostring ( query ) ); 
    end 
end 
  
addEventHandler ( "onPlayerJoin", getRootElement(), 
    function () 
        local playerNick = getPlayerName ( source ); 
        local isValidPlayer = dbSelect ( "SELECT `user_id` FROM `user` WHERE `user_name` = '".. playerNick .."'" ); 
        if ( isValidPlayer and isValidPlayer [ 0 ] ~= nil ) then 
            if ( isValidPlayer [ 0 ].user_password ~= "" ) then 
                -- log in the user if he fills in a password or what so ever. 
            else 
                -- not a valid user 
            end 
        else 
            dbInsert ( "INSERT INTO `user` ( `user_name`, `user_level`, `user_password`, `user_ip` ) VALUES ( '".. playerNick .."', 1, '', '".. getPlayerIP ( source ) .."' )" ); 
        end 
    end 
); 

note: I never use this way of handling a database. I always go for the outdated mysql plugin.

[ViRuZGamiing]

dbPoll failed; You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ''user' WHERE 'user_name' = 'Viruz'' at line 1 

[tosfera]

Make sure that the quotes are a backtick/grave ( ` ) and not a quote in your script. you might want to remove them.

[ViRuZGamiing]

Woops, sorry my bad, I changed them. Now it indeed works. Thanks `again`