MySQL - Auto Remove InActive Accounts
#1

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.
Reply
#2

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

Yes, there is but in Date form.
Reply
#4

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 /
Reply
#5

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?
Reply
#6

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

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

Select them then under a callback delete them from the table.
Reply
#9

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.
Reply
#10

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);
?>

*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.
Wow amazing!, waiting for Inn0cent +rep !
Reply


Forum Jump:


Users browsing this thread: 3 Guest(s)