Delete expired domains

[ServWise.com]

I've got about 1500 expired domains accumulated from many years of service which I don't want to manually delete.

 

No way to bulk delete them so I am thinking to write my own SQL script to do it.

 

This is what I have so far, anyone want to sanity check it for me. Yes I know it is a select not a delete statement, I will change to a delete statement once I have it fullproof

 

 

It should delete all domain records that have status expired and have been expired for more than 3 months.

 

SELECT * FROM tbldomains td LEFT JOIN tbldomainsadditionalfields tdaf ON td.id=tdaf.domainid WHERE td.status="expired" AND DATE(expirydate) < DATE_ADD(CURDATE(), INTERVAL -3 MONTH);

Should convert to this as a delete statement

DELETE tbldomains td, tbldomainsadditionalfields tdaf FROM td LEFT JOIN tbldomainsadditionalfields tdaf ON td.id=tdaf.domainid WHERE td.status="expired" AND DATE(expirydate) < DATE_ADD(CURDATE(), INTERVAL -3 MONTH);

 

I just want to know that I am not missing something obvious, is there other linked data? is my SQL going to delete everything I need to delete an no more?

[antoniojeldres]

Hi,

 

Did it work for you?

 

I'm stuck with thousands of pending and expired domains names and like always WHMCS sucks in providing the help we need.

[twhiting9275]

Why use raw SQL? Just use a hook to get this straightened out on your cron runs? Save as something.php in includes/hooks/

<?php
//delete expired domains on cron
//provided by https://www.whmcs.guru
//version 1.0.0
use Illuminate\Database\Capsule\Manager as Capsule;
function check_domain_expiry($vars)
{

//how long do we wait before it's deleted?
$howlong = "90";

//set a base time
$time = time();

//really delete?
$reallydelete = TRUE;

if (!empty($graceperiod))
{
	//get the corrected date
	$graceperiod = $graceperiod  * 24 * 60 * 60;
	$todelete = $time - $graceperiod;
}
logActivity("Checking domains\n");
foreach (Capsule::table('tbldomains')->WHERE ('status', '=', 'Expired') ->get() as $domainrow) {
	$domid = $domainrow->id;
	$domname = $domainrow->domain;
	$expirydate = $domainrow->expirydate;
	if ($expirydate > $todelete)
	{
		if (!$reallydelete)
		{
			logActivity("I would delete $domname [$expirydate]\n");
		}
		if ($reallydelete)
		{
			Capsule::table('tbldomains')->where('id', '=', $domid)->delete();
			logActivity("Cron deleted $domname [$expirydate]\n");
		}
	}
}

logActivity ("Done checking domains\n");

}



//add the cron to run daily
add_hook('DailyCronJob', 1, check_domain_expiry );

 

This will check automatically every day if your domain is over 90 days expired and if so, delete it. Of course, it's going to log as well.

Change $reallytrue to FALSE to just get a log output

[pRieStaKos]

Why use raw SQL? Just use a hook to get this straightened out on your cron runs? Save as something.php in includes/hooks/

This will check automatically every day if your domain is over 90 days expired and if so, delete it. Of course, it's going to log as well.

Change $reallytrue to FALSE to just get a log output

 

I've done the same, but I transfer the domains table data to a backup table, for client/domain history & future company reports.