[Pawn] MySQL Question

[kalEd]

What is the most efficient and optimized method to save data in the database?



For example this?:

Code:

new gQuery[ 256?]; //at the beginning of the gamemode

//in a function=>

gQuery[ 0 ] = EOS;

mysql_format( handle, gQuery, sizeof gQuery, "INSERT INTO `logs` (`Player`, `IP`) VALUES ('%e', '%s')", getName( playerid ), getPlayerIp( playerid ) );

mysql_tquery( handle, gQuery, "", "" );

or this?:

Code:

new Query[ 90 ];

mysql_format( handle, Query, sizeof Query, "INSERT INTO `logs` (`Player`, `IP`) VALUES ('%e', '%s')", getName( playerid ), getPlayerIp( playerid ) );

mysql_tquery( handle, Query, "", "" );

What exactly does gQuery[0] = EOS; mean? What's the difference?

[Virsenas]

What is the most efficient and optimized method to save data in the database?



For example this?:

Code:

new gQuery[ 256?]; //at the beginning of the gamemode

//in a function=>

gQuery[ 0 ] = EOS;

mysql_format( handle, gQuery, sizeof gQuery, "INSERT INTO `logs` (`Player`, `IP`) VALUES ('%e', '%s')", getName( playerid ), getPlayerIp( playerid ) );

mysql_tquery( handle, gQuery, "", "" );

or this?:

Code:

new Query[ 90 ];

mysql_format( handle, Query, sizeof Query, "INSERT INTO `logs` (`Player`, `IP`) VALUES ('%e', '%s')", getName( playerid ), getPlayerIp( playerid ) );

mysql_tquery( handle, Query, "", "" );

What exactly does gQuery[0] = EOS; mean? What's the difference?

EOS: predefined constant standing for End Of String; it has the value ?n0?

Basically, it makes the array (string variable) empty, hold no real value. You do not even need to use it in that mysql code because the gQuery string is already empty. So second code is the way things should be.



Use this if you encounter something you don't know.

https://github.com/compuphase/pawn/blob/..._Guide.pdf

[Kwarde]

Basically, it makes the array (string variable) empty, hold no real value

Less basically, it sets the first cell to "EOS" (End Of String, null terminator, \0).
PAWN uses the null terminator to determine the end of a string. For example, strlen() counts the amount of characters (or cells) untill the null terminator is found.
Example:

Code:

new myArray[] = "Hello World";
//myArray now is:
//{'H', 'e', 'l', 'l', 'o', ' ', 'W', 'o', 'r', 'l', 'd', '\0'}
printf("%s -- length: %i", myArray, strlen(myArray)); //Would print: Hello -- length: 11

myArray[4] = EOS;
//myArray now is:
//{'H', 'e', 'l', 'l', '\0', ' ', 'W', 'o', 'r', 'l', 'd', '\0'}
printf("%s -- length: %i", myArray, strlen(myArray)); //Would print: Hell -- length: 4

myArray[0] = EOS;
//myArray now is:
//{'\0', 'e', 'l', 'l', ''0', ' ', 'W', 'o', 'r', 'l', 'd', '\0'}
printf("%s -- length: %i", myArray, strlen(myArray)); //Would print:  -- length: 0

So setting the first cell to EOS does not erase all the cells. It merely tells the string ends there. That plus:

You do not even need to use it in that mysql code because the gQuery string is already empty

So it's really just useless. When you're formatting a string it doesn't matter where the null terminator is or if a string is empty or not. The null terminator (EOS) gets set at the end of the string after formatting anyway.
Few other sidenotes:
- It's not needed to escape a username. Usernames can't contain characters that might cause SQL queries to fail (/SQL injection).
- To answer the question "What is the most efficient and optimized method to save data in the database?":
1- You might want to save IPs as an integer (unsigned integer) using INET_ATON (Address To Number) and INET_NTOA (Number To Address).
2- Don't save the player's name but their account ID (assuming you have a users table where users have an ID. If you don't have that you probably have a poor optimised database).
Example:

Code:

/*
Example users table:
----
CREATE TABLE users(
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    name VARCHAR(24) NOT NULL UNIQUE,
    PRIMARY KEY(id)
) ENGINE = InnoDB;
----
Example logs table:
----
CREATE TABLE logs( --
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    player INT UNSIGNED NOT NULL, -- You probably already have a users table. Link to the account ID and not the name
    ip INT UNSIGNED NOT NULL,
    -- Foreign key linking 'player' to the users table (account id):
    CONSTRAINT fk_logs_to_users FOREIGN KEY(player) REFERENCES users(id) ON DELETE CASCADE ON UPDATE CASCADE,
    PRIMARY KEY(id)
) ENGINE = InnoDB;
*/
//** Saving the log (connection log?) to the database **//
format(gQuery, sizeof(gQuery), "INSERT INTO logs (player, ip) VALUES ('%s', INET_ATON('%s'));", getAccountId(playerid), getPlayerIp(playerid));
mysql_tquery(handle, gQuery);

//** Loading the log **//
format(gQuery, sizeof(gQuery), "SELECT u.name AS username, INET_NTOA(l.ip) AS userip FROM logs l LEFT JOIN users u ON l.player = u.id WHERE u.name = '%s';", getName(playerid)); //(...)WHERE u.id = %i;", getAccountId(playerid); -- Would be better since the id is the primary key
inline on_load_log()
{
    new rows;
    cache_get_row_count(rows);
    if (rows)
    {
        new name[MAX_PLAYER_NAME], ip[16];
        for (new i; i < rows; i)
        {
            cache_get_value_name(i, "username", name);
            cache_get_value_name(i, "userip", ip);
            //Do stuff with 'username' and 'userip'
        }
    }
    else
    {
        //No logs available for this user
    }
}
MySQL_TQueryInline(handle, gQuery, using inline on_load_log); //Just an example of using y_inline with MySQL. Otherwise you would be using something like this:
/*
mysql_tquery(handle, gQuery, "on_load_log", "i", playerid);
forward on_load_log(playerid);
public on_load_log(playerid)
{
    //The code above that is in the inline function on_load_log
}
*/

- The parameters of mysql_tquery():

(MySQL:handle, const query[], const callback[] = "", const format[] = "", {Float,_}:...)

'callback' is by default : "" and format is also by default: "". There is no need to include it if you're leaving it empty. Thus this would suffice:

Code:

mysql_tquery(handle, gQuery);

[Y_Less]

Those are the same thing. The location of the string makes no difference to the efficiency of the query. If you want to start learning about SQL optimisations I suggest you start with https://use-the-index-luke.com/

[kalEd]

Thanks to everyone for the help.