[Scenario]

Connecting to a MySQL server
by RealCop228

Please note that any links in the body of this tutorial have been colored black in order to keep with the color scheme.

Introduction

I am going to be writing a series of tutorials regarding integration of MySQL into your PAWN scripts. These tutorials will also be available for download (in PDF format) once my website is set up, respectively. Any further questions, comments, or concerns can be directed to my message box.

Materials

• BlueG's MySQL plugin
• a MySQL server to connect to
• experience in phpMyAdmin

Step One

You need to begin by downloading BlueG's MySQL plugin and placing the files in their respective places. Please note that if you will be running your server on Windows, you need plugin files ending in .dll. If you plan to use Linux for your server, you will need files ending in .so. If you plan to enhance your knowledge of MySQL beyond this series, I would recommend that you check out this website (click here).

Step Two

This step is optional, but recommended just for ease-of-use.

Go ahead and open up your current PAWN script (or begin a new one). You'll need to add the following lines towards the top of your script. I personally add them just before the main() function.

pawn Код:

#define SQL_HOST ""
#define SQL_USER ""
#define SQL_DATA ""
#define SQL_PASS ""

Those defines will be a handy place for you to change your MySQL server information at ease without needing to do so in the actual "mysql_connect()" function. If you are currently running a WAMP server, your default settings should be the following:

pawn Код:

#define SQL_HOST "localhost"
#define SQL_USER "root"
#define SQL_PASS ""

As this is an optional step, you don't need to use defines. However, if you choose not to use them you can input your MySQL server information into the mysql_connect() function. You'll learn more about this function in Step Three, Section A.

In Example:

pawn Код:

mysql_connect("host", "user", "database", "password");

Step Three

Section A:

You need to add the following line when your GM/FS initiates:

pawn Код:

mysql_connect(SQL_HOST, SQL_USER, SQL_DATA, SQL_PASS);

In Example:

pawn Код:

public OnFilterScriptInit()
{
    mysql_connect(SQL_HOST, SQL_USER, SQL_DATA, SQL_PASS);
    return 1;
}

public OnGameModeInit()
{
    mysql_connect(SQL_HOST, SQL_USER, SQL_DATA, SQL_PASS);
    return 1;
}

Section B:

This step is optional, but recommended because it will help you debug your system in the future.

Add the function mysql_debug() above the function you added in Step Three, Section A.

In Example:

pawn Код:

public OnFilterScriptInit()
{
    mysql_debug(1);
    mysql_connect(SQL_HOST, SQL_USER, SQL_DATA, SQL_PASS);
    return 1;
}

public OnGameModeInit()
{
    mysql_debug(1);
    mysql_connect(SQL_HOST, SQL_USER, SQL_DATA, SQL_PASS);
    return 1;
}

Notice the entire function:

pawn Код:

mysql_debug(1);

Specifically, the number one (1) in the function. Without that number, this function will most likely return an error when you compile. Adding number one signifies that you wish to enable MySQL debugging, adding a zero (0) would signify that you wish to disable MySQL debugging.

Section C:

This step, along with Step Three, Section A is mandatory. Without this, your SA-MP server will never officially disconnect from your MySQL server and this could eventually cause your MySQL server not to function properly.

Add the function "mysql_close()" when your GM/FS exits.

In Example:

pawn Код:

public OnFilterScriptExit()
{
    mysql_close();
    return 1;
}

public OnGameModeExit()
{
    mysql_close();
    return 1;
}

Once this function is called, it will close the connection between your SA-MP server and your MySQL server. This will prevent you from reaching your max amount of connections and eventually stopping your SA-MP server from connecting to your MySQL server. You don't want to call this function anywhere else besides when your GM/FS closes, otherwise your server will crash.

Step Four

You are set to establish a connection to your MySQL server, congratulations if you have exceeded this far into the tutorial. However, don't close out this page just yet because you still don't have a database to use. This is where having phpMyAdmin knowledge will come in handy.



Your interface may be a little different than in the picture above, however most phpMyAdmin interfaces are quite similar if not 90% the same. In the blank box, input a database name and hit the "Create" button. Your database is ready to go.

If you recall correctly, in step two we didn't input a value for the "SQL_DATA" define, we'll do that now. The name of your database goes between the apostrophes, just like the other defines.

In Example:

pawn Код:

#define SQL_DATA "database1"

Conclusion

Congratulations, you have just set up your script to communicate with a MySQL server. Please look for more tutorials in this series. I'll be posting about 2 tutorials a week so stay tuned. Remember that these tutorials will be downloadable in PDF format once my website is set up!

Our finished product:

pawn Код:

#define FILTERSCRIPT

#include <a_samp>

#define SQL_HOST "localhost"
#define SQL_USER "root"
#define SQL_DATA "database1"
#define SQL_PASS ""

#if defined FILTERSCRIPT

public OnFilterScriptInit()
{
    mysql_debug(1);
    mysql_connect(SQL_HOST, SQL_USER, SQL_DATA, SQL_PASS);
    return 1;
}

public OnFilterScriptExit()
{
    mysql_close();
    return 1;
}

#endif

public OnGameModeInit()
{
    mysql_debug(1);
    mysql_connect(SQL_HOST, SQL_USER, SQL_DATA, SQL_PASS);
    return 1;
}

public OnGameModeExit()
{
    mysql_close();
    return 1;
}

Resources

• You can find a complete list of BlueG's MySQL functions here.
• You can find great game/web hosting by visiting Volt Host.
• You can message me for any tutorial requests.

Credits/Special Thanks

• Nick "RealCop228" Piccoli - Creating this tutorial and having the idea of a whole series.
• BlueG for the MySQL plugin
• Calg00ne for assisting me a long time ago in MySQL
• playbox12 for pretty much teaching me a ton of MySQL-related material