[RIDE2DAY]

How to connect to a MySQL database

[*] Index

• Introduction
• Installing a database management system
• Installing the MySQL plugin
• Creating a database
• Connecting to a database
  • mysql_connect
  • mysql_connect_file
• Real applications
• The end

[*] Introduction
Hello, this is the first part of a series of tutorials about MySQL I'm going to write related to the MySQL plugin wrote by BlueG and maddinat0r. First of all, I'd like to tell you that I'm not native English so if you find any grammatical error just let me know. On the other hand, everything you're going to read is focused on the current version of the plugin (R41-4). On this first part I'll cover something basic, maybe everyone knows how to do it but I decided to start from scratch so new people might follow these guides. Needless to say, suggestions and code/technique improvements are welcome.


[*] Installing a database management system
In order to use this plugin in our computer we must install a database management system, in this case: MySQL. If you're using Windows I recommend to install XAMPP or WAMP because they provide the phpMyAdmin tool. In case of you're a Linux user I suppose you know how to install MySQL (I won't explain it here because it depends on the distribution used). For this series I'm going to use XAMPP.

Alright, if you're using Windows, you're new into this and you don't know how to install XAMPP or WAMP, don't worry, don't get nervous because here you have these magnificent well-documented guides:

• How to install XAMPP
• How to install WAMP

A funny fact is that XAMPP installs MariaDB! MariaDB is a fork of the original MySQL system mantained by the original founders after they sold MySQL to the Oracle Corporation. Currently BlueG's plugin uses MariaDB, so yes, maybe XAMPP is the way to go. Anyway, MariaDB is fully compatible with MySQL.


[*] Installing the MySQL plugin
In order to install the MySQL plugin on your SA-MP server just follow the next steps:

1. Go to the releases section on GitHub.
2. Download the latest Windows version, in this case: mysql-R41-4-win32.zip (released on 10 Sep.).
3. Unzip the .zip file and copy everything to the main folder of your server, replace if it proceeds.
4. Open server.cfg and add mysql to the plugins line, create it if it doesn't exist.
   
   Code:

   plugins mysql

5. Open the .pwn file of your gamemode and add the a_mysql include.
   
   Code:

   #include <a_samp>
   #include <a_mysql>

   Now you might compile your script and run samp-server.exe, it should run fine if you have done everything correctly.

[*] Creating a database
Before connecting to a database we must create one, right? Okay, after installing XAMPP start the Apache and the MySQL modules from the control panel:

Once you started those modules follow these steps:

1. Access the phpMyAdmin tool using the next URL in your web browser: localhost/phpmyadmin
   
   
   
   
2. Click the New button located on the left side:
   
   
   
   
3. Type a database name and click the Create button:
   
   
   
   
4. After creating the database you'll get a message with a blue background saying "no tables found in database", that's alright, the database has been created correctly and now you should be able to see it listed on the left.
   
   

[*] Connecting to a database
Now we're going to establish a connection between the SA-MP server and the database created previously, in the R41-4 version we're able to do that using two different methods:

• mysql_connect: connects to a MySQL server and database using connection credentials and options provided inside the script file.
• mysql_connect_file: connects to a MySQL server and database using a .ini file where all connection credentials and options are specified.

So, which one is better? It's up to you! If you think the credentials are going to be compromised then use the first method. Let's see two examples:

• mysql_connect
  
  Code:

  Parameters:
  const host[]                    -> IP or hostname of the MySQL server.
  const user[]                    -> Username of the account you want to connect to.
  const password[]                -> Password of the account you want to connect to.
  const database[]                -> Name of the database you want to connect to.
  MySQLOpt:option_id = MySQLOpt:0 -> MySQL connection options instance (optional).
  
  Return values:
  Connection handle or MYSQL_INVALID_HANDLE on error.

  As you can see, this is the "old school" way, that which everybody is used to. Pay attention to the parameters, now the password must be specified after the username. The option_id parameter references a set of rules defined previously, you should understand this much better with an example:
  
  PHP Code:

  /* Define connection credentials. */
#define MYSQL_HOST "localhost" 
#define MYSQL_USER "root" 
#define MYSQL_PASS "" 
#define MYSQL_DB   "sa-mp" 
new MySQLOpt:my_options; // Notice the "MySQLOpt" tag.
public OnGameModeInit() 
{ 
    /* Create a set of options and store the ID. */
    my_options = mysql_init_options(); 
    /* Define connection options for the created set. */
    mysql_set_option(my_options, AUTO_RECONNECT, true); 
    mysql_set_option(my_options, MULTI_STATEMENTS, false); 
    mysql_set_option(my_options, POOL_SIZE, 2); 
    mysql_set_option(my_options, SERVER_PORT, 3306); 
    /* Connect to the database using the defined credentials and options. */
    mysql_connect(MYSQL_HOST, MYSQL_USER, MYSQL_PASS, MYSQL_DB, my_options); 
    return 1; 
} 

  The option_id parameter is optional, if you don't specify it default options are used. Check mysql_init_options and mysql_set_option for more information.
  
  
• mysql_connect_file
  
  Code:

  Parameters:
  const file_name[] = "mysql.ini" -> name of the .ini file (optional).
  
  Return values:
  Connection handle or MYSQL_INVALID_HANDLE on error.

  The .ini file must be located inside the main directory, this function doesn't accept subdirectories. Basically, using this function we can keep all connection credentials and options in an external file without the need to define them in our script, that's why I prefer this method rather than the previous one. A small example:
  
  
  • mysql.ini
    
    Code:

    hostname = localhost
    username = root
    database = sa-mp
    auto_reconnect = true
    multi_statements = false
    pool_size = 2
    server_port = 3306

  PHP Code:

  public OnGameModeInit()
{
    mysql_connect_file("mysql.ini");
    return 1;
} 

  This code does exactly the same thing as the code from the first example, and it looks cleaner in my opinion. Notice there isn't a password key, our database doesn't have a password yet.

[*] Real applications
We have already seen some theory, but let's see how would we do it in a real case:

PHP Code:

/* 1 - Connection handle. */
new MySQL:db_handle;
public OnGameModeInit() 
{ 
    /* 2 - log everything related to the MySQL plugin. */ 
    mysql_log(ALL); 
    /* 3 - connect to the database using the .ini file. */
    db_handle = mysql_connect_file("mysql.ini"); 
    /* 4 - check the connection handle. */ 
    if(mysql_errno(db_handle) != 0) 
    { 
        printf("** [MySQL] Couldn't connect to the database (%d).", mysql_errno(db_handle));
        SendRconCommand("exit");
    } 
    else 
    { 
        printf("** [MySQL] Connected to the database successfully (%d).", _:db_handle);
        /* Queries here. */
    } 
    return 1; 
}
public OnGameModeExit() 
{ 
    /* 5 - close the connection on exit. */ 
    if(db_handle) 
    { 
        mysql_close(db_handle); 
    } 
    return 1; 
} 


Let's analyze all previous steps:

1. We declare the db_handle variable in order to identify the connection for future use, notice we used the MySQL tag.
2. We use the mysql_log function passing the ALL log level in order to print everything related to the MySQL plugin inside the logs folder which is located inside the main directory of the SA-MP server. We should keep this enabled while development to track SQL errors easier.
3. mysql_connect and mysql_connect_file return the connection handle, that would be something like a "bridge" between the SA-MP server and the database. This way we know where to send our queries. Both functions return MYSQL_INVALID_HANDLE if they cannot connect.
4. Before executing SQL statements we should check the connection handle, sending queries nowhere wouldn't make sense, right? mysql_errno returns 0 if everything went alright, so if it returns a value different than 0 it means the connection wasn't successful, in this case we print a message with the error code and we close the server. When the connection is successful we print the connection handle ID and we might send queries just after.
5. When we close the server we should close the MySQL connection also, so we check for a valid connection handle and we execute mysql_close on it. Watch out, when we end the server process forcing it (clicking "x" on Windows) the OnGameModeExit function isn't called, the correct way would be typing the exit command on the console. Sometimes I wonder why some people add "in case of crash" code there, if the server crashes that callback isn't called! So don't bother on adding anything.

[*] The end
This is where the first tutorial finishes, on the next one we're going to see what kind of queries we can use and how to create a table correctly. You might follow the development of the MySQL gamemode I'm writing for these tutorials on GitHub, that way you could see and understand better what I do.

Thank you for reading my thread, if you want to support what I do you might buy me a coffe! Subscribe to my ******* channel if you wish to follow the development of my gamemode.

Please, if you find any error let me know so I can correct it.

Ci vediamo!

[Jaxium]

That's neat. Well written and well done.

[Eoussama]

I wish I found something like this on my debut, very good tutorial, looking forward to seeing more advanced ones.

[DonaldDuck]

Great tutorial.

[rfr]

thank you this was useful

[Stefhan]

Code:

gamemodes\.pwn(43) : error 017: undefined symbol "sql_handle"

Got this error so I added this, is that good?

Code:

new MySQL:sql_handle;