MySQL Tutorial

From SA-MP Wiki

Basic MySQL Tutorial

Plugin: This tutorial is based on G-sTyLeZzZ's MySQL plugin. Info & download: [1]

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: [2] 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 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:

#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

This makes it easy to connect to another host.

Connecting to the MySQL Server: The way I do it, is making separate 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.

forward ConnectMySQL();
public ConnectMySQL()
{
	mysql_connect(SQL_HOST, SQL_USER, SQL_DB, SQL_PASS);
 
        if(mysql_ping() == 1)
        {    
            mysql_debug(1);
	    printf("[MYSQL]: Connection to `%s` succesful!", SQL_DB);
	} 
	else
	{
	    printf("[MYSQL]: [ERROR]: Connection to `%s` failed!", SQL_DB);
	}
	return 1;
}

Note: if mysql_debug[3] 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 retrieve 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):

mysql_query("INSERT INTO `users` (`username`, `password`) VALUES ('%s', MD5('%s'))", PlayerName, password);

Note: MD5() will hash the users' password.

- If you want to login a player, you can check if his password is correct by string comparison. To retrieve the password from the database, you'll need to use a format like this:

mysql_query("SELECT `password` FROM `users` WHERE `username` = '%s'", PlayerName);

Note: After this query, you will have to store your result using mysql_store_result[4]. 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[5], where you split the string using a delimiter. The functions, available for use can be found here: http://forum.sa-mp.com/index.php?topic=79352.0. After processing the returned result by the plugin, you will have to liberate the result from the memory, using mysql_free_result[6]. 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:

mysql_query("UPDATE `users` SET `fieldname` = '%s' WHERE `username` = '%s'", string to update);

- If you want to get a total number of rows, from some table, for example we can lookup the total number of users with:

mysql_query("SELECT * FROM `users`");

Note: This will select everything from the users table. When this has been returned via mysql_store_result[7], you can call mysql_num_rows[8]. 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.

--Faraday 08:58, 30 March 2010 (UTC)