[Tutorial] MySQL Registration System [Threaded Queries(R33+) + Whirlpool]

[PowerPC603]

Originally Posted by Vince

This looks good, just a couple remarks. It is common practice to write definitions in UPPERCASE and all other variables (ID, VIP) in lowercase or CamelCase. The other thing; I don't really consider it useful creating tables from within the script. This will most likely only ever be executed once in the lifetime of the server. Lastly, only select the fields you will actually need instead of using the '*' selector. In you account check function, for example, you only use the password.

If some gamemode uses many tables, it would be nice to see the script creating them automatically.
Would you feel fine if a downloaded gamemode needs 10 tables, where every table has more than 10 columns, to create them by hand?
Should the author of the gamemode write a manual about how the tables should be named, which column has which name, content-type (int, float, text, byte, ...), instead of adding a line of code which will be used only once, as you said?

I'd rather have the gamemode create them in such a case, even if that code is executed only once.
Then everyone is certain they have the proper table-name and table-structure and don't have to fiddle around trying to get all tables created manually and testing if the script works using your manually created table.
I created a table with 35 columns a few days ago and it took a while to set it up.



Nice tutorial btw, I'll be using this.

[Konstantinos]

Originally Posted by PowerPC603

If some gamemode uses many tables, it would be nice to see the script creating them automatically.
Would you feel fine if a downloaded gamemode needs 10 tables, where every table has more than 10 columns, to create them by hand?
Should the author of the gamemode write a manual about how the tables should be named, which column has which name, content-type (int, float, text, byte, ...), instead of adding a line of code which will be used only once, as you said?

I'd rather have the gamemode create them in such a case, even if that code is executed only once.
Then everyone is certain they have the proper table-name and table-structure and don't have to fiddle around trying to get all tables created manually and testing if the script works using your manually created table.
I created a table with 35 columns a few days ago and it took a while to set it up.



Nice tutorial btw, I'll be using this.

They can create a .txt file with the queries for creating each table. All you will need then is just to copy them, paste them into phpMyAdmin and execute them once.

[PowerPC603]

And if you have multiple tables which should have data in them already, let's say 1000 records per table?

Suppose I would convert all my files into SQL-tables.
Those files hold all:
- mission vehicles
- cargo-types
- locations
- route-data
- police stations
- gas stations
- vehicle-info (max-fuel, fuel-consumption, refuel times per vehicle etc)

They would have to copy/paste several thousand pre-defined queries from the txt file.
It would be nicer if the script itself creates the tables and inserts all data when starting the server for the first time.
Or perhaps make a .bat file to execute them.

[Tamer]

Hi guys, I got R33 and compiled it without editing the code on the tutorial, when I try to load the plugin, I get this error:

[13:36:48] Could not connect to database!
[13:36:48] Script[gamemodes/mysqlbase.amx]: Run time error 20: "Invalid index parameter (bad entry point)"

I'm about the go mad, because I couldn't get this fixed in a week. I am really bored of trying.

[Konstantinos]

Originally Posted by Tamer T

Hi guys, I got R33 and compiled it without editing the code on the tutorial, when I try to load the plugin, I get this error:

[13:36:48] Could not connect to database!
[13:36:48] Script[gamemodes/mysqlbase.amx]: Run time error 20: "Invalid index parameter (bad entry point)"

I'm about the go mad, because I couldn't get this fixed in a week. I am really bored of trying.

Add:

main() {}

Just googling the run time error or using the search button, you'd have found the solution in 10 seconds rather than waiting for a week to find out what's the problem.

@PowerPC603: We're talking about creating tables within the script only, not records. Even if that, would you insert those stuff again and again after a server start/restart? That'd be pointless. You can insert many records by executing only once by separating them with a semicolon (;).

[Tamer]

Originally Posted by Konstantinos

Add:

main() {}

Just googling the run time error or using the search button, you'd have found the solution in 10 seconds.


@PowerPC603: We're talking about creating tables within the script only, not records. Even if that, would you insert those stuff again and again after a server start/restart? That'd be pointless. You can insert many records by executing only once by separating them with a semicolon (.

I gotta blame myself because 2 mins later I posted this, I tended to use the almigthy search function and found it. I got no idea why did I not do this at the first place. Thanks.

[Tamer]

I found a bug. When you register and quit, your stats are not saved. To re-produce the bug follow these steps:

Copy the whole tutorial code,


Add this command to the script:

CMD:statson(playerid,params[])
{
    pInfo[playerid][Admin] = 8;
        pInfo[playerid][VIP] = 8;
        pInfo[playerid][Money] = 8;
        return 1;
}

compile it, open the server, make sure all MySQL databases are running and it connected.

Register to the server and execute the /statson command, then quit the server. Check your MySQL databases, the info's will not be updated. However, the user will be registered.

Then, re-join to the server (make sure you are registered) you will have a login window, which is normal. Now execute the /statson command and quit. Now your stats will be saved.

This is pretty annoying. Stats should save as soon as the player registers. It should not wait for the player to re-connect.

[The-Krew]

can you explain how to add some extra variables when the table already exist?

[Blast3r]

Originally Posted by Tamer T

I found a bug. When you register and quit, your stats are not saved. To re-produce the bug follow these steps:

Copy the whole tutorial code,


Add this command to the script:

CMD:statson(playerid,params[])
{
    pInfo[playerid][Admin] = 8;
        pInfo[playerid][VIP] = 8;
        pInfo[playerid][Money] = 8;
        return 1;
}

compile it, open the server, make sure all MySQL databases are running and it connected.

Register to the server and execute the /statson command, then quit the server. Check your MySQL databases, the info's will not be updated. However, the user will be registered.

Then, re-join to the server (make sure you are registered) you will have a login window, which is normal. Now execute the /statson command and quit. Now your stats will be saved.

This is pretty annoying. Stats should save as soon as the player registers. It should not wait for the player to re-connect.

Confirmed, anyone managed to fix this problem? Because I just can't seem to fix it :/

[PowerPC603]

case 2: //register dialog
        {
            if(!response) return Kick(playerid); //if they clicked Quit, we will kick them
            if(strlen(inputtext) < 6) return ShowPlayerDialog(playerid, 2, DIALOG_STYLE_INPUT, "Register", "In order to play, you need to register.\nYour password must be at least 6 characters long!", "Register", "Quit");
            //strlen checks a lenght of a string. so if player types their password that is lower than 6, we tell them; Your password must be at least 6 characters long!
            WP_Hash(pInfo[playerid][Password], 129, inputtext); //hashing inputtext
            mysql_format(mysql, query, sizeof(query), "INSERT INTO `players` (`Username`, `Password`, `IP`, `Admin`, `VIP`, `Money`, `posX` ,`posY`, `posZ`) VALUES ('%e', '%s', '%s', 0, 0, 0, 0.0, 0.0, 0.0)", Name[playerid], pInfo[playerid][Password], IP[playerid]);
            //Now let's create a new row and insert player's information in it
            mysql_tquery(mysql, query, "", "");
            //let's execute the query
        }
    }

When the player registers his account, the data is inserted properly.
BUT the tutorial doesn't have the ID of the player in the database.
Only the player's name, password and IP are inserted.
The table auto-creates the ID in MySQL, but it's not loaded and stays 0 in the script.

When exiting the game, OnPlayerDisconnect tries to update data for ID 0, which doesn't exist.

When the player logs back in (using the code from the tutorial), ALL data is read properly, including the ID.
Therefore it can be saved properly during OnPlayerDisconnect because now the script holds the proper ID of the player to update the data.




The code above should become (adjusted the query here):

case 2: //register dialog
        {
            if(!response) return Kick(playerid); //if they clicked Quit, we will kick them
            if(strlen(inputtext) < 6) return ShowPlayerDialog(playerid, 2, DIALOG_STYLE_INPUT, "Register", "In order to play, you need to register.\nYour password must be at least 6 characters long!", "Register", "Quit");
            //strlen checks a lenght of a string. so if player types their password that is lower than 6, we tell them; Your password must be at least 6 characters long!
            WP_Hash(pInfo[playerid][Password], 129, inputtext); //hashing inputtext
            mysql_format(mysql, query, sizeof(query), "INSERT INTO `players` (`Username`, `Password`, `IP`, `Admin`, `VIP`, `Money`, `posX` ,`posY`, `posZ`) VALUES ('%e', '%s', '%s', 0, 0, 0, 0.0, 0.0, 0.0)", Name[playerid], pInfo[playerid][Password], IP[playerid]);
            //Now let's create a new row and insert player's information in it
            mysql_tquery(mysql, query, "OnAccountCreate", "i", playerid);  // Adjusted the query
            //let's execute the query
        }
    }

Then use code like this:

// This custom callback is used after creating a new account (playername and password are inserted, but the ID must be loaded now to reference the player later on using this ID)
forward OnAccountCreate(playerid);
public OnAccountCreate(playerid)
{
    // Setup local variables
    new Query[128];

    // Construct the query to get the new player's SQLID for later referencing and execute it
    mysql_format(SQL_db, Query, sizeof(Query), "SELECT ID FROM %s WHERE PlayerName = '%e' LIMIT 1", table_playerdata, APlayerData[playerid][PlayerName]);
    mysql_tquery(SQL_db, Query, "OnAccountCreateLoadID", "i", playerid);

    return 1;
}

// This custom callback is used after creating a new account and executing the query to load the new player's SQLID for later referencing
forward OnAccountCreateLoadID(playerid);
public OnAccountCreateLoadID(playerid)
{
    // Store the loaded ID
    APlayerData[playerid][SQLID] = cache_get_field_content_int(0, "ID", SQL_db);

    return 1;
}

Of course, adjust database names, variablenames and mysql-connectionhandle to the proper values in your script, as this was literally taken out of my script.