STOP relying on email!

STOP relying on email!

Let WHMCS Notify Extended ensure that clients and staff get proper notifications of events!

Hackers are EVERYWHERE

Hackers are EVERYWHERE

Take control of your WHMCS installation with WHMCS  User and IP Control

WHMCS can be complicated

WHMCS can be complicated

Spend a few hours learning from a Guru! Training available

Don't get caught with your servers down!

Don't get caught with your servers down!

Let WHMCS Guru host your billing and support website, securely and remotely!

Importing from another billing client?

Importing from another billing client?

We can absolutely put together a script to take care of that for you

Need WHMCS Customized?

Need WHMCS Customized?

We’ve been developing addons, hooks and modules for WHMCS since 2007. Drop us a line

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!!