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();
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).
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!!