MySQL - Auto Remove InActive Accounts

MySQL - Auto Remove InActive Accounts - 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 - Auto Remove InActive Accounts (/showthread.php?tid=536057)

MySQL - Auto Remove InActive Accounts - SPA - 07.09.2014

Hello,

I'v many accounts in my server about 100K accounts, is there are any function which remove inactive accounts?

which removes auto inactive accounts?

Thanks.

Re: MySQL - Auto Remove InActive Accounts - BroZeus - 07.09.2014

Do u store a variable which shows when player was last connected?

Re: MySQL - Auto Remove InActive Accounts - SPA - 07.09.2014

Yes, there is but in Date form.

Re: MySQL - Auto Remove InActive Accounts - BroZeus - 07.09.2014

give the format of date..
like mm/dd/yyyy
or dd/mm/yyyy
and also give the delimiter by which date is separated like : or . or /

Re: MySQL - Auto Remove InActive Accounts - SPA - 07.09.2014

I didnt got you but i'v this code also:

Код:

CMD:settemplevel(playerid,params[])
{
	if(pInfo[playerid][Admin] > 5 || IsPlayerAdmin(playerid))
	{
	    new player1 = strval(params);
	    new level = strval(params);
	    new query[200];
	    new date,year,month;
	    if(sscanf(params,"uiiii",player1,level,date,month,year))return DUsage(playerid,"/settemplevel [playerid] [level] [day] [month] [year]");
		if(level < 0) return SendClientMessage(playerid,Red,"[ERROR]:- {FFFFFF}Invalid Level");
		if(level > 6) return SendClientMessage(playerid,Red,"[ERROR]:- {FFFFFF}Invalid Level");
		if(!IsPlayerConnected(player1))return SendClientMessage(playerid,Red,"[ERROR]:- {FFFFFF}Player Not Connected!");
		if(pInfo[player1][Admin] == level) return SendClientMessage(playerid,Red,"[ERROR]:- {FFFFFF}Player Already Has That Level");
		if(date == 0 || month == 0 || year == 0)
		{
		    if(level > 0) return DError(playerid,"Please Use ADMIN LEVEL as '0' As you have setted either the date or month as 0");
			else return DError(playerid,"Please Enter A Correct Date,Month Or Year!");
		}
		SendCommandToAdmins(playerid,"{FF0000}SETTEMPLEVEL");
		//new old_level = pInfo[player1][Admin];
		new string[256];
		format(string,sizeof(string),"{00FFFF}-> Administrator %s(%d) Has Set Your Admin Level [Level %d]",pName(playerid),playerid,level);
		SendClientMessage(player1,White,string);
		format(string,sizeof(string),"{00FFFF}-> You Have Set %s's(%d) Admin Level [Level %d]",pName(player1),player1,level);
		SendClientMessage(playerid,White,string);
		PlayerPlaySound(player1,1057,0.0,0.0,0.0);
		if(level > pInfo[player1][Admin])
		{
			GameTextForPlayer(player1,"~r~~h~Promoted!",7000,3);

		}
		if(level < pInfo[player1][Admin])
		{
			GameTextForPlayer(player1,"~r~~h~Demoted!",7000,3);
		}
		if(level == 0)
		{
	        mysql_format(Con_Handle, query, sizeof(query), "UPDATE Users SET Admin = 0,TempAdmin = 0,TempAdminDay = 0,TempAdminMonth = 0,TempAdminYear = 0 WHERE Name= '%e'",pName(player1));
	        mysql_tquery(Con_Handle, query);
		}
		pInfo[player1][Admin] = level;
		pInfo[player1][TempAdmin] = 1;
		pInfo[player1][TempAdmDay] = date;
		pInfo[player1][TempAdmMonth] = month;
		pInfo[player1][TempAdmYear] = year;
        mysql_format(Con_Handle, query, sizeof(query), "UPDATE Users SET Admin = %d,TempAdmin = 1,TempAdminDay = %d,TempAdminMonth = %d,TempAdminYear = %d WHERE Name= '%e'",level,date,month,year,pName(player1));
        mysql_tquery(Con_Handle, query);
		return 1;
	}
	else return LevelMSG(playerid,6);
}

And this

Код:

forward BanTheUser(playerid, name, reason);
public BanTheUser(playerid, name, reason)
{
		new query [256];
		new Date,Month,Year;
     	new Hour,Minute,Second;
     	getdate(Year,Month,Date);
     	gettime(Hour,Minute,Second);
    	if(cache_num_rows() == 0) return DError(playerid,"Player does not have an account on data base!");
		if(cache_num_rows() == 1)
		{
			mysql_format(Con_Handle, query, sizeof(query),"UPDATE `Users` SET Banned = 1, BannedReason = '%s',BannedByAdmin = '%s' WHERE Name = '%e'",reason,pName(playerid),name);
			mysql_tquery(Con_Handle, query);
		}
		new string[256];
		format(string,sizeof(string),"{FFFFFF}_____________Ban-User: Status: Succesfull_____________\nName: '%s'\nReason: '%s'\n{FF0000}BAN TYPE: Permanent\n{FFFFFF}TIME: %d:%d:%d\nDATE: %d/%d/%d",name,reason,Hour,Minute,Second,Date,Month,Year);
		ShowPlayerDialog(playerid,2522,DIALOG_STYLE_MSGBOX,"{FFFFFF}User Banned!",string,"Ok","");
		return 0;
}

This help?

Re: MySQL - Auto Remove InActive Accounts - BroZeus - 07.09.2014

Show me where u store player last connected date..
It should be in OnPlayerDiscconet

Re: MySQL - Auto Remove InActive Accounts - Inn0cent - 07.09.2014

BroZeus, Its separated in different columns, like, lastondate, lastonmonth, lastonyear.

Re: MySQL - Auto Remove InActive Accounts - Stanford - 07.09.2014

Select them then under a callback delete them from the table.

Re: MySQL - Auto Remove InActive Accounts - BroZeus - 07.09.2014

It is done in php so as to avoid lag while looping through all players in mysql and removing odl accounts
Based on what In0cent told this is the php script that will remove old accounts

Код HTML:

<?php
$dbservername = "127.0.0.1";//change
$dbusername = "root";//change
$dbpassword = "";//change
$dbname = "DB_Name";//change

$con = mysql_connect($dbservername, $dbusername, $dbpassword);

if(!con)exit();
$now = strtotime("now");

mysql_select_db($dbname, $con);

$query = "SELECT * FROM players"; //------here name of table

$result = mysql_query($query) or die(mysql_error());

while($row = mysql_fetch_array($result))
{
switch($row["lastonmonth"])//---------here the name  column which stores last month
{
case 1: 
$mon = "January";
break;
case 2:
$mon = "February";
break;
case 3:
$mon = "March";
break;
case 4:
$mon = "April";
break;
case 5:
$mon = "May";
break;
case 6:
$mon = "June";
break;
case 7:
$mon = "July";
break;
case 8:
$mon = "August";
break;
case 9:
$mon = "September";
break;
case 10:
$mon = "October";
break;
case 11:
$mon = "November";
break;
case 12:
$mon = "December";
break;
}
$ld = $row["lastondate"];//---------here the name  column which stores last date
$ly = $row["lastonyear"];//---------here the name  column which stores last year
$last = strtotime("$ld $mon $ly");
$dif = $now - $last;
if($dif < (60 * 60 * 24 * 30 * 4))continue;//4 months
$nn = $row["username"];//-----------------------------------here the name of column storing player name
mysql_query("DELETE  players WHERE username='$nn'");//--------here name of table and also the column name which stores player name
}
mysql_close($con);
?>

*The code removes accounts that have been not used in last 4 monts || if u want to change duration then go to line in which i have commented "//4 monts" and in that line change 4 to whatever month u want
I have commented in front of lines which needs changes in them like table name change , column name change , you need to change them according to your table.

To auto remove all of the old acconts this script needs to be run every 12 hours
Well u wont run it by urself after every 12 hours

So to run this script every 12 hours by it self, settings need to be made in the host on which the script is hosted
An example of showing how to auto run php script after every specified time is in this video --
https://www.youtube.com/watch?v=l5Wa8sgbo2Y

In the following video it is shown how to run a file named WP-Mail.php after a specified interval of time..

If the website host u use doesn't provide this facility then u need to find which does.

Re: MySQL - Auto Remove InActive Accounts - SPA - 07.09.2014

Quote:

Originally Posted by BroZeus

It is done in php so as to avoid lag while looping through all players in mysql and removing odl accounts
Based on what In0cent told this is the php script that will remove old accounts

Код HTML:

<?php
$dbservername = "127.0.0.1";//change
$dbusername = "root";//change
$dbpassword = "";//change
$dbname = "DB_Name";//change

$con = mysql_connect($dbservername, $dbusername, $dbpassword);

if(!con)exit();
$now = strtotime("now");

mysql_select_db($dbname, $con);

$query = "SELECT * FROM players"; //------here name of table

$result = mysql_query($query) or die(mysql_error());

while($row = mysql_fetch_array($result))
{
switch($row["lastonmonth"])//---------here the name  column which stores last month
{
case 1: 
$mon = "January";
break;
case 2:
$mon = "February";
break;
case 3:
$mon = "March";
break;
case 4:
$mon = "April";
break;
case 5:
$mon = "May";
break;
case 6:
$mon = "June";
break;
case 7:
$mon = "July";
break;
case 8:
$mon = "August";
break;
case 9:
$mon = "September";
break;
case 10:
$mon = "October";
break;
case 11:
$mon = "November";
break;
case 12:
$mon = "December";
break;
}
$ld = $row["lastondate"];//---------here the name  column which stores last date
$ly = $row["lastonyear"];//---------here the name  column which stores last year
$last = strtotime("$ld $mon $ly");
$dif = $now - $last;
if($dif < (60 * 60 * 24 * 30 * 4))continue;//4 months
$nn = $row["username"];//-----------------------------------here the name of column storing player name
mysql_query("DELETE  players WHERE username='$nn'");//--------here name of table and also the column name which stores player name
}
mysql_close($con);
?>

Wow amazing!, waiting for Inn0cent

+rep !