[PCheriyan007]

TRANSLATED FROM ROMANIAN FORUMS BY PCHERIYAN007
Original by Zh3r0

Introduction
- Many of you think that SQLite is very difficult to understand, it is actually quite simple! I decided to make this tutorial because I got bored and want to show you that this is a easier system to use.
You can trust SQLite 99.9%. If set up properly, you will not have memory leaks or lack of time.


What is SQLite?
- SQLite is a kind of MySQL but uses an internal database. The database is a file with a .db extension. SQLite is based on tables and columns, it is very easy to use and has many useful functions with which you can do more than than of writing files.
SQL stands for Structured Query Language. In SQL everything is based on strings with key functions which lead to an action.
You need a Database Manager to read information!
Download it HERE


Clause Use:

• Clause CREATE TABLE IF NOT EXISTScreate the table under OnFilterScriptInit, OnGamemodeInit or wherever you feel necessary.
• Clause FROM specified in the table to make certain information.
• ClauseWHERE specifies the location of an item in the table, for example, we want to look at`Kills` where `Name` is 'Zh3r0'. It is used as a reference point.
• Clause ORDER BY orders a particular column by title, ascending, descending.
• Clause SELECT `State` select. It can be used in conjunction with the FROM and WHERE. Gender, "SELECT * FROM` table `WHERE` Name `= 'Zh3r0'", which means that check where the name is Zh3r0 there.
• Clause AND can be used to strengthen a verification like "SELECT * FROM` table `WHERE` Name `= 'Zh3r0' AND` Password `= 'blabla'" check in `name` is yet Zh3r0 'Password' to have 'blabla'
• Clause UPDATE table. Updates a row if there is something there, otherwise nothing will happen.
• Clause INSERT INTO inserts a new row in the table, even if there turn, will be duplicated, so you should refer to UPDATE clause when you want to update the row.
• Clause DELETE FROM delete a row in the table with the WHERE clause is used to determine which row will be deleted.
  - There are several clauses, but I listed the most important and widely used in SA-MP.

How To:

How to create a database?
Use the db_open();.
First create a variable with the tag DB because we want the variable to be recognized as a member of SQL.
Write at the top of the script

pawn Код:

new DB:Database;

Go to the callback OnFilterScriptInit() if FS or OnGameModeInit() if GM
Add the following code:

pawn Код:

Database = db_open("Database.db");

The code above will create database and opens if there is one, to receive information or give information.
Sub token add this:

pawn Код:

db_free_result(db_query(Database, "CREATE TABLE IF NOT EXISTS `Table` (`Name`, `Money`, `Ratio`)"));

The above code will create the file and table inside of Database.db, located in the scriptfiles folder, the same time if you add exist. In 3 columns, those listed in ().
Success! You created a database and have also have added 3 columns!

What is db_query() and db_free_result()?

pawn Код:

db_query()

This function can be read and written at the same time, depends on what you wrote in that string, and what you use clause.

pawn Код:

db_free_result()

You must insure that every time you read or write in a database, to release the results, otherwise they would cause a memory leak bug, which you do not want happening!


How do I write in a database?

Using the db_query().
As an example we will work in OnFilterScriptInit() .

You should not open the database with the db_open function() since it was opened first.
Let's write something in those three columns in the table called Table.

pawn Код:

/ / create a string called Query that you could dd man data, like player names, Score, etc. Kill Streak.
new Query [129];

//Formed string name and all that after you use db_query () to write.
format(Query, sizeof(Query), "INSERT INTO `Table` (`Name`, `Money`, `Ratio`) VALUES('%s', '%d', '%f')", ("Zh3r0"), 500, Float:555 / Float:34);

//Now write in database I wrote above, well not really, the codes above will be turned into an order that databases understand and transform them into something physical and processes in and out a new line.
db_query(Database, Query);

//If you want to be sure that any remnant of information release, do this.
db_free_result (db_query (Database, Query)) / / At the same time release the information that is necessary and act.

You can now open the folder scriptfiles Database.db file folder with the Database Manager made ​​available to you in the Introduction above.
When you open the database you should have something



How do I read from a database?
Now let's make a console command to avoid entering the server and all.
Add this function somewhere in the script, if already there, just put in and take what's there.

pawn Код:

public OnRconCommand(cmd[])
{
    if(!strcmp(cmd, "/start", .length = strlen("/start")))
    {
     
    }
    return 1;
}

Now if you type in the console /start nothing will not happen because it is empty!
Now let's fill it with the code for reading!

pawn Код:

if(!strcmp(cmd, "/start", .length = strlen("/start")))
{
     /Create a string called Query that we made ​​the code to retrieve information.
     new Query[256];

     //Create a variable to store the result received from db_query().
     new DBResult:Result;

     //Query string formatted with the necessary data acquisition information.
     //In this format Check the table `table` there is a row containing the column 'Name' word Zh3r0.
     format(Query, sizeof(Query), "SELECT * FROM `Table` WHERE `Name` = 'Zh3r0'");

    //With this code you read what I asked above, I wrote to store result = db_query information received to check if what I wrote above is valid.
     Result = db_query(Database, Query);

     //Now, using db_num_rows function to check if we received a valid information or not, if there Zh3r0 the table column `Name`.
     //First check if it is true, if indeed, there is a 'Zh3r0' in the column 'Name' table.
     if(db_num_rows(Result))
     {
           //If that was the correct result, the console will print what I wrote below.
           print("The result was correct, there is a table column Zh3r0 `Name`");
   
           //Create a string that store information received from the database.
           new Field[30];
    
           //Create variables to store information.
           new Money, Float:Ratio;

           //Now we take the data from column 'Ratio' and 'Money' from `Name` equal Zh3r0 name.
            db_get_field_assoc (Result, # Money, Field 30);

           //Field Strngul now received information from the `Money` where `name` = 'Zh3r0'
           //Create and store information in a variable which can be done by you, like pInfo[playerid][Money]
           Money = strval(Field);//strval because information is a string and must become integer. (EX: 100)
  
           //Ratio was part of `Table` where `Name` = 'Zh3r0'
           db_get_field_assoc(Result, #Ratio, Field, 30);

           //floatstr because the information in Field is a string and must be turned into a float (eg 4.7)
           Ratio = floatstr(Field);

           //Now to print information received!
           printf(#Money: %d","# Ratio: %.4f, Money, Ratio);
}
//I have taken the results from `Table` where `Name` = 'Zh3r0' successfully, now to release the excess information!
db_free_result(Result);

Now in the console should appear:
Money: 500 Ratio: 16.3235


How do I delete a line in the table?

Simple! Using DELETE FROM clause.

Example of deletion:

pawn Код:

db_query(Database, "DELETE FROM `Table` WHERE `Name` = 'Zh3r0'");

Will delete all found in the line where `Name` = 'Zh3r0'.

Useful information!

• Why did I use ``? Because, when you add a column without `` it is more likely to have memory leaks, using these two apostrophes help accuracy.
• Why he wrote or something I used ''? As above, information to be specified!
• Why I used ("Zh3r0")? It is just an example, one can become like Name (playerid) or NEAP, which is a variable that contains the name of the player.

Download a simple Login / Register script done with SQL.
DOWNLOAD HERE!
Once downloaded, you will enter the game and yes /register <password>.
Password must be at least 5 characters and max 24.
Once you have created your account, go to scriptfiles and open the file called Database.db using the program mentioned above.
Inside must have:


But your data!


### If you have questions ask Zh3r0.

### If you see anything written incorrectly in this thread PLEASE let me (PCheriyan007) know.

Credits

• Zh3r0 - writing the original tutorial in the Romanian Forums
• PCheriyan007 - translating from Romanian to English
• ****** Translate - I'll let you figure this one out