WHMCS Database tips and tricks & further queries

So, we’ve covered the basics. We’ve covered the parts of the schema and some of the day to day queries. Now, let’s take a look at some more day to day queries, operations you’ll likely want to run on a daily basis. Deletes, updates, grabbing the number of rows, even some other small laravel magic.

As always, make sure you’ve initialized Capsule

use Illuminate\Database\Capsule\Manager as Capsule;

Let’s start with determining just how many users we have in our database, ok?

$r2 = Capsule::table('tblclients') ->select('id')->count();

Now, $r2 will give you the total count of users. Pretty cool, huh?

But, again, what if we just wanted those named Tom? I hear he’s a pretty good guy and all

$r2 = Capsule::table('tblclients') ->select('id')->WHERE('firstname', '=', 'Tom')->count();

See? Easy

What if we wanted to change Tom’s name? I don’t know why we would, but hey, let’s assume we do

$r2=Capsule::table('tblclients')->where('firstname', 'Tom')->update(array('firstname' => 'Tommy'));

Again, quite easy to do.

Now, what if we want to delete a client. Usually, we’d use the WHMCS admin area, but let’s say we don’t want to (again, you probably DO want to use the admin delete functionality).

Capsule::table('tblclients')->where('firstname', '=','Tommy')->delete();

Now, let’s say we want to re-add Tommy back in. Again, it’s usually best to handle this via the client interface, but for this example we won’t.

$query = Capsule::table('tblclients')->>(array('firstname' => 'Tommy', 'lastname' => 'Tutone'));

So, this will wrap up this quick guide on working with the WHMCS database. Hopefully you’ve learned a few things about the process here, and passed it on to your friends. Please do take a moment and share these tips and tricks!!