SA-MP Forums Archive
[Tutorial] Making a MySQL system - 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] Making a MySQL system (/showthread.php?tid=159785)

Pages: 1 2 3 4

Making a MySQL system - [HiC]TheKiller - 14.07.2010

Making a MySQL system!
Introduction
I decided to make this because MySQL owns! It's totally remade from the last code in this thread. from Pvars to variables because Pvars are less efficient. I've still included the auto login system. The database will be the exact same name but I'll make the table name different. If you are having issues with your script download the full server + the filterscript http://www.filejungle.com/f/xk5X8T/mysql.rar

What do I need?
You will need the following things to be able to do this tutorial.
Now that wamp and the MySQL plugin has been set up in the right directories we can start the script. For any script that you are using mysql, you must have the includes

pawn Code:
#include <a_samp>
#include <a_mysql>
Now, before we start the actual code, we are going to make defines with out SQL information so it's easier for connecting / editing later on. This information should be provided by your host or it will be the following if you are using wamp

pawn Code:
#define mysql_host "127.0.0.1" //Has to be a string
#define mysql_user "root" //Has to be a string
#define mysql_password "" //There is none for wamp unless you set one.
#define mysql_database "sa-mp" //Has to be a string
Now, the first thing that we must do to be able to use MySQL is connect to the MySQL database. We will be using our defined information above. We will also going to be creating the table if it doesn't exist here rather then manually doing it in PhpMyAdmin.

We will be following this format
Code:
CREATE TABLE IF NOT EXISTS TABLENAME(STRUCTURE)
If you want to add further fields to the table later on, create a script and use mysql query like we will be doing for creating the table.
Code:
ALTER TABLE table_name
ADD column_name datatype
For more information on datatypes visit this link.

The mysql functions used in this snippet is mysql_connect and mysql_query.


pawn Code:
public OnGameModeInit()
{
    mysql_connect(mysql_host, mysql_user, mysql_database, mysql_password);
    mysql_query("CREATE TABLE IF NOT EXISTS playerdata(user VARCHAR(24), password VARCHAR(41), score INT(20), money INT(20), IP VARCHAR(16) )");
    //Fields:
    //Field Name - Use - Type
    //user- Player Name - String
    //password- Players password - String
    //score - Players score - int
    //money - Players Cash - int
    //IP - Players IP - int
    return 1;
}
Now that we have connected to the database, we are going to check on OnPlayerConnect what dialog we are going to show for the player or if the player should be auto-logged in. We are going to use a global variable called Money given as we cannot give the player money on OnPlayerConnect / OnPlayerRequestClass The functions that are being used in this part of the tutorial are mysql_query, mysql_fetch_field_row, mysql_num_rows, mysql_store_result and mysql_free_result. We will be using our first Query and there are multiple different statements for queries. We are starting by using the SELECT statement which can be modelled in this format:



pawn Code:
new IsRegistered[MAX_PLAYERS]; 
//We are using this variable so we don't have to query later to 
//check if the player is registered in the database.

new MoneyGiven[MAX_PLAYERS]; //Explained in the paragraph above.

public OnPlayerConnect(playerid)
{
    MoneyGiven[playerid] = -1; //Resets the variable that you will discover later in the tutorial.
    new query[200], pname[24]; //Creates our variables.
    GetPlayerName(playerid, pname, 24); //Gets the players name
    format(query, sizeof(query), "SELECT IP FROM `playerdata` WHERE user = '%s' LIMIT 1", pname); //Formats the query, view above the code for a explanation
    mysql_query(query); //This is our query function to query the string
    mysql_store_result(); //We store the result.
    new rows = mysql_num_rows(); //We get how many rows the query returned.
    if(!rows) 
    {
        //If the rows are equal to 0. This means that the query did not find 
        //anyone under the name we connected under in the database.
        //So here we send the player the register dialog.
        ShowPlayerDialog(playerid, 15000, DIALOG_STYLE_INPUT, "Register","Your user is {FF0000}not{FFFFFF} registered! Please {0000FF}register{FFFFFF} with a password below!","Register","Cancel"); //Shows our register dialog :).
    }
    if(rows == 1) 
    {
        //If the rows are equal to 1, this means there is a player already registered 
        //so we can initiate the login dialog to the player or check if the players
        //current IP is the same one as in the database.
        new IP[2][16]; //We create a variable with two IP strings, one for retrieving the mysql field and one for GetPlayerIP.
        mysql_fetch_field_row(IP[0],"IP");
        GetPlayerIp(playerid, IP[1], 16);
        if(strlen(IP[0]) != 0 && !strcmp(IP[0], IP[1], true)) //Checks that the MySQL IP has a value and that they are the same.
        {
            MySQL_Login(playerid);
        }
        else if(!strlen(IP[0]) || strcmp(IP[0], IP[1], true))
        {
            ShowPlayerDialog(playerid, 15500, DIALOG_STYLE_INPUT, "Login","Your user is {FF0000}registered{FFFFFF}! Please {0000FF}login{FFFFFF} with your password below!","Login","Cancel"); //Shows our login dialog :).
            IsRegistered[playerid] = 1; //Sets the registered variable to 1 (Shows that the player is registered).
        }
    }
    mysql_free_result(); 
    //You must always free the mysql result to avoid
    //there being massive memory usage.
    return 1;
}
Now that we have finished the OnPlayerConnect stuff, we are going to make our dialog responses. These are basic and require no MySQL for the register part and a single query to check if the password is right on our login dialog. We will be using the functions mysql_query, mysql_real_escape_string and mysql_num_rows.

pawn Code:
public OnDialogResponse(playerid, dialogid, response, listitem, inputtext[])
{
    if(dialogid == 15000) //If Dialog is our register dialog
    {
        if(response) //If they click the button register
        {
            if(!strlen(inputtext) || strlen(inputtext) > 100)  //Password is not 1 to 100 characters
            {
                SendClientMessage(playerid, 0xFF0000, "You must insert a password between 1-100 characters!"); //Sends the client a error message
                ShowPlayerDialog(playerid, 15000, DIALOG_STYLE_INPUT, "Register","Your user is {FF0000}not{FFFFFF} registered! Please {0000FF}register{FFFFFF} with a password below!\n {FF0000}ERROR:Please enter a password between 1-100 characters!","Register","Cancel"); //Shows our register dialog :).
            }
            else if(strlen(inputtext) > 0 && strlen(inputtext) < 100) 
            {
                new escpass[100];
                mysql_real_escape_string(inputtext, escpass);
                MySQL_Register(playerid, escpass); 
            }
            //If the password is between 1 and 100 characters then we will
            //call our MySQL_register function which will register the player.
        }
        if(!response)
        {
                SendClientMessage(playerid, 0xFF0000, "You must register before logging in!"); //Sends the client a error message
                ShowPlayerDialog(playerid, 15000, DIALOG_STYLE_INPUT, "Register","Your user is {FF0000}not{FFFFFF} registered! Please {0000FF}register{FFFFFF} with a password below!\n {FF0000}ERROR:Please enter a password !","Register","Cancel"); //Shows our register dialog :).
        }
    }
    if(dialogid == 15500) //Dialog login
    {
        if(!response) //If they click the cancel button
        {
                SendClientMessage(playerid, 0xFF0000, "You must login before you spawn!"); //Sends the client a error message
                ShowPlayerDialog(playerid, 15500, DIALOG_STYLE_INPUT, "Login","Your user is {FF0000}registered{FFFFFF}! Please {0000FF}login{FFFFFF} with your password below!\n{FF0000} You must login before you spawn!","Login","Cancel"); //Shows our login dialog :).
        }
        if(response) //If the player clicked login
        {
            new query[200], pname[24], escapepass[100]; //
            GetPlayerName(playerid, pname, 24); //Gets the players name
            mysql_real_escape_string(inputtext, escapepass); //We escape the inputtext to avoid SQL injections.
            format(query, sizeof(query), "SELECT `user` FROM playerdata WHERE user = '%s' AND password = SHA1('%s')", pname, escapepass);
            mysql_query(query);
            mysql_store_result();
            new numrows = mysql_num_rows();
            if(numrows == 1) MySQL_Login(playerid);
            //This means that there is a user in the database with the same
            //password that we typed, we now proceed by using the login function.
            if(!numrows) 
            {
                //This means that the password that the player
                //typed was incorrect and we will resend the dialog.
                ShowPlayerDialog(playerid, 15500, DIALOG_STYLE_INPUT, "Login","Your user is {FF0000}registered{FFFFFF}! Please {0000FF}login{FFFFFF} with your password below!\n{FF0000} The password you typed was incorrect!","Login","Cancel"); //Shows our login dialog :).
                SendClientMessage(playerid, 0xFF0000, "Incorrect password!"); //Sends the client a error message
            }
            mysql_free_result(); //Remember to always free a result if you stored one!
        }
    }
    return 1;
}
Now that we have done our register and login dialogs, we are now moving onto our MySQL functions. These functions are MySQL_register and MySQL_login. We are going to use one variable called Logged to show that the player is logged in. We already have checked if the player is ready to register or login so, all we need to do is retrieve and set values in these functions. We are going to use the INSERT INTO statement in the register function, the structure of this is:
Code:
INSERT INTO table_name (column1, column2, column3,...) VALUES (value1, value2, value3,...)
In the following code, we will be using mysql_fetch_field_row and mysql_fetch_row_format that we haven't used before.

pawn Code:
new Logged[MAX_PLAYERS]; //The variable to check if the player is logged.

//NOTE:Passwordstring has already been escaped. If you want to use
//this in another script, make sure that you escape the passwordstring
//before you 
stock MySQL_Register(playerid, passwordstring[])
{
    new query[200], pname[24], IP[16];
    GetPlayerName(playerid, pname, 24);
    GetPlayerIp(playerid, IP, 16);
    format(query, sizeof(query), "INSERT INTO playerdata (user, password, score, money, IP) VALUES('%s', SHA1('%s'), 0, 0, '%s')", pname, passwordstring, IP);
    mysql_query(query);
    //We do not need to store or free a result as it
    //is not a select statement. We can now send the
    //client a registration success message and set the
    //Login variable to 1.
    SendClientMessage(playerid, -1, "You have been registered on this server!");
    Logged[playerid] = 1; //Sets the login variable
    return 1;
}

stock MySQL_Login(playerid)
{
    new query[300], pname[24], savingstring[20];
    GetPlayerName(playerid, pname, 24);
    format(query, sizeof(query), "SELECT * FROM playerdata WHERE user = '%s'", pname);
    //We only select the variables that we want to use.
    //We don't need things like the password string or the user string.
    mysql_query(query); //Queries the result
    mysql_store_result(); //Store a result because it's a SELECT statement.
    while(mysql_fetch_row_format(query,"|"))
    {
        //We use while so that it does a single query, not multiple
        //Especially when we have more variables. If there is more 
        //Variables, you should just split the line with sscanf. To 
        //Make it easier.
        mysql_fetch_field_row(savingstring, "score"); SetPlayerScore(playerid, strval(savingstring));
        mysql_fetch_field_row(savingstring, "money"); MoneyGiven[playerid] = strval(savingstring);
        //If you are wondering why I'm using savingstring instead
        //Of a variable like using MoneyGiven right away, it's because
        //mysql_fetch_field_row requires a string.
    }
    mysql_free_result(); //We must always free a stored result
    SendClientMessage(playerid, -1, "You have been logged in!"); //Sends the client a message.
    Logged[playerid] = 1; //Sets our logged in variable to one
    return 1;
}
Now that we have done the register and loading part of it, we have to now get onto saving the data on OnPlayerDisconnect. This is a totally different statement to the previous ones too. We are going to use the UPDATE statement. The update statement format is
Code:
UPDATE table_name SET column1=value, column2=value2,... WHERE some_column=some_value
This is just a simple get variables + put them into the query.
pawn Code:
public OnPlayerDisconnect(playerid, reason)
{
    if(Logged[playerid] == 1)
    {
        //If the player disconnects before registering,
        //we want to make sure it doesn't try update
        //so we check if the player is logged in.
        new score = GetPlayerScore(playerid); //Gets players score
        new money = GetPlayerMoney(playerid); //Gets players money
        new query[200], pname[24]; //Creates the variables 
        GetPlayerName(playerid, pname, 24); //Gets the players name.
        format(query, sizeof(query), "UPDATE playerdata SET score=%d, money=%d WHERE user='%s'", score, money, pname);
        mysql_query(query);
        //No need to store a result for a update string
    }
    return 1;
}
We have almost finished! All we have is to edit two small things in two callbacks. There isn't much explaining to do for the following code. OnPlayerSpawn gives the player their cash and OnPlayerRequestClass prevents the player from spawning if not logged in.

pawn Code:
public OnPlayerSpawn(playerid)
{
    if(MoneyGiven[playerid] != -1) 
    {
        GivePlayerMoney(playerid, MoneyGiven[playerid]);
        MoneyGiven[playerid] = -1;
    }
    //Gives the player money if they haven't received it yet
    return 1;
}
pawn Code:
public OnPlayerRequestSpawn(playerid)
{
    if(!Logged[playerid]) //If the player isn't logged in and (s)he tries to spawn.
    {
        if(!IsRegistered[playerid])  //If the player isn't registered
        {
            ShowPlayerDialog(playerid, 15000, DIALOG_STYLE_INPUT, "Register","Your user is {FF0000}not{FFFFFF} registered! Please {0000FF}register{FFFFFF} with a password below!\n {FF0000}ERROR:You must register before spawning!","Register","Cancel"); //Shows our register dialog :).
            return 0; //Prevents the player from spawning
        }
        if(IsRegistered[playerid] == 1) //Our handy variable comes into use now
        {
            ShowPlayerDialog(playerid, 15500, DIALOG_STYLE_INPUT, "Login","Your user is {FF0000}registered{FFFFFF}! Please {0000FF}login{FFFFFF} with your password below!\n{FF0000} You must login before you spawn!","Login","Cancel"); //Shows our login dialog :).
            return 0; //Prevents the player from spawning
        }
    }
    return 1;
}
Now, I'm going to explain how to add a extra variable(s) to saving / loading. For this, I'm going to use the variable X Position. You can either edit the table in PhpMyAdmin or make a temporary script and do this
pawn Code:
public OnFilterScriptInit()
{
     mysql_connect(mysql_host, mysql_user, mysql_database, mysql_password);
     mysql_query("ALTER TABLE playerdata ADD XPOS FLOAT");
     return 1;
}
Then we go into the MySQL_Register function and edit this line
pawn Code:
format(query, sizeof(query), "INSERT INTO playerdata (user, password, score, money, IP) VALUES('%s', SHA1('%s'), 0, 0, '%s', 0.0)", pname, passwordstring, IP);
And add below the other mysql_fetch_feild_rows's.

pawn Code:
mysql_fetch_field_row(savingstring, "XPOS"); /*XPOS VARIABLE*/ = strval(savingstring);
Lastly, we need to edit the saving query.

pawn Code:
format(query, sizeof(query), "UPDATE playerdata SET score=%d, money=%d, XPOS=%f WHERE user='%s'", score, money, /*XPOS VARIABLE*/, pname);
CURRENT NOTES
1. Change the query sizes if you are using a lot of variables.
2. Field names are case sensitive
3. Post any bugs here .
4. All passwords are hashed with SHA1, that means you cannot decrypt SHA1 passwords once they are set. For more information, visit the Wikipedia Page
5. For all strings, you must surround them with ' and '. So a string would be represented as username = '%s' and not username = %s.

Conclusion
I hope that you guys have learnt something from this. If you have any questions, post them here. I've fully edited this as of the 9th of August. My MSN is in my signature if anyone needs me .


PHP PART (PART 2)


Introduction
I decided to make this tutorial because I had a basic one on my other MySQL tutorial and a lot of people used it. This is a continue on from my MySQL tutorial but it can be easily modified so that you can make it fit for your server. This tutorial requires almost no knowledge at all because I'm going to explain everything step by step. If you are stuck on a step, post here and I'll fix the tutorial up with a better explanation. For testing this, you need something that can run PHP. You are able to use the WAMP server and later on I'll explain how you can do that. If you are having trouble making any of these files, you can download it all at http://www.filejungle.com/f/Yp5ENE/mysql.rar.

Straight to the basics
We are going to start off with the most basic things possible. If you already know basic PHP / HTML you can skip this step and go straight to building the UCP. I'm going to explain how we implement PHP onto our webpage and how we print the information. We will start with PHP tags and commenting.

PHP Code:
<?php
//This is where PHP code goes in here. This is also how you can comment in PHP (Similar to PAWN).
/*
This is a multiple line 
comment that we can use
up multiple lines to comment :D.
*/
?>
OK, so what we have learnt from the above code is that we have to put all of our PHP code inside php tags (<?php to open and ?> to close). We have also learnt that we can comment in a very similar way to PAWN.

Now that we have got the extremely basic stuff out of the way, I'm going to show you how we print information from PHP onto our webpage. It's really simple, it's like printing information into the sa-mp console using print / printf except we are printing it to the webpage.

PHP Code:
<?php
//This is where PHP code goes in here. This is also how you can comment in PHP (Similar to PAWN).
/*
This is a multiple line 
comment that we can use
up multiple lines to comment :D.
*/
echo "Hello World";
?>
The above code will print to our page the text "Hello World". This shows us that we can print anything from PHP onto our webpage for the viewers to see. We must remember that with the same in pawn, we have semi colons at the end of every function. Now lets take a look into how we create and use variables in PHP

PHP Code:
$string = "It's A Wonderful Day!";
echo "Hello World $string"; 
The above code will print "Hello World It's A Wonderful Day!" because we are inserting a string inside another one. We are going to be using this later on to insert a value such as a stats value that we are going to print to the user. Now that we have finished the real basic PHP side of things, I'll explain a bit of the basic HTML that we are going to be using.

HTML Code:
<form name="input" action="next.php" method="post">
Username: <input type="text" name="user" /> <br />
<input type="submit" value="Submit" />
</form>
<!-- This is a comment! -->
The code above is probably the only HTML we are going to be using in this tutorial. The form tag creates a form that activates the action when you press the submit button. With HTML, functions are surrounded by '<' and '>'. Some functions have closing and opening tags. If they have closing tags (only the form function in this case) they do not have a '/' at the end of the tag. The input tag gives us a type of input whether it be a password box, a normal text box or a submit button. The <br /> tag is just for a new line, we are using it so that the submit button is on the line under the username text field because otherwise it would look silly having everything on a single line. Of course we don't stop here with this code because we still have to make the next.php to tell us what to do when we click that submit button and the action is called. We are using the post method in the form so we will use the post function in PHP to retrieve the information.

PHP Code:
$typedinformation  = $_POST["user"];
echo "You have typed the username $typedinformation in the box!"; 
The code above is setting the variable $typedinformation to the posted information from the previous page and then telling you what you typed in the box. We will also check if anything was even submitted into the box or if the person went to the next.php page straight away. If the person goes to the next.php page directly and there is no post variable then the PHP code will return errors and we don't want that to happen.

PHP Code:
if(!isset($_POST["user"]))
{
    echo "There is no value for the user variable";
}
else
{
    $typedinformation  = $_POST["user"];
    echo "You have typed the username $typedinformation in the box!";
} 
The isset function tells us if the variable exists and it will not return errors if it doesn't. This way we will not get a PHP error telling us that the variable doesn't exist if we go directly onto the page. It will say "There is no value for the user variable" instead. Last but not least is linking to other pages using HTML, it's really basic and we use it so that the people can go on the stats page and other pages.

HTML Code:
<a href="index.php">This will go to the index page</a>
The above code is pretty self explanatory. If you click on the text "This will go to the index page", the HTML will redirect it straight to the index.php page.

Lets get started!

Saving the pages
Each page will have a name above it that you have to save the file as because they all link together. Save the pages as all undercase letters. To save these documents, you can use Microsoft Notepad. Copy and paste the code into notepad -> Save As -> Save Type As - All Files -> Enter the name of the document -> Press the save button.

Wamp with PHP
You may not know this yet but you cannot open PHP documents in your browser and it will just work. All you have to do it put it in your WAMP server directory -> WWW -> Create a file called samp. Then visit http://localhost/samp.

Pages

The variables page
We are starting off with creating a variables page that will have our database information so it can be edited easily later. This saves you having to copy and paste all of this information onto each page, you can use the PHP include function like including a .inc with PAWN.


Page Name: variables.php
Use: Saving the database variables
Language: PHP
PHP Code:
<?php
$dbservername = "127.0.0.1";
$dbusername = "root";
$dbpassword = "";
$dbname = "sa-mp";
?>
More Explanation: The information above will obviously be changed by you to suit your needs because not everyone will have the same information.


The login page
For the login page, we are going to be using 2 pages. One page is the page with the form and the other page is going to be the page that checks if our information is correct.

Page Name: index.html
Use: Getting the information for the PHP file
Language: HTML
HTML Code:
<form name="input" action="login.php" method="post"> <!-- This is the form that will redirect to our next page using the post method when we click the submit button -->
Username: <input type="text" name="user" /> <br /> <!-- This is the text field that records our username and posts it to the PHP file --> 
Password: <input type="password" name="password" /> <br /> <!-- This is the password field that records our password and posts it to the PHP file. --> 
<input type="submit" value="Submit" /> <!-- The submit button -->
</form> <!-- End of the form -->
More Explanation: This will be a page with 2 input fields, one password box and one text box. The password box shows *'s instead of the actual letters so that you cant see what someone is typing. This is pretty much explained above in the basics section.

Page Name: login.php
Use: Checking if the user is correct / password is correct then showing them the UCP.
Language: PHP
PHP Code:
<?php
session_start(); //Starts our session variables, more explained below.
//Firstly we need to check if the information is posted
if((!isset($_POST["user"]) || !isset($_POST["password"])) && !isset($_SESSION["username"])) //Session variable will be explained below
{
    echo "There was no values for username or password posted!"; //Echoes that there is no username or password posted.
}
else
{
    include("variables.php"); //This includes our variables, same type of style as PAWN
    $connection = mysql_connect($dbservername, $dbusername, $dbpassword); 
    /* 
    We connect to the database here with the variables in our variables.php. 
    mysql_connect(HOST, USERNAME, PASSWORD)
    */
    mysql_select_db($dbname, $connection); 
    /* 
    We have a separate function to connect to our database (a bit silly tbh). 
    mysql_select_db(DATABASE NAME, CONNECTION IDENTIFIER)
    */
    //Below we are making sure the people submitting the information are not trying to MySQL inject or find a XSS     vulnerability. We are going to strip it of html elements using  mysql_escape_string.
    if(!isset($_SESSION["username"]))
    {
        $username = mysql_escape_string($_POST["user"]); //This gets the user variable.
        $password = mysql_escape_string($_POST["password"]); //This gets the password variable.
    }
    else $username = mysql_escape_string($_SESSION["username"]); //Sets the username to the saved     session variable!
    /*
    Below we check if the user exists with the password that the user entered. 
    This is where you will have to change the variables if you are not using my
    mysql tutorial as a guideline.
    */
    if(!isset($_SESSION["username"]))  $result = mysql_query("SELECT * FROM `playerdata` WHERE user='$username'     AND password=SHA1('$password')");
    /*Queries the database to see if there is a user and password the same as what we have entered.
    Passwords are encoded with SHA1 so they have to be converted to that before we compare (My MySQL tutorial).
    Explained further in further explanation */
    else $result = mysql_query("SELECT * FROM `playerdata` WHERE user='$username'");
    /* 
    If you are wondering why I've checked if the session variables
    are set, read the further explanation at the bottom.
    */
    if(!mysql_num_rows($result))
    /*
    Checks if it has returned anything with the password and username that we 
    have entered. If there is nothing, it will return 0. If there is a user the same
    with the same password, it will return 1. mysql_num_rows requires the resource
    result from mysql_query, this is one of the differences to PAWN.
    */
    {
        //No matches
        echo "The password or username you have entered is incorrect.";
    }
    else
    {
        //We found a match! Now we are going to get the information 
        $row = mysql_fetch_assoc($result);
        /*
        The code above is just making it so we can retrieve the values such
        as the players score and money so that we can print it to show the 
        user what their stats are. mysql_fetch_assoc pretty much allows us to
        fetch the arrays by name rather than by the order that they are in.
        $row['score'] instead of lets say $row[2]. This pretty much goes through
        */
        $score = $row["score"]; //Sets the variables to the value of score
        $money = $row["money"]; //Sets the variables to the value of score
        $currentip = $row["IP"]; //Sets the variables to the value of IP
        $_SESSION["username"] = $username;
        /*
        The code above is so that we don't have to log in every page. 
        Session variables are pretty much server sided variables for a 
        certain person. It's so we do not have to log in on every page
        of the website that we visit.
        */
        echo "Welcome $username to the user control panel! <br />"; //Will print "Welcome [HiC]TheKiller to the user control panel!" then it will go onto a new line.
        echo "Score: $score <br />"; //Will print my score
        echo "Money: $money <br />"; //Will print my cash
        echo "Current IP address on your account: $currentip <br />"; //Will print my current IP. You can take this out if you want.
        echo "<a href='changepass.html'>Change your password</a><br />"; //Links to the change password page.
        echo "<a href='setip.php'>Set your auto login IP</a><br />"; //Links to the auto login IP page
        echo "<a href='stats.html'>View another players statistics</a><br />"; //Links to the stats page.
        echo "<a href='logout.php'>Logout</a><br />"; //Links to the logout page
        mysql_close($connection); //Closes the MySQL connection.
    }
}
?>
Further Explanation: Firstly, the above serves 2 purposes, one being checking if the player information is correct and the second being showing the information if the player is already logged in. The session cookies allow us to do this and some parts are not called if the information is incorrect. The other functions are pretty much explained, for more information on mysql_fetch_assoc visit the PHP website.

Page Name: setip.php
Use: Sets the Auto login IP for the player, if you press the link, it will set the persons current ip.
Language: PHP

PHP Code:
<?php
session_start(); //Starts our session variables.
if(!isset($_SESSION["username"]))
{
    echo "You need to be logged in to set your IP!";
}
else
{
    include("variables.php"); //Includes our variables
    $connection = mysql_connect($dbservername, $dbusername, $dbpassword);  //Connects to the server
    mysql_select_db($dbname, $connection);  //Connects to the database
    $ip = $_SERVER["REMOTE_ADDR"]; //$_SERVER["REMOTE_ADDR"] gets the current IP of the person viewing the website
    $username = mysql_escape_string($_SESSION["username"]); //Get the username from our session variable
    mysql_query("UPDATE playerdata SET IP = '$ip' WHERE user='$username'"); //Updates the IP
    echo "IP set, redirecting in 5 seconds!";
    echo "<meta http-equiv='Refresh' content='5;url=login.php' />";
    /*
    The above code tells the server that it should redirect
    us in 5 seconds to the login.php page. It will refresh
    the page to the other page if you kind of understand
    what I'm trying to say :).
    */
    mysql_close($connection); //Closes the MySQL connection.
}
?>
Further Explanation: There isn't much to further explain here other than it gets the username and updates the IP value. You already know what the Update query does from the mysql tutorial above.

Page Name: logout.php
Use: Logs the player out, deletes the session variable.
Language: PHP
PHP Code:
<?php
session_start(); //Starts our session variables.
unset($_SESSION["username"]); //Deletes the session variable
echo "Logged out! Redirecting in 5 seconds."; //Shows that it's logged out.
echo "<meta http-equiv='Refresh' content='5;url=index.html' />"; //Redirects us to the main page 5 seconds later.
?>
Further Explanation: None needed.

Page Name: stats.html
Use: Goes to a input page for a player to enter the name of the persons stats he wants to view.
Language: HTML
HTML Code:
<form name="input" action="statview.php" method="post"> <!-- Goes to statview.php when the submit button is clicked. -->
Username: <input type="text" name="user" /> <br /> <!-- User field -->
<input type="submit" value="Submit" /> <!-- Submit button -->
</form> <!-- Closing the form tag -->
Further Explanation: None needed.

Page Name: statview.php
Use: Views the players stats that another played typed
Language: PHP
PHP Code:
<?php
if(!isset($_POST["user"])) echo "No input value found!";
else
{
    $username = mysql_escape_string($_POST["user"]); //Escapes the post value from the stats.html
    include("variables.php"); //Our handy dandy includes page!
    $connection = mysql_connect($dbservername, $dbusername, $dbpassword);  //Connects to the server
    mysql_select_db($dbname, $connection);  //Connects to the database
    $result = mysql_query("SELECT score, money FROM playerdata WHERE user = '$username'"); //Selects the users score and money from the database.
    if(!mysql_num_rows($result)) //If we could find the users data
    {
        echo "No user found"; //Self explanatory :).
        echo "<meta http-equiv='Refresh' content='5;url=stats.html' />"; //Redirects us back to the stat page 5 seconds later.
    }
    else
    {
       $array = mysql_fetch_array($result, MYSQL_ASSOC); //Fetches our variables for us.
       $score = $array['score']; //Sets $score to the result in our database for the players score.
       $money = $array['money']; //Sets $money to the result in our database for the players money.
       echo "$username has $score score and $$money cash!"; //Will output "[HiC]TheKiller has 20 score and $1000 cash"
       echo "<br /><a href='stats.html'>Go back to the stats page</a>"; //Goes onto a new line and then gives us a link to go back to our other page.
    }
    mysql_close($connection);
}
?>
Further Explanation: It's nothing new in the above. We first make our value safe, check if the user exists inside the database, then we get the users score and money using mysql_fetch_array and send the info to the user.

Page Name: changepass.html
Use: Will send the input to the next page so the user can change their password.
Language: HTML
HTML Code:
<form name="input" action="changepass.php" method="post"> <!-- Goes to changepass.php when the submit button is clicked. -->
Current Password: <input type="password" name="currentpassword" /> <br /> <!-- Current Password field -->
New password: <input type="password" name="newpass" /> <br /> <!-- The new password -->
Confirm new password: <input type="password" name="newpassconfirm" /> <br /> <!-- User field -->
<input type="submit" value="Submit" /> <!-- Submit button -->
</form> <!-- Closing the form tag -->
Further Explanation: None needed.

Page Name: changepass.php
Use: Changes the players password from the posted information from changepass.html
Language: PHP
PHP Code:
<?php
session_start(); //Starts our session variables.
if(!isset($_SESSION["username"])) echo "You are not logged in!"; //If the player isn't logged in
else
{
    /*
    Firstly we are going to check if the person typed the same thing for 
    the confirmation password and the new password. It's better to do
    that first, so we don't need to open a database connection for 
    nothing.
    */
    $newpass = mysql_escape_string($_POST['newpass']); //Makes our newpass safe
    $newpassconfirm = mysql_escape_string($_POST['newpassconfirm']); //Makes our confirm pass safe
    $password = mysql_escape_string($_POST['currentpassword']); // Makes our current pass safe
    $username = mysql_escape_string($_SESSION["username"]); //Makes our username safe.
    if($newpass != $newpassconfirm) //If the confirmation pass isn't the same
    {
        echo "Your new password was not the same as your confirmation password!"; //Sends the user a message that it's not the same
        echo "<meta http-equiv='Refresh' content='5;url=changepass.html' />"; //Redirects us back to the pass page 5 seconds later.
    }
    else
    {
        include("variables.php"); //Our handy dandy includes page!
        $connection = mysql_connect($dbservername, $dbusername, $dbpassword);  //Connects to the server
        mysql_select_db($dbname, $connection);  //Connects to the database
        $result = mysql_query("SELECT password FROM playerdata WHERE user = '$username' AND password = SHA1('$password')"); //Tries to find the line where our user and password are the ones we have specified.
        if(!mysql_num_rows($result)) //Current password is incorrect
        {
            echo "The current password typed is incorrect!"; //Sends the user a message
            echo "<meta http-equiv='Refresh' content='5;url=changepass.html' />"; //Redirects us back to the pass page 5 seconds later.
        }
        else //All the information is good to insert, our current password matches etc.
        {
            mysql_query("UPDATE playerdata SET password = SHA1('$newpass') WHERE user = '$username'");
            echo "The current password has been changed"; //Sends the user a message
            echo "<meta http-equiv='Refresh' content='5;url=login.php' />"; //Redirects us back to the player page 5 seconds later.
        }
        mysql_close($connection); //Closes our connection ;).
    }
}
?>
Further Explanation: Nothing new, it explains it all in the code.

Conclusion
This took a while to make and it may have a few bugs, so please make sure to tell me if their is any. If you have any questions about this part of the tutorial, add me on MSN or ask in this topic.

Re: Making a MySQL system - ViruZZzZ_ChiLLL - 14.07.2010

First!!

Awesome tut, [Hic]TheKiller :O

Re: Making a MySQL system - [HiC]TheKiller - 14.07.2010

Quote:
Originally Posted by ViruZZzZ_ChiLLL
View Post
First!!

Awesome tut, [Hic]TheKiller :O
Thanks .

Re: Making a MySQL system - DiddyBop - 14.07.2010

good job sexeh. now ima go eat a dohnut for brakfast.

Re: Making a MySQL system - willsuckformoney - 14.07.2010

Quote:
Originally Posted by LilGunna
View Post
good job sexeh. now ima go eat a dohnut for brakfast.
lulz fatteh! xD and good work HiC 3 hours worth it lol

Re: Making a MySQL system - hab2ever - 14.07.2010

Wow! really awesome
thanks ...

Re: Making a MySQL system - Jeffry - 14.07.2010

This might be usefull for me soon. Good job.

Re: Making a MySQL system - [DK]JaloNik - 14.07.2010

Awesomeness

Re: Making a MySQL system - Guest3598475934857938411 - 14.07.2010

I don't hate MySql but it can be sql injected easily!

Re: Making a MySQL system - [HiC]TheKiller - 15.07.2010

Quote:
Originally Posted by LilGunna
View Post
good job sexeh. now ima go eat a dohnut for brakfast.
I ate a crumpet .

Quote:
Originally Posted by willsuckformoney
View Post
lulz fatteh! xD and good work HiC 3 hours worth it lol
Lol, I worked from 11PM - 2AM D=.

Quote:
Originally Posted by hab2ever
View Post
Wow! really awesome
thanks ...
Thanks

Quote:
Originally Posted by Jeffry
View Post
This might be usefull for me soon. Good job.
No problem

For the last 2 comments (For some reason my net won't open them)

Thanks and I know MySQL can be injected easly, I will add things so it makes it harder for hackers .

Re: Making a MySQL system - Hiddos - 15.07.2010

Meh, I'm still using fopen & frwite and that boring stuff, just because I didn't felt like doing MySQL. But reading this tutorial, I'll give it a try.

Thanks Killer, awesome tut.

Re: Making a MySQL system - willsuckformoney - 15.07.2010

Quote:
Originally Posted by [HiC]TheKiller
View Post
I ate a crumpet .



Lol, I worked from 11PM - 2AM D=.



Thanks



No problem

For the last 2 comments (For some reason my net won't open them)

Thanks and I know MySQL can be injected easly, I will add things so it makes it harder for hackers .
want me to rub??

Re: Making a MySQL system - FireCat - 16.07.2010

hey epro

Re: Making a MySQL system - Carlton - 24.07.2010

@RealCop228

Password: <inputbox> (That's obviously an input box for your password)

I then write inside of the box:

Quote:

'; DROP TABLE SA-MP;
That's injection.

Re: Making a MySQL system - Scenario - 24.07.2010

Quote:
Originally Posted by Carlton
View Post
@RealCop228

Password: <inputbox> (That's obviously an input box for your password)

I then write inside of the box:



That's injection.
Yikes! That doesn't sound good, how could I fix that?

Re: Making a MySQL system - legodude - 01.08.2010

when i start samp server it says to me:
Cant Load LIBMYSQL.dll(heavily shortened and really with capitals)

HELP!!

Re: Making a MySQL system - skaTim - 05.09.2010

Quote:
Originally Posted by legodude
View Post
when i start samp server it says to me:
Cant Load LIBMYSQL.dll(heavily shortened and really with capitals)

HELP!!
Same problem, any suggestions?

Thank you.

Re: Making a MySQL system - [HiC]TheKiller - 17.09.2010

Hiddos, you pretty much will get the hang of it if you understand the basics here. It took me a while to get a hang of MySQL but there was no tutorials around then.

Also, to all the other posts about security. I'll add escapes all the SQL queries tomorrow.

Re: Making a MySQL system - Hiddos - 17.09.2010

Thanks for explaining, but what are mostly the advantages of SQL, when compared to fwrite, if I do NOT use it for anything but saving (No connection with anything different then the server to save player stats).

Thanks in advance ^^

Re: Making a MySQL system - [03]Garsino - 17.09.2010

Excellent, I learnt a lot from this, thanks!