[Tutorial] Top x players using sqlite
#1

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
Reply
#2

very nice tutorial
Reply
#3

Thanks for that Very well weiten and explaned

Just fix few mystics in new DB:top_players add the ; so it will be
Код:
new DB:top_players;
and in 3. Adding data In the end of the code add missing }
Reply
#4

Nice work.
Reply
#5

Quote:
Originally Posted by Emirt
Посмотреть сообщение
Thanks for that Very well weiten and explaned

Just fix few mystics in new DB:top_players add the ; so it will be
Код:
new DB:top_players;
and in 3. Adding data In the end of the code add missing }
alright fixed, and thanks guys
Reply
#6

Nice tut!
I even didn't know that this exists XD.
Whatever I have an tip to save diskspace. It's about this part:
pawn Код:
public OnGameModeInit()
{
    top_players = db_open("stats.db");
   
    new query[256];//create a string (I'm not sure if it has to be this big every mysql tutorial I saw had 256 strings
    format(query,sizeof(query),"CREATE TABLE IF NOT EXISTS `Users` (`Name`,`stat`)");//format the string
    db_free_result(db_query(top_players,query));//query the database and free the result
}
You first format the string. That's now not needed, there are no ... idk how it's called but there are not things like this: '%d', '%s', '%i' etc...
So you can just run the query on this way:

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
}
Reply
#7

Nice
Reply
#8

The query format size doesn't need to be 256, people just use it so there is ample space for the query. Some for me are smaller so i may use query[100] because i know that with the characters inside the format and the maximum characters of what i'm inputing.
Reply
#9

Since I still don't know MySQL or SQLite, this will might help me to learn it.
Reply
#10

Why:
Quote:

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
}

not just:
Quote:

db_query(top_players,"CREATE TABLE IF NOT EXISTS `Users` (`Name`,`stat`)");//query the database and free the result

You don't have any DBResult there ... ?
Reply
#11

Hah, I've been waiting for this for a long time now. Thank you. I can finally start working with SQLite and understand it. Great tutorial.
Reply
#12

Hey i've question.

If the database has only 1 row (i mean 1 column) - let me make it easier, i mean 1 player.

How could i display the 2nd, 3rd, 4th and 5th as

"-----"

Example

"1. Jake_Hero Money: $952422
2. --------------------------
3. --------------------------
4. --------------------------
5. --------------------------
"

If there are 2 players registered, then the slot 2 will be available and so on.
Reply


Forum Jump:


Users browsing this thread: 1 Guest(s)