21.02.2010, 10:28
Okay since I searched the forums alot and didn't find a simple MySQL tutorial. I decided to make my own. It's very simple, but has all the basic info you need, to start out. If there are any problems according Queries, Connections or phpmyadmin, you can ask for help here. I'll be glad to help out.
SA:MP Wiki Link
Plugin:
I'm using the MySQL plugin from G-sTyLeZzZ which can be found here.
Hosting Environment:
First thing to do is create a virtual server on your pc for local testing.
In this tutorial I will be using wampserver. You can download it here.
In wampserver, when Installed you can find "phpmyadmin". We will use that to control our MySQL database.
Controlling your Database:
After the installation we will have to create a database. Click the "Wampserver" icon in the system tray of your operation system, and open phpmyadmin. There you will see there are already a few databases installed. We will just create a new one. Fill in the database name and click "Create". (fig. 1)
fig. 1
Once your database has been created, we will need a table to store data in. I will use the user table example, since this is the most used for MySQL registration systems in servers.
So click on your database and fill in the fields to create a new table.
We will make a 'users' table with 3 fields. Adding fields beyond this point is very easy, so we will just start out with 3. (fig. 2)
fig. 2
Click "Go" and there will be some empty spaces to fill in your data. (fig. 3)
fig. 3
So fill in the data like in the picture. (fig. 3)
In the userid column I checked the AUTO_INCREMENT box. This means that MySQL will number the userid into ascending order.
Once this has been setup we can go to the pawn script.
Pawno Script:
First of all we have to define the connection parameters.
Standard, when you're localtesting, there's no password. So we can define it like this:
This makes it easy to connect to another host.
Connecting to the MySQL Server:
The way I do it, is making seperate functions. This makes it easier to use, and it makes everything more clear to oversee.
The first function is to connect MySQL and is a function we will put in OnGameModeInit.
Note: if mysql_debug is turned on (value: 1), it means that the plugin will create a file for debugging purposes. The file will be mysql_log.txt, in your server directory.
By now you should be able to connect to the MySQL database.
The part that comes next, requires some basic Pawno knowledge, since I won't be posting code snippets for commands or functions, if you want those things, the best thing to do is download some gamemodes or filterscripts with MySQL systems.
Common Queries:
Now I will show some basic Queries to send, to retreive info from the database, write to the database, etc..
* Example:
- When you want to register a new user, you will have to insert a new row into the table.
Your Query will have to look like this (this is standard MySQL language):
Note: MD5() will hash the users' password.
Note: Don't forget to format a string and send that string to the MySQL database, or you will get the "Argument Type Mismatch" Pawno error.
- If you want to login a player, you can check if his password is correct by string comparison. To retreive the password from the database, you'll need to use a format like this:
Note: After this Query, you will have to store your result using mysql_store_result(). This will have the plugin remember the last returned result. When this function has been called, you can use functions like, mysql_fetch_row_format, where you split the string using a delimiter. The functions, available for use can be found here.
After processing the returned result by the plugin, you will have to liberate the result from the memory, using mysql_free_result().
Note: You will also have to return the hash on the password that has been returned from the Query. You can use MD5 for that.
- If you want to update some parameters, maybe save a user in OnPlayerDisconnect, you'll have to update the database like this:
- If you want to get a total number of rows, from some table, for example we can lookup the total number of users with:
Note: This will select everything from the users table. When this has been returned via mysql_store_result(), you can call mysql_num_rows(). This will give you the total amount of rows (a.k.a total amount of users), which you can store in a variable.
General Note: Using backticks, like I did in all of my examples, is not necessary for MySQL. I just use it due to habit.
SA:MP Wiki Link
Plugin:
I'm using the MySQL plugin from G-sTyLeZzZ which can be found here.
Hosting Environment:
First thing to do is create a virtual server on your pc for local testing.
In this tutorial I will be using wampserver. You can download it here.
In wampserver, when Installed you can find "phpmyadmin". We will use that to control our MySQL database.
Controlling your Database:
After the installation we will have to create a database. Click the "Wampserver" icon in the system tray of your operation system, and open phpmyadmin. There you will see there are already a few databases installed. We will just create a new one. Fill in the database name and click "Create". (fig. 1)
fig. 1
Once your database has been created, we will need a table to store data in. I will use the user table example, since this is the most used for MySQL registration systems in servers.
So click on your database and fill in the fields to create a new table.
We will make a 'users' table with 3 fields. Adding fields beyond this point is very easy, so we will just start out with 3. (fig. 2)
fig. 2
Click "Go" and there will be some empty spaces to fill in your data. (fig. 3)
fig. 3
So fill in the data like in the picture. (fig. 3)
In the userid column I checked the AUTO_INCREMENT box. This means that MySQL will number the userid into ascending order.
Once this has been setup we can go to the pawn script.
Pawno Script:
First of all we have to define the connection parameters.
Standard, when you're localtesting, there's no password. So we can define it like this:
Code:
#define SQL_HOST "localhost" #define SQL_USER "root" #define SQL_PASS "" #define SQL_DB "DB_NAME"// You'll have to change this to the name of the database created in phpmyadmin
Connecting to the MySQL Server:
The way I do it, is making seperate functions. This makes it easier to use, and it makes everything more clear to oversee.
The first function is to connect MySQL and is a function we will put in OnGameModeInit.
Code:
forward ConnectMySQL();
public ConnectMySQL()
{
if(mysql_connect(SQL_HOST,SQL_USER,SQL_DB,SQL_PASS))
{
mysql_debug(1);
printf("[MYSQL]: Connection to `%s` succesful!",SQL_DB);
}
else
{
printf("[MYSQL]: [ERROR]: Connection to `%s` failed!",SQL_DB);
}
return 1;
}
By now you should be able to connect to the MySQL database.
The part that comes next, requires some basic Pawno knowledge, since I won't be posting code snippets for commands or functions, if you want those things, the best thing to do is download some gamemodes or filterscripts with MySQL systems.
Common Queries:
Now I will show some basic Queries to send, to retreive info from the database, write to the database, etc..
* Example:
- When you want to register a new user, you will have to insert a new row into the table.
Your Query will have to look like this (this is standard MySQL language):
Code:
new string[256];
format(string,sizeof(string),"INSERT INTO `users` (`username`, `password`) VALUES ('%s', MD5('%s'))",PlayerName, password);
mysql_query(string);
Note: Don't forget to format a string and send that string to the MySQL database, or you will get the "Argument Type Mismatch" Pawno error.
- If you want to login a player, you can check if his password is correct by string comparison. To retreive the password from the database, you'll need to use a format like this:
Code:
new string[256]; format(string,sizeof(string),"SELECT `password` FROM `users` WHERE `username` = '%s'",PlayerName); mysql_query(string);
After processing the returned result by the plugin, you will have to liberate the result from the memory, using mysql_free_result().
Note: You will also have to return the hash on the password that has been returned from the Query. You can use MD5 for that.
- If you want to update some parameters, maybe save a user in OnPlayerDisconnect, you'll have to update the database like this:
Code:
new string[256]; format(string,sizeof(string),"UPDATE `users` SET `fieldname` = '%s' WHERE `username` = '%s'",string to update); mysql_query(string);
Code:
mysql_query("SELECT * FROM `users`");
General Note: Using backticks, like I did in all of my examples, is not necessary for MySQL. I just use it due to habit.
Really nice tutorial now i can finally use mySQL
