SA-MP Forums

Go Back   SA-MP Forums > SA-MP Scripting and Plugins > Scripting Help > Help Archive

 
 
Thread Tools Display Modes
Old 21/02/2010, 11:28 AM   #1
Faraday
Big Clucker
 
Join Date: Jan 2007
Location: Belgium
Posts: 117
Reputation: 4
Default [Tutorial] Basic MySQL Tutorial

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.
__________________
The Official littlewhitey's SA:MP Server: 94.23.120.101:7778
Faraday is offline  
Old 21/02/2010, 11:36 AM   #2
adsy
Huge Clucker
 
Join Date: Aug 2008
Posts: 410
Reputation: 3
Default Re: [Tutorial] Basic MySQL Tutorial

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.)
adsy is offline  
Old 21/02/2010, 01:18 PM   #3
Sergei
High-roller
 
Sergei's Avatar
 
Join Date: Mar 2008
Location: Slovenia
Posts: 2,870
Reputation: 230
Default Re: [Tutorial] Basic MySQL Tutorial

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;
}
__________________
Watch out, scouts everywhere!
Sergei is offline  
Old 21/02/2010, 01:25 PM   #4
pyrodave
Big Clucker
 
Join Date: Jul 2008
Posts: 117
Reputation: 14
Default Re: [Tutorial] Basic MySQL Tutorial

Great tutorial, although I think it might be worth mentioning mysql_real_escape_string for things like passwords and other user inputted strings.
pyrodave is offline  
Old 22/02/2010, 09:58 AM   #5
Faraday
Big Clucker
 
Join Date: Jan 2007
Location: Belgium
Posts: 117
Reputation: 4
Default Re: [Tutorial] Basic MySQL Tutorial

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.
__________________
The Official littlewhitey's SA:MP Server: 94.23.120.101:7778
Faraday is offline  
Old 22/02/2010, 10:54 AM   #6
[03]Garsino
Guest
 
Posts: n/a
Default Re: [Tutorial] Basic MySQL Tutorial

Thanks for sharing this tutorial. I've thought for a long time to start with mysql, never figured out how tho
 
Old 12/03/2010, 07:07 AM   #7
Naruto4
Huge Clucker
 
Join Date: May 2009
Posts: 234
Reputation: 0
Default Re: [Tutorial] Basic MySQL Tutorial

Nice one
Naruto4 is offline  
Old 12/03/2010, 08:12 AM   #8
Deat_Itself
Gangsta
 
Join Date: May 2009
Location: UAE (Abudhabi,Shahama)
Posts: 595
Reputation: 4
Default Re: [Tutorial] Basic MySQL Tutorial

nice Tutorial.
Deat_Itself is offline  
Old 14/03/2010, 05:44 PM   #9
andrewp
Big Clucker
 
Join Date: Mar 2010
Posts: 51
Reputation: 0
Default Re: [Tutorial] Basic MySQL Tutorial

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
andrewp is offline  
Old 14/03/2010, 06:44 PM   #10
deather
Huge Clucker
 
deather's Avatar
 
Join Date: Dec 2009
Location: India
Posts: 360
Reputation: 2
Default Re: [Tutorial] Basic MySQL Tutorial

Great tutorial.
It would be helpful if you could explain more functions.
deather is offline  
 

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
MYSQL tutorial? acade Help Archive 6 24/02/2010 03:33 PM
[TUTORIAL]PHP With MySQL coole210 Filterscripts 9 23/02/2010 03:19 AM
Is there a tutorial for the MYSQL plugin? dirkblok Help Archive 12 23/01/2010 11:17 PM


All times are GMT. The time now is 07:28 AM.


Powered by vBulletin® Version 3.8.6
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.