SA-MP Forums

Go Back   SA-MP Forums > SA-MP Scripting and Plugins > Scripting Help > Tutorials

Reply
 
Thread Tools Display Modes
Old 16/11/2017, 01:58 AM   #1
RIDE2DAY
Huge Clucker
 
RIDE2DAY's Avatar
 
Join Date: Jul 2015
Location: Modena, Italy
Posts: 211
Reputation: 381
Default How to connect to a MySQL database

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: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_optionsAUTO_RECONNECTtrue); 
        
    mysql_set_option(my_optionsMULTI_STATEMENTSfalse); 
        
    mysql_set_option(my_optionsPOOL_SIZE2); 
        
    mysql_set_option(my_optionsSERVER_PORT3306); 

        
    /* Connect to the database using the defined credentials and options. */
        
    mysql_connect(MYSQL_HOSTMYSQL_USERMYSQL_PASSMYSQL_DBmy_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!

Last edited by RIDE2DAY; 24/02/2019 at 12:53 PM.
RIDE2DAY is offline   Reply With Quote
Old 16/11/2017, 02:48 AM   #2
Jaxium
Little Clucker
 
Join Date: Nov 2017
Location: Egypt
Posts: 11
Reputation: 1
Default Re: How to connect to a MySQL database

That's neat. Well written and well done.
Jaxium is offline   Reply With Quote
Old 16/11/2017, 09:21 AM   #3
Eoussama
High-roller
 
Eoussama's Avatar
 
Join Date: Jul 2016
Location: Kingdom of Morocco // Tangier
Posts: 1,071
Reputation: 281
Default Re: How to connect to a MySQL database

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

|===[Web taxi]===|
List of my work
Github
Pastebin

|===[Interesting topics]===|
Semantic Versioning
Eoussama is offline   Reply With Quote
Old 20/12/2017, 07:17 AM   #4
DonaldDuck
Banned
 
Join Date: Jun 2017
Location: Phillipines
Posts: 258
Reputation: 91
Default Re: How to connect to a MySQL database

Great tutorial.
DonaldDuck is offline   Reply With Quote
Old 20/12/2017, 07:57 AM   #5
rfr
Banned
 
Join Date: Nov 2017
Posts: 229
Reputation: 43
Default Re: How to connect to a MySQL database

thank you this was useful
rfr is offline   Reply With Quote
Old 20/12/2018, 10:08 PM   #6
Stefhan
Big Clucker
 
Stefhan's Avatar
 
Join Date: Nov 2014
Posts: 139
Reputation: 0
Default Re: How to connect to a MySQL database

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;
Stefhan is offline   Reply With Quote
Reply

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
mysql can't connect database [S-G]Cypher Scripting Help 1 03/10/2015 02:34 PM
mysql can't connect database [S-G]Cypher Scripting Help 0 03/10/2015 02:34 PM
MySQL database can't connect. [rG]Cold Server Support 0 28/09/2015 11:36 AM
Can't connect to mysql database.. Ox1gEN Scripting Help 45 28/08/2014 04:41 AM


All times are GMT. The time now is 01:27 PM.


Powered by vBulletin® Version 3.8.6
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.