Store player informations with MySQL.

[IzNoGoD]

1. its easy to exploit your database system, just naming myself something with ' will escape your string-input. User input == evil, so treat it as such.
2. You should look into insert...on duplicate key update....
3. You are using the most basic mysql functions, which can cause your server to lagg. Try looking into the async mysql module i inserted into libcod.
4. Can you confirm by using phpmyadmin or heidisql that the changes are actually in the database? if not, your connection to the database might not even exist.

Edit 5. your insert into doesnt specify the guid of the player, which is the primary key of the table and should always be set.

[ebusiangamers]

Thank you for replying.
1. I'm new to MySQL so I'm kinda struggling with it.
2. Will try and make a report on it.
3. I'm testing CoDUOExtended (made by phpcod, not yet released), thus I can just use provided functions/methods.
4. I use phpmyadmin to confirm: no changes were made in the database but still, my connection exists: if not, the game crashes.

		mysql_stuff() 
{
    level.host = getCvar("mysql_host"); 
    level.pass = getCvar("mysql_password"); 
    level.database = getCvar("mysql_database"); 
    level.user = getCvar("mysql_user");  
    level.table = getCvar("mysql_table");
    
    level.mysql_connection = xmysql_connect(level.host, level.user, level.pass, level.database, 3306);    
    
    if(!isdefined(level.mysql_connection) || level.mysql_connection == 0) 
    {
        logprint("mysql not defined");
        return;
    }
    else
        logprint("MySQL: Connect to "+ level.host +" successfully");
} 
	

5. I forgot it.

[ebusiangamers]

I got the function get_db_field(fieldname) to work:

		get_db_field(fieldname) {
    query = "SELECT * FROM "+level.table+" WHERE `user_name`='"+ self.name +"' LIMIT 1";
    mysql_query(level.mysql_connection, query);
    r = mysql_store_result(level.mysql_connection);
    ret = r;
    num = mysql_num_fields(r);
    if(num != 0) {
        row = mysql_fetch_row(r);
        if(isdefined(row)) {
            mysql_query(level.mysql_connection, "SELECT `"+fieldname+"` FROM `"+level.table+"` WHERE `user_name`='"+self.name+"' LIMIT 1");
            r2=mysql_store_result(level.mysql_connection);
            ret = r2;
            if(mysql_num_fields(r2) > 0) {
                row2=mysql_fetch_row(r2);
                if(isdefined(row2)) {
                    if(isdefined(row2[0])) {
                        //printconsole("row2="+row2[0]+"\n");
                        ret = row2[0];
                    }
                }
            }
            mysql_free_result(r2); 
        }
    }
    mysql_free_result(r);
    return ret;
} 
	

However, the set_db_field(fieldname, value) still not working: with helps from previous replies, it works OCCASIONALLY.

		set_db_field(fieldname, value) 
{
    if(isDefined(self.isbot))
        return;
    query = "SELECT * FROM "+level.table+" WHERE `user_name`='"+ self.name +"' LIMIT 1";
    mysql_query(level.mysql_connection, query);
    r = mysql_store_result(level.mysql_connection);
    num = mysql_num_fields(r);
    if(num != 0) {
        row = mysql_fetch_row(r);
        if(isdefined(row)) {
            mysql_query(level.mysql_connection, "UPDATE `"+ level.table +"` SET `"+fieldname+"`='"+value+"' WHERE `user_name` = '"+self.name+"' LIMIT 1");
        }
        else
            mysql_query(level.mysql_connection, "INSERT INTO `"+level.table+"`(`"+fieldname+"`) VALUES ('"+value+"')");
    }
    else
        mysql_query(level.mysql_connection, "INSERT INTO `"+level.table+"`(`"+fieldname+"`) VALUES ('"+value+"')");
    mysql_free_result(r);
} 
	

Please help.

[ebusiangamers]

1. its easy to exploit your database system, just naming myself something with ' will escape your string-input. User input == evil, so treat it as such.
2. You should look into insert...on duplicate key update....
3. You are using the most basic mysql functions, which can cause your server to lagg. Try looking into the async mysql module i inserted into libcod.
4. Can you confirm by using phpmyadmin or heidisql that the changes are actually in the database? if not, your connection to the database might not even exist.

5. your insert into doesnt specify the guid of the player, which is the primary key of the table and should always be set.

1. I dont really understand this point. Please explain.
2. Use "INSERT...ON DUPLICATE UPDATE..." doesn't change anything.
3. This is CoDUOExtended, so it doesn't have it.
5. The where user_guid = self getGuid() doesn't work with insert