WHMCS Database tips and tricks – The queries

So, we’ve covered the basics. We’ve covered the parts of the schema. Now, it’s time to go through the database itself, the queries that you’ll likely use on a day to day basis.

We’ll continue working on our ‘mynewaddon’ addon. This won’t really show much, but at the end of the tips and tricks guide, you’ll have a functioning addon that will serve as an example of how addons should be created, and what they can do from there.

Using our example addon again, we’ll assume that we’re working with the database ‘mynewaddon’. So, let’s start by processing the output. Again, working with the example in WHMCS documentation . This function will show the output to the admin user… Again, we’ll want to make sure we’ve initialized Capsule

use Illuminate\Database\Capsule\Manager as Capsule;

function mynewaddon_output($vars) {

$modulelink = $vars['modulelink'];
}

It’s pretty basic, so let’s spice it up a bit. Adding the DB from the previous connection, let’s use it to make a query.

$users = Capsule::table('mynewaddon')->select('myid', 'uniquecolumn')->get();
foreach ($users as $data)
{
 $myid = $data->myid;
 $uniquecolumn = $data->uniquecolumn;
 //do something with myid and unique column
}

So, now we have our admin output display. It’s not going to do much, but it’s there.. Let’s say though that we only wanted certain things from the table. I mean, we could end up with 1000s of rows, right? So, let’s add a ‘where’ statement to this mix

$users = Capsule::table('mynewaddon')->select('myid', 'uniquecolumn')-> where('myid' ">=" '100')->get();
foreach ($users as $data)
{
 $myid = $data->myid;
 $uniquecolumn = $data->uniquecolumn;
 //do something with myid and unique column
}

Now, we only get info if the user has a myid of 100 or greater.

But , what if we wanted to limit that a bit. Say, we only want users between 100 and 200? Easy!

$users = Capsule::table('mynewaddon')->select('myid', 'uniquecolumn')->whereBetween('votes', array(100, 200))->get();
foreach ($users as $data)
{
 $myid = $data->myid;
 $uniquecolumn = $data->uniquecolumn;
 //do something with myid and unique column
}

See, I told you it was easy!
Up next? A few more query examples for you