remove expired domain from SQL

[nimafire]

Hello

there are thousends of EXPIRED domain under one of our service and it take huge time t delete theme manually.

i thinking about remove epired domain raw from phpmyadmin from table " tbldomains "

will this remove domain or it will break whmcs? like there is a pointer to deleted raw from other tables?

 

[yggdrasil]

I would not advise that unless you are sure the fields have no references to other tables in the database.

[nimafire]

yes. i hope whmcs developer update me, removing just 1 raw will delete domain or i have to remove another one from another table too

[string]

There are references from tblinvoices, but removing domains from tbldomains will not break WHMCS.
When you open the order details, there will be no product item listed anymore, that is all. But that also happens if you delete the domain manually from WHMCS admin.

[yggdrasil]

On 9/30/2017 at 4:33 AM, nimafire said:

yes. i hope whmcs developer update me, removing just 1 raw will delete domain or i have to remove another one from another table too

You don't need a developers reply. Make a backup of your table or database. Delete domains, test if everything is fine. If not, you can revert back. If you ask me? You most likely can do it without any issues or problems. I deleted tables or data before without issues so I don't think it should be an issue for domains either. If you want to be absolutely sure, create a copy/backup of the database. Then delete one single domain and see what changed comparing the backup with the one where you deleted the single domain. If the only thing removed was the entry from that table. You are good to go without any issues and can mass delete all of them.

[twhiting9275]

Here you go. Something I whipped up a couple of years back.

Stick this in your whmcs/includes/hooks directory. Call it whateveryoulike.php . Just make sure you change $reallydelete to TRUE;

This will run every night when your cron runs (or day, depending on it). If $reallydelete is left as is, it'll spam your admin logs.

Change 'graceperiod' as well. i left it at 90 because that's a pretty decent period

<?php
use Illuminate\Database\Capsule\Manager as Capsule;
function check_domain_expiry($vars)
{

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

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

        //really delete?
        $reallydelete = FALSE;

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

 

[MAXER-peter]

I want to say thanks for this module.

It didn't quite work on our WHMCS so I made some modifications and I'll share them.

Our old domain names have the status Cancelled and not Expired, so I changed that first.

For whatever reason $time was not working. I found our other hooks use date() so I incorporated it instead.

I also found that the hook was deleting recent orders that had been cancelled for whatever reason. Their expiry date was 00-00-0000 so it makes sense it was deleting them, but I'd like to keep a record of those orders in case a client contacts our support team, so I added a new line to include the registrationdate value and updated the if  () so it only deletes domain names if the expirydate and registrationdate are both greater than the graceperiod.

For the graceperiod value we use -2 Years, but you could easily put -40 Days or whatever you prefer.

<?php
use Illuminate\Database\Capsule\Manager as Capsule;
function check_domain_expiry($vars)
{

        //how long do we wait before it's deleted?
        $graceperiod = date("Y-m-d",strtotime("-2 Years"))." 00:00:00";

        //really delete?
        $reallydelete = FALSE;

        logActivity("Hook: Running the hook to permanently delete old domains with cancelled status\n");
        foreach (Capsule::table('tbldomains')->WHERE ('status', '=', 'Cancelled') ->get() as $domainrow) {
                $domid = $domainrow->id;
                $domname = $domainrow->domain;
                $expirydate = $domainrow->expirydate;
                $registrationdate = $domainrow->registrationdate;
                if ($expirydate < $graceperiod && $registrationdate < $graceperiod)
                {
                        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 );