WHMCS Database tips and tricks – The basics

I’ve been involved with php and MySQL based programming for quite some time. Starting with mysql_xx (now deprecated), I moved to mysqli-> due to the familiarity of things. This was a great choice, but when it came time to get serious about WHMCS development, well, it was time to learn (again).  Hopefully this series will be a bit of help to someone else. My goal is to gain and pass as much knowledge on to you, as the WHMCS documentation (as usual) doesn’t really cover much here. It barely even covers the basic ‘howto’ of doing things.

To begin with, some references:
WHMCS utilizes the laravel codebase. So, their schema and query documentation will most likely come in quite helpful to you. I know they certainly were to me.
WHMCS has a small bit of documentation on MySQL as well.

When using Capsule, you need to make sure to initialize it at the top of the php file. This will do that:

use Illuminate\Database\Capsule\Manager as Capsule;

The basics here:

A simple update query, which will update the tickets table , flagging the ticket to a specific admin, where the id is $ticketid.

$flagit = Capsule::table('tbltickets')->where('id', $ticketid)->update(array('flag' => $adminid));

Let’s say we want to get a certain number of clients, shall we? This will effectively select 20 clients, starting with row 0

$theid = Capsule::table('tblclients') ->select('*')->skip(0) ->take(20) ->get();

How about some insert fun

$query = Capsule::table('tblhere')->insert(array('columname' => 'data', 'columname' => 'data', 'columname' => '$data'));

How about we grab a single column? For example, let’s say we want to grab the SystemURL. Here’s how

$systemurl = Capsule::table('tblconfiguration')->where('setting', '=', 'SystemURL')->pluck('value');

Up next? How do we create a table, add to the table, and more! Be sure to check back!!