[Tutorial] Connecting to a MySQL server

[Tutorial] Connecting to a MySQL server - Printable Version

+- SA-MP Forums Archive (https://sampforum.blast.hk)
+-- Forum: SA-MP Scripting and Plugins (https://sampforum.blast.hk/forumdisplay.php?fid=8)
+--- Forum: Scripting Help (https://sampforum.blast.hk/forumdisplay.php?fid=12)
+---- Forum: Tutorials (https://sampforum.blast.hk/forumdisplay.php?fid=70)
+---- Thread: [Tutorial] Connecting to a MySQL server (/showthread.php?tid=290257)

Pages: 1 2

Connecting to a MySQL server - Scenario - 15.10.2011

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

Re: Connecting to a MySQL server - Scenario - 15.10.2011

Reserved

Re: Connecting to a MySQL server - Ricop522 - 15.10.2011

Thanks, awesome tutorial.
Do you know how to create a top5 with mysql ?
sorry for this question..

Re: Connecting to a MySQL server - Scenario - 15.10.2011

Quote:

Originally Posted by Ricop522

Thanks, awesome tutorial.
Do you know how to create a top5 with mysql ?
sorry for this question..

Do you mean something like a list of the players with the top 5 amounts of cash?

Re: Connecting to a MySQL server - Venice - 15.10.2011

Quote:

Originally Posted by Ricop522

Thanks, awesome tutorial.
Do you know how to create a top5 with mysql ?
sorry for this question..

that is easy you need to do in php
and
Nice tutorial

Re: Connecting to a MySQL server - [HiC]TheKiller - 15.10.2011

Quote:

Originally Posted by Venice

that is easy you need to do in php
and
Nice tutorial

You don't need to do it in PHP, you can easily do this in PAWN too. Read this post.

A few suggestions for this tutorial would be
1. Tell them where they can find the MySQL log.
2. You should tell them what issues can arise E.g. not being able to connect to the database.

Re: Connecting to a MySQL server - Venice - 15.10.2011

Quote:

Originally Posted by [HiC]TheKiller

I mean in website

Re: Connecting to a MySQL server - Kar - 15.10.2011

Quote:

Originally Posted by Venice

that is easy you need to do in php
and
Nice tutorial

..

By Calg00ne

pawn Код:

mysql_query("SELECT `Name`, `Score` FROM `players` ORDER BY `Score` DESC LIMIT 10");
mysql_store_result();
new szPrepTD[460], iIterator = 1, iScore, szName[MAX_PLAYER_NAME], szUnload[64];
while(mysql_fetch_row_format(szUnload)) {
// This code will repeat ten times.
sscanf(szUnload, "p<|>s[24]d", szName, iScore);
if(strlen(szPrepTD) == 0) format(szPrepTD, sizeof(szPrepTD), "#%d - %s (%d score)\n", iIterator, szName, iScore);
format(szPrepTD, sizeof(szPrepTD), "%s#%d - %s (%d score)\n", szPrepTD, iIterator, szName, iScore);
iIterator++;
}
mysql_free_result();*/

Re: Connecting to a MySQL server - Hiddos - 15.10.2011

Good job, about time somebody made a high-quality tutorial

Re: Connecting to a MySQL server - Scenario - 15.10.2011

Quote:

Originally Posted by Kar

..

By Calg00ne

pawn Код:

mysql_query("SELECT `Name`, `Score` FROM `players` ORDER BY `Score` DESC LIMIT 10");
mysql_store_result();
new szPrepTD[460], iIterator = 1, iScore, szName[MAX_PLAYER_NAME], szUnload[64];
while(mysql_fetch_row_format(szUnload)) {
// This code will repeat ten times.
sscanf(szUnload, "p<|>s[24]d", szName, iScore);
if(strlen(szPrepTD) == 0) format(szPrepTD, sizeof(szPrepTD), "#%d - %s (%d score)\n", iIterator, szName, iScore);
format(szPrepTD, sizeof(szPrepTD), "%s#%d - %s (%d score)\n", szPrepTD, iIterator, szName, iScore);
iIterator++;
}
mysql_free_result();*/

I don't really understand your point?

Quote:

Originally Posted by Hiddos

Good job, about time somebody made a high-quality tutorial

Thanks!

Re: Connecting to a MySQL server - playbox12 - 15.10.2011

Good job, hope there will be less questions on how to get started now.

(and thanks for including me in the credits).

Re: Connecting to a MySQL server - juraska - 15.10.2011

Good tutorial RealCop228, nicely explained

Re: Connecting to a MySQL server - Scenario - 15.10.2011

Coming up next will be a tutorial on setting up tables in phpMyAdmin for use with your SA-MP server.

Re: Connecting to a MySQL server - [NoV]LaZ - 15.10.2011

Quote:

Originally Posted by RealCop228

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.

Not necessarily. When the plugin's deconstructor is called, it will automatically disconnect from the MySQL database without having mysql_close.

Re: Connecting to a MySQL server - Scenario - 15.10.2011

Quote:

Originally Posted by [NoV]LaZ

Not necessarily. When the plugin's deconstructor is called, it will automatically disconnect from the MySQL database without having mysql_close.

I don't believe that's true. I was always told that the function mysql_close() was mandatory to include.

Respuesta: Connecting to a MySQL server - Zafiro - 15.10.2011

Very nice RealCop228.

Re: Connecting to a MySQL server - jot16 - 15.10.2011

nice tutorial.

Re : Connecting to a MySQL server - Naruto_Emilio - 15.10.2011

Thanks you for this Tutorial

useful

Re: Connecting to a MySQL server - Scenario - 24.12.2011

I'm working on the next phase of this tutorial, where you can setup tables for use with your SA-MP server.

Re: Connecting to a MySQL server - Elegy - 02.01.2016

not work for me -_-

PHP Code:



[10:16:37] [debug] Run time error 19: "File or function is not found"

[10:16:37] [debug] mysql_query

[10:16:37] [debug] mysql_store_result

[10:16:37] [debug] mysql_free_result

[10:16:37] [debug] mysql_close

[10:16:37] [debug] mysql_real_escape_string

[10:16:37] [debug] mysql_insert_id

[10:16:37] [debug] mysql_retrieve_row

[10:16:37] [debug] mysql_fetch_field_row

[10:16:37] [debug] mysql_num_rows

[10:16:37] [debug] mysql_connect

[10:16:37] [debug] mysql_ping

[10:16:37] [debug] mysql_debug

[10:16:37] [debug] Run time error 19: "File or function is not found"

[10:16:37] [debug] mysql_query

[10:16:37] [debug] mysql_store_result

[10:16:37] [debug] mysql_free_result

[10:16:37] [debug] mysql_close

[10:16:37] [debug] mysql_real_escape_string

[10:16:37] [debug] mysql_insert_id

[10:16:37] [debug] mysql_retrieve_row

[10:16:37] [debug] mysql_fetch_field_row

[10:16:37] [debug] mysql_num_rows

[10:16:37] [debug] mysql_connect

[10:16:37] [debug] mysql_ping

[10:16:37] [debug] mysql_debug

[10:16:37] Script[gamemodes/xSF.amx]: Run time error 19: "File or function is not found"

[10:16:37] Number of vehicle models: 0