[Tutorial] Basic MySQL Tutorial

[Tutorial] Basic MySQL Tutorial - 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: Help Archive (https://sampforum.blast.hk/forumdisplay.php?fid=89)
+---- Thread: [Tutorial] Basic MySQL Tutorial (/showthread.php?tid=129183)

Pages: 1 2 3

[Tutorial] Basic MySQL Tutorial - Faraday - 21.02.2010

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:

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

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.

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;
}

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):

Code:

new string[256];
format(string,sizeof(string),"INSERT INTO `users` (`username`, `password`) VALUES ('%s', MD5('%s'))",PlayerName, password);
mysql_query(string);

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:

Code:

new string[256];
format(string,sizeof(string),"SELECT `password` FROM `users` WHERE `username` = '%s'",PlayerName);
mysql_query(string);

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:

Code:

new string[256];
format(string,sizeof(string),"UPDATE `users` SET `fieldname` = '%s' WHERE `username` = '%s'",string to update);
mysql_query(string);

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

Code:

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

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.

Re: [Tutorial] Basic MySQL Tutorial - adsy - 21.02.2010

THANKYOU!!! finally some real explanation on how it works!

Quote:

#define SQL_DB "DB_NAME"// You'll have to change this to your database created in phpmyadmin

(fig 1.)

Re: [Tutorial] Basic MySQL Tutorial - Sergei - 21.02.2010

mysql_connect will return connection handle in any case (min 1), so that check is useless.

pawn Code:

stock 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;
}

Re: [Tutorial] Basic MySQL Tutorial - pyrodave - 21.02.2010

Great tutorial, although I think it might be worth mentioning mysql_real_escape_string for things like passwords and other user inputted strings.

Re: [Tutorial] Basic MySQL Tutorial - Faraday - 22.02.2010

Quote:

Originally Posted by DavidC

Great tutorial, although I think it might be worth mentioning mysql_real_escape_string for things like passwords and other user inputted strings.

Since I haven't used mysql_real_escape_string, can you explain it to me? If it's really important to know, I can add it to the tutorial.

Re: [Tutorial] Basic MySQL Tutorial - [03]Garsino - 22.02.2010

Thanks for sharing this tutorial. I've thought for a long time to start with mysql, never figured out how tho

Re: [Tutorial] Basic MySQL Tutorial - Naruto4 - 12.03.2010

Nice one

Re: [Tutorial] Basic MySQL Tutorial - Deat_Itself - 12.03.2010

nice Tutorial.

Re: [Tutorial] Basic MySQL Tutorial - andrewp - 14.03.2010

Quote:

Originally Posted by Faraday

Quote:

Originally Posted by DavidC

Great tutorial, although I think it might be worth mentioning mysql_real_escape_string for things like passwords and other user inputted strings.

Since I haven't used mysql_real_escape_string, can you explain it to me? If it's really important to know, I can add it to the tutorial.

Not really important to know, since I don't think it's possible to SQL inject through SA-MP. But it's a function in PHP that allows a person to protect a form from SQL injection (a form of hacking where a hacker can use a SQL query to withdraw data from the database). And I have a suggestion:

Code:

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

They'll have to MD5 hash the string that they are comparing it to so it compares correctly. I think you should mention that.

Thanks

Re: [Tutorial] Basic MySQL Tutorial - deather - 14.03.2010

Great tutorial.

It would be helpful if you could explain more functions.

Re: [Tutorial] Basic MySQL Tutorial - Faraday - 14.03.2010

Quote:

Originally Posted by AndrewP

Code:

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

They'll have to MD5 hash the string that they are comparing it to so it compares correctly. I think you should mention that.

Added as a note in the Original Post.

Quote:

Originally Posted by |)Ω{−}R Dare To Die

Great tutorial.

It would be helpful if you could explain more functions.

What functions do you want explained more. As it says in the OP this is a basic MySQL tutorial to get you going, and do basic stuff. Once you get comfortable with using MySQL in combination with Pawno, it'll work itself out.. But if you have specific questions I'm willing to help out.

Re: [Tutorial] Basic MySQL Tutorial - adsy - 17.03.2010

databases are quite easy if you create everything you need tableswise in phpmyadmin first

everything after you connect is a query

query update will change a record
query insert into will add a new record
query delete from will remove a record
query select will get the information stored for a record

mysql isnt all that complicated. once you learn one database youll start to understand the other sorts that are out there.

Re: [Tutorial] Basic MySQL Tutorial - Jay_ - 18.03.2010

Nice tutorial. Post this in the wiki.

Re: [Tutorial] Basic MySQL Tutorial - Brian_Furios - 19.03.2010

very nice tutorial on MySql , cool 10/10 on this

Re: [Tutorial] Basic MySQL Tutorial - MaykoX - 19.03.2010

Quote:

Originally Posted by [03

Garsino ]
Thanks for sharing this tutorial. I've thought for a long time to start with mysql, never figured out how tho

Same here

Really nice tutorial now i can finally use mySQL

Re: [Tutorial] Basic MySQL Tutorial - Faraday - 19.03.2010

Whenever I have some spare time I'll add it to the wiki.

Re: [Tutorial] Basic MySQL Tutorial - deather - 21.03.2010

Mna, mysql_query shows aregument mismatch -

Code:

mysql_query("INSERT INTO 'users' ('name', 'pass') VALUES ('%s', MD5('%s'))",PName, inputtext);

Re: [Tutorial] Basic MySQL Tutorial - Kastranova - 21.03.2010

keep getiing this error

Quote:

C:\DOCUME~1\JUSTIN~1\BUREAU~1\SG\gtarp.pwn(11340) : error 029: invalid expression, assumed zero
C:\DOCUME~1\JUSTIN~1\BUREAU~1\SG\gtarp.pwn(11340) : error 017: undefined symbol "ConnectMySQL"
C:\DOCUME~1\JUSTIN~1\BUREAU~1\SG\gtarp.pwn(11341) : error 029: invalid expression, assumed zero
C:\DOCUME~1\JUSTIN~1\BUREAU~1\SG\gtarp.pwn(11341) : error 017: undefined symbol "ConnectMySQL"

This is the script

Quote:

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;
}
}

please help

Re: [Tutorial] Basic MySQL Tutorial - adsy - 21.03.2010

have you included at the top

#include <a_mysql>

Re: [Tutorial] Basic MySQL Tutorial - Kastranova - 21.03.2010

Quote:

Originally Posted by adsy

have you included at the top

#include <a_mysql>

Yup but i already got it but thanks anyway