mysql - duplicated rows - Printable Version

mysql - duplicated rows - 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)
+--- Thread: mysql - duplicated rows (/showthread.php?tid=531124)
mysql - duplicated rows - cyanogen - 11.08.2014

I noticed some issues with my sql DB while I was browsing through the rows that players have created. Some (not all) have information that is completely identical. They usually occur consecutively as well. The differences between the rows is the username they registered, the ID, and the IP they registered with. Everything else is identical. Relevant code following:
Код:
#define dRegister 6287
#define dLogin 6288

//sql config
#define mysql_host "edited"
#define mysql_user "edited"
#define mysql_password "edited"
#define mysql_database "edited"


//sql connection handle
new mysql = -1;
new Name[MAX_PLAYERS][24], IP[MAX_PLAYERS][16];

enum PlayerInfo
{
    Adminlevel, //User's admin level
    ID,
    Password[129],
    Money, //User's money
    Kills, //User's kills
    Deaths, //User's deaths
    Bounty, //User's bounty
    isMuted, //Whether or not they are muted.
    StartYear, //Day player started.
    StartMonth,
    StartDay,
    StartIP[16], //IP of the player when they register
    TogglePM,
    ToggleG,
    knifeK,
    chainsawK,
    colt45K,
    silencedK,
    deagleK,
    shotgunK,
    sawedoffK,
    spazK,
    uziK,
    mp5K,
    ak47K,
    m4K,
    tec9K,
    rifleK,
    sniperK,
    dWins,
    dLosses,
    silencedUnk,
    colt45Unk,
    deagleUnk, 
    shotgunUnk,
    spazUnk,
    sawedoffUnk,
    uziUnk,
    mp5Unk,
    ak47Unk,
    m4Unk,
    rifleUnk,
    sniperUnk,
    parachuteUnk,
    tec9Unk,
    armorUnk,
    dildoUnk,
    flowerUnk,
    caneUnk,
    brassUnk,
    clubUnk,
    nitestickUnk,
    knifeUnk,
    batUnk,
    shovelUnk,
    poolstickUnk,
    katanaUnk,
    chainsawUnk
}

new gData[MAX_PLAYERS][PlayerInfo];

public OnGameModeInit()
{
       //other code
        mysql_log(LOG_ERROR | LOG_WARNING | LOG_DEBUG);

        mysql = mysql_connect(mysql_host, mysql_user, mysql_database, mysql_password);

	if(mysql_errno(mysql) != 0) print ("Could not connect to the database!");
}
public OnPlayerConnect(playerid)
{
        //other code
        GetPlayerName(playerid, Name[playerid], 24);
        GetPlayerIp(playerid, IP[playerid], 16);
        mysql_format(mysql, query, sizeof(query), "SELECT `Password`, `ID` FROM `playerinfo` WHERE `Username` = '%e' LIMIT 1", Name[playerid]);
        mysql_tquery(mysql, query, "OnAccountCheck", "d", playerid);
}
public OnPlayerDisconnect(playerid)
{
     new query[300];

	mysql_format(mysql, query, sizeof(query), "UPDATE `playerinfo` SET `Adminlevel`=%d, `Money`=%d,  `Kills`=%d WHERE `ID`=%d", gData[playerid][Adminlevel], gData[playerid][Money], gData[playerid][Kills], gData[playerid][ID]);
	mysql_tquery(mysql, query, "", "");
	mysql_format(mysql, query, sizeof(query), "UPDATE `playerinfo` SET `Deaths`=%d, `Bounty`=%d, `isMuted`=%d WHERE `ID`=%d", gData[playerid][Deaths], gData[playerid][Bounty], gData[playerid][isMuted], gData[playerid][ID]);
	mysql_tquery(mysql, query, "", "");
	mysql_format(mysql, query, sizeof(query), "UPDATE `playerinfo` SET `TogglePM`=%d, `ToggleG`=%d, `knifeK`=%d WHERE `ID`=%d", gData[playerid][TogglePM], gData[playerid][ToggleG], gData[playerid][knifeK], gData[playerid][ID]);
	mysql_tquery(mysql, query, "", "");
	mysql_format(mysql, query, sizeof(query), "UPDATE `playerinfo` SET `chainsawK`=%d, `colt45K`=%d, `silencedK`=%d WHERE `ID`=%d", gData[playerid][chainsawK], gData[playerid][colt45K], gData[playerid][silencedK], gData[playerid][ID]);
	mysql_tquery(mysql, query, "", "");
	mysql_format(mysql, query, sizeof(query), "UPDATE `playerinfo` SET `deagleK`=%d, `shotgunK`=%d, `sawedoffK`=%d WHERE `ID`=%d", gData[playerid][deagleK], gData[playerid][shotgunK], gData[playerid][sawedoffK], gData[playerid][ID]);
	mysql_tquery(mysql, query, "", "");
	mysql_format(mysql, query, sizeof(query), "UPDATE `playerinfo` SET `spazK`=%d, `uziK`=%d, `mp5K`=%d WHERE `ID`=%d", gData[playerid][spazK],gData[playerid][uziK], gData[playerid][mp5K], gData[playerid][ID]);
	mysql_tquery(mysql, query, "", "");
	mysql_format(mysql, query, sizeof(query), "UPDATE `playerinfo` SET `ak47K`=%d, `m4K`=%d, `tec9K`=%d WHERE `ID`=%d", gData[playerid][ak47K], gData[playerid][m4K], gData[playerid][tec9K], gData[playerid][ID]);	
	mysql_tquery(mysql, query, "", "");
	mysql_format(mysql, query, sizeof(query), "UPDATE `playerinfo` SET `rifleK`=%d, `sniperK`=%d, `dWins`=%d WHERE `ID`=%d", gData[playerid][rifleK], gData[playerid][sniperK], gData[playerid][dWins], gData[playerid][ID]);
	mysql_tquery(mysql, query, "", "");
	mysql_format(mysql, query, sizeof(query), "UPDATE `playerinfo` SET `dLosses`=%d  WHERE `ID`=%d", gData[playerid][dLosses], gData[playerid][ID]);
	mysql_tquery(mysql, query, "", "");
	mysql_format(mysql, query, sizeof(query), "UPDATE `playerinfo` SET `silencedUnk`=%d, `colt45Unk`=%d, `deagleUnk`=%d WHERE `ID`=%d", gData[playerid][silencedUnk], gData[playerid][colt45Unk], gData[playerid][deagleUnk], gData[playerid][ID]);
	mysql_tquery(mysql, query, "", "");
	mysql_format(mysql, query, sizeof(query), "UPDATE `playerinfo` SET `shotgunUnk`=%d, `spazUnk`=%d, `sawedoffUnk`=%d WHERE `ID`=%d", gData[playerid][shotgunUnk], gData[playerid][spazUnk], gData[playerid][sawedoffUnk], gData[playerid][ID]);
	mysql_tquery(mysql, query, "", "");
	mysql_format(mysql, query, sizeof(query), "UPDATE `playerinfo` SET `uziUnk`=%d, `mp5Unk`=%d, `ak47Unk`=%d WHERE `ID`=%d", gData[playerid][uziUnk], gData[playerid][mp5Unk], gData[playerid][ak47Unk], gData[playerid][ID]);
	mysql_tquery(mysql, query, "", "");
	mysql_format(mysql, query, sizeof(query), "UPDATE `playerinfo` SET `m4Unk`=%d, `rifleUnk`=%d, `sniperUnk`=%d WHERE `ID`=%d", gData[playerid][m4Unk], gData[playerid][rifleUnk], gData[playerid][sniperUnk], gData[playerid][ID]);
	mysql_tquery(mysql, query, "", "");
	mysql_format(mysql, query, sizeof(query), "UPDATE `playerinfo` SET `parachuteUnk`=%d, `tec9Unk`=%d, `armorUnk`=%d WHERE `ID`=%d", gData[playerid][parachuteUnk], gData[playerid][tec9Unk], gData[playerid][armorUnk], gData[playerid][ID]);
	mysql_tquery(mysql, query, "", "");
	mysql_format(mysql, query, sizeof(query), "UPDATE `playerinfo` SET `dildoUnk`=%d, `flowerUnk`=%d, `caneUnk`=%d WHERE `ID`=%d", gData[playerid][dildoUnk], gData[playerid][flowerUnk], gData[playerid][caneUnk], gData[playerid][ID]);
	mysql_tquery(mysql, query, "", "");
	mysql_format(mysql, query, sizeof(query), "UPDATE `playerinfo` SET `brassUnk`=%d, `clubUnk`=%d, `nitestickUnk`=%d WHERE `ID`=%d", gData[playerid][brassUnk], gData[playerid][clubUnk], gData[playerid][nitestickUnk], gData[playerid][ID]);
	mysql_tquery(mysql, query, "", "");
	mysql_format(mysql, query, sizeof(query), "UPDATE `playerinfo` SET `knifeUnk`=%d, `batUnk`=%d, `shovelUnk`=%d WHERE `ID`=%d", gData[playerid][knifeUnk], gData[playerid][batUnk], gData[playerid][shovelUnk], gData[playerid][ID]);
	mysql_tquery(mysql, query, "", "");
	mysql_format(mysql, query, sizeof(query), "UPDATE `playerinfo` SET `poolstickUnk`=%d, `katanaUnk`=%d, `chainsawUnk`=%d WHERE `ID`=%d", gData[playerid][poolstickUnk], gData[playerid][katanaUnk], gData[playerid][chainsawUnk], gData[playerid][ID]);
	mysql_tquery(mysql, query, "", "");
}
forward OnAccountCheck(playerid);
public OnAccountCheck(playerid)
{
	new rows, fields;
	cache_get_data(rows, fields, mysql);
	if(rows)
	{
		cache_get_field_content(0, "Password", gData[playerid][Password], mysql, 129);
		gData[playerid][ID] = cache_get_field_content_int(0, "ID");
	   	ShowPlayerDialog(playerid, dLogin, DIALOG_STYLE_PASSWORD, "Login", "Type your password.", "Confirm", "Decline");
	}
	else
	{
		ShowPlayerDialog(playerid, dRegister, DIALOG_STYLE_PASSWORD, "Register", "Type in a password you can remember.", "Confirm", "Decline");
	}
	return 1;
}
forward OnAccountLoad(playerid);
public OnAccountLoad(playerid)
{
	gData[playerid][ID] = cache_get_field_content_int(0, "ID");
	gData[playerid][Adminlevel] = cache_get_field_content_int(0, "Adminlevel");
	gData[playerid][Money] = cache_get_field_content_int(0, "Money");
	gData[playerid][Kills] = cache_get_field_content_int(0, "Kills");
	gData[playerid][Deaths] = cache_get_field_content_int(0, "Deaths");
	gData[playerid][Bounty] = cache_get_field_content_int(0, "Bounty");
	gData[playerid][isMuted] = cache_get_field_content_int(0, "isMuted");
	gData[playerid][StartYear] = cache_get_field_content_int(0, "StartYear");
	gData[playerid][StartMonth] = cache_get_field_content_int(0, "StartMonth");
	gData[playerid][StartDay] = cache_get_field_content_int(0, "StartDay");
	gData[playerid][TogglePM] = cache_get_field_content_int(0, "TogglePM");
	gData[playerid][ToggleG] = cache_get_field_content_int(0, "ToggleG");
	gData[playerid][knifeK] = cache_get_field_content_int(0, "knifeK");
	gData[playerid][chainsawK] = cache_get_field_content_int(0, "chainsawK");
	gData[playerid][colt45K] = cache_get_field_content_int(0, "colt45K");
	gData[playerid][silencedK] = cache_get_field_content_int(0, "silencedK");
	gData[playerid][deagleK] = cache_get_field_content_int(0, "deagleK");
	gData[playerid][shotgunK] = cache_get_field_content_int(0, "shotgunK");
	gData[playerid][sawedoffK] = cache_get_field_content_int(0, "sawedoffK");
	gData[playerid][spazK] = cache_get_field_content_int(0, "spazK");
	gData[playerid][uziK] = cache_get_field_content_int(0, "uziK");
	gData[playerid][mp5K] = cache_get_field_content_int(0, "mp5K");
	gData[playerid][ak47K] = cache_get_field_content_int(0, "ak47K");
	gData[playerid][m4K] = cache_get_field_content_int(0, "m4K");
	gData[playerid][tec9K] = cache_get_field_content_int(0, "tec9K");
	gData[playerid][rifleK] = cache_get_field_content_int(0, "rifleK");
	gData[playerid][sniperK] = cache_get_field_content_int(0, "sniperK");
	gData[playerid][dWins] = cache_get_field_content_int(0, "dWins");
	gData[playerid][dLosses] = cache_get_field_content_int(0, "dLosses");
	gData[playerid][silencedUnk] = cache_get_field_content_int(0, "silencedUnk");
	gData[playerid][colt45Unk] = cache_get_field_content_int(0, "colt45Unk");
	gData[playerid][deagleUnk] = cache_get_field_content_int(0, "deagleUnk");
	gData[playerid][shotgunUnk] = cache_get_field_content_int(0, "shotgunUnk");
	gData[playerid][spazUnk] = cache_get_field_content_int(0, "spazUnk");
	gData[playerid][sawedoffUnk] = cache_get_field_content_int(0, "sawedoffUnk");
	gData[playerid][uziUnk] = cache_get_field_content_int(0, "uziUnk");
	gData[playerid][mp5Unk] = cache_get_field_content_int(0, "mp5Unk");
	gData[playerid][ak47Unk] = cache_get_field_content_int(0, "ak47Unk");
	gData[playerid][m4Unk] = cache_get_field_content_int(0, "m4Unk");
	gData[playerid][rifleUnk] = cache_get_field_content_int(0, "rifleUnk");
	gData[playerid][sniperUnk] = cache_get_field_content_int(0, "sniperUnk");
	gData[playerid][parachuteUnk] = cache_get_field_content_int(0, "parachuteUnk");
	gData[playerid][tec9Unk] = cache_get_field_content_int(0, "tec9Unk");
	gData[playerid][armorUnk] = cache_get_field_content_int(0, "armorUnk");
	gData[playerid][dildoUnk] = cache_get_field_content_int(0, "dildoUnk");
	gData[playerid][flowerUnk] = cache_get_field_content_int(0, "flowerUnk");
	gData[playerid][caneUnk] = cache_get_field_content_int(0, "caneUnk");
	gData[playerid][brassUnk] = cache_get_field_content_int(0, "brassUnk");
	gData[playerid][clubUnk] = cache_get_field_content_int(0, "clubUnk");
	gData[playerid][nitestickUnk] = cache_get_field_content_int(0, "nitestickUnk");
	gData[playerid][knifeUnk] = cache_get_field_content_int(0, "knifeUnk");
	gData[playerid][batUnk] = cache_get_field_content_int(0, "batUnk");
	gData[playerid][shovelUnk] = cache_get_field_content_int(0, "shovelUnk");
	gData[playerid][poolstickUnk] = cache_get_field_content_int(0, "poolstickUnk");
	gData[playerid][katanaUnk] = cache_get_field_content_int(0, "katanaUnk");
	gData[playerid][chainsawUnk] = cache_get_field_content_int(0, "chainsawUnk");
	isLogged[playerid] = true;
}
forward OnAccountRegister(playerid);
public OnAccountRegister(playerid)
{
	gData[playerid][ID] = cache_insert_id();
	printf("New account registered. ID: %d", gData[playerid][ID]);
	isLogged[playerid] = true;
	return 1;
}
public OnDialogResponse(playerid, dialogid, response, listitem, inputtext[])
{
	switch(dialogid)
	{
		case dLogin:
		{
			if(response)
			{
				new hpass[129], query[100];
		        WP_Hash(hpass, 129, inputtext);
		        if(!strcmp(hpass, gData[playerid][Password]))
		        {
		        	mysql_format(mysql, query, sizeof(query), "SELECT * FROM `playerinfo` WHERE `Username` = '%e' LIMIT 1", Name[playerid]);
		        	mysql_tquery(mysql, query, "OnAccountLoad", "i", playerid);
		        }
		        else
		        {
		        	ShowPlayerDialog(playerid, dLogin, DIALOG_STYLE_INPUT, "Login", "Type your password.\nWrong password.", "Confirm", "Decline");
		        }
			}
			else Kick(playerid);	
		}
		case dRegister:
		{
			if(response)
			{
				if(strlen(inputtext) < 6) return ShowPlayerDialog(playerid, dRegister, DIALOG_STYLE_INPUT, "Register", "Type a password you can remember. \nAt least 6 chars long.", "Confirm", "Decline");
		    	new query[300];
		    	WP_Hash(gData[playerid][Password], 129, inputtext);
		    	new Year, Month, Day;
				getdate(Year, Month, Day);
		    	mysql_format(mysql, query, sizeof(query), "INSERT INTO `playerinfo` (`Username`, `Password`, `StartYear`, `StartMonth`, `StartDay`, `StartIP`) VALUES ('%e', '%s', %d, %d, %d, '%s')", Name[playerid], gData[playerid][Password], Year, Month, Day, IP[playerid]);
		    	mysql_tquery(mysql, query, "OnAccountRegister", "i", playerid);
			}
			else Kick(playerid);	
		}
}
Re: mysql - duplicated rows - cyanogen - 16.08.2014

Okay. Took me a while to realize the problem but it finally dawned on me. In onaccountregister I don't query the server to get the default values to set into the player enum. That means if a person registers they get the data that was in the slot on the server before them, so if it was an admin, then they get their alevel and other stuffs. this was a dangerous bug and I'm glad I caught it. hope this helps those who have similar problems. -

-Closed-