[cessil]

I've just learned the basics of SQLite not so long ago and I found it hard to find any tutorials on it so I said to myself when I learn enough to understand it I'll write a tutorial.

1. My first database
so first off you need to create a database variable, these have their own variable type just add DB: as a prefix when creating them

pawn Код:

new DB:top_players;

now we'll open up the database when we start the gamemode and close it when the gamemode closes

pawn Код:

public OnGameModeInit()
{
    top_players = db_open("stats.db");//here top_players now equals the database 'stats.db', if this doesn't exist it'll be created in your scriptfiles folder
}
public OnGameModeExit()
{
    db_close(top_players);
    return 1;
}

ok now we have a database! I found it much easier to work with databases when visualizing them, here's my understanding of the hierarchy.

DATABASE => TABLE => ROWS/COLUMNS

I think there's more things like you can have a table group, but for now we'll leave it as it is.

2. My first table
ok so we've created a database file but it has nothing in it, so will create our first table.
Under OnGameModeInit we'll create the table

pawn Код:

public OnGameModeInit()
{
    top_players = db_open("stats.db");
    
    db_free_result(db_query(top_players,"CREATE TABLE IF NOT EXISTS `Users` (`Name`,`stat`)"));//query the database and free the result
}

what we've done here is sent "CREATE TABLE IF NOT EXISTS `Users` (`Name`,`stat`)" to the database.

NOTE: if you want to add more than 1 stat, then just expand it like this

pawn Код:

"CREATE TABLE IF NOT EXISTS `Users` (`Name`,`stat`,`stat2`,`stat3`,`stat4`)"

I'll write what it might look like in pawn so you can understand it better

pawn Код:

if(!table_exists(users))
{
    new table:users;
    table_add_column(users,name);
    table_add_column(users,stat);
}//this is made up code and will not work, it's just so you can understand how sqlite works

and in English "If the table "users" does not exist create it, and give it two columns called "name" and "stat"".

3. Adding data
now we've set up the basics of database, we can now add data into it

here I have my save data function that gets called when ever it needs to save player data

pawn Код:

public savedata(playerid)
{   
    new tname[MAX_PLAYER_NAME];
    GetPlayerName(playerid,tname,sizeof(tname));//gets the player's name

    new query[256];
    format(query,sizeof(query),"SELECT `Name` FROM `Users` WHERE `Name`=lower('%s') LIMIT 1",tname);
/*ok first query is asking to select a from the `Name` column from the table `Users` 
and the rule we're saying is only select the name that matches the players name
I limited it to 1 so it won't keep looking for more matches after it's found one, you'll notice that I used "lower('%s')"
what this does is prevents the user from joining with a capitalized alternative of their name and having the database add that one as well.*/
    new DBResult:qresult;//new database result variable, they have their own var type too.
    qresult = db_query(top_players,query);//the result equals the query we sent off.
    if(db_num_rows(qresult) == 1)//if it's found a match that means the user is in the database so we'll be updating their stat and not creating a new one.
    {
        format(query,sizeof(query),"UPDATE `Users` SET `stat`='%d' WHERE (`Name` = lower('%s'))",GetPVarInt(playerid,"stat"),tname);
/*update will get the player's row and update variables in that row
"UPDATE `Users` SET `stat`='%d' WHERE (`Name` = lower('%s'))"
update the table `Users`, set the variable `stat` on the same row as the players name.*/
        db_free_result(db_query(top_players,query));//query the database and free the query result after it
    }
    else if(db_num_rows(qresult) == 0)//if it hasn't found a match it'll return 0 here
    {
        format(query, sizeof(query), "INSERT INTO `Users` (`Name`,`stat`) VALUES(lower('%s'),%d)",tname,GetPVarInt(playerid,"stat"));
/*Insert a new row into the table `Users`, in the columns `Name` add their name in lowercase, in the `stat` column add their stat*/
        db_free_result(db_query(top_players,query));
    }
}

ok now that's a database storing data for us, now that's pretty useless if we can never read it.

4. Reading data from a database

here's from the wiki with a little more explanation. https://sampwiki.blast.hk/wiki/Db_get_field

pawn Код:

new query[256],string[128],string2[128];
        new DBResult:qresult;
        format(query,sizeof(query),"SELECT Level FROM `users` WHERE Username = '%s'", PlayerName[playerid]);
//here it says select from the column level from the table `users` and the row is the same row as the player's name row
        qresult = db_query(USERDB,query);
        if(db_num_rows(qresult) == 1)//checks if there's a match on the player's name row.
        {
                db_get_field(qresult,0,string,128);//gets the result of the query as a string.
                format(string2,sizeof(string2),"Your admin level is: %s",string);
                SendClientMessage(playerid,0xAFAFAFAA,string2);
        }
        db_free_result(qresult);//frees the result
        return 1;

but what we want is a top x, but that's more if you're creating a sqlite registration system.

5. Returning a Top x

we'll jump right into this one without so much explaining

pawn Код:

CMD:topstat(playerid,params[])
{
    new string[128], query[256];
    SendClientMessage(playerid,GREEN,"Top X Y'ers");
    new string2[128];
    new DBResult:result;
    new DBResult:result2;
    format(query,sizeof(query),"SELECT `Name` FROM `Users` ORDER BY (`stat` * 1) DESC limit 3");
/*Select from the `Name` column in the table `Users`, and order them by stat on a descreasing list limited to the top 3
I had ('stat` *1) because without it it'll look at it as if it's a string, timing it by 1 will turn it into a number.*/
    result = db_query(top_players,query);
    for(new a;a<db_num_rows(result);a++)//what we have now is 10 rows in the database result, so we'll do a loop to show each one on a new line.
    {
        db_get_field(result,0,string,128);//get the result of the database and format it into a string
//at the moment this will return the first row, we need to switch it using a function.
        format(query,sizeof(query),"SELECT `stat` FROM `Users` WHERE `Name` = lower('%s')",string);
/*this result2 will get the data from the stat column from the row that matches the first row of the "result" variable
"result" is already in order from 1-10, "result" will be reading the 1st row at the moment or the player with the highest stat*/
        result2 = db_query(top_players,query);
        db_get_field(result2,0,string2,128);//get the `stat` column from the row of the first player.
        format(string,sizeof(string),"%d. %s Stat: %s",a+1,string,string2);//it's a+1 because the result starts at 0, this will display as "1. Name Stat: X".
        SendClientMessage(playerid,ORANGE,string);
        db_next_row(result);//this function changes to the next row down, then when it goes into the next loop cycle it'll get the second top player and so forth.
        db_free_result(result2);//remember to free any results you don't need anymore
    }
    db_free_result(result);
    return 1;
}

I'm still new to sqlite so if I've made a mistake I've made let me know please