WHMCS Database Queries

WHMCS Database Queries

WHMCS use Laravel framework’s database component.
So Insert, Select, Update and Delete queries should be written in
Laravel style.

If you are planning to do the database query from a custom WHMCS page, add the below code
at the top of the page.

require_once("init.php");
use Illuminate\Database\Capsule\Manager as Capsule;

If you are going to do query from a module page, just the below code is enough.

use Illuminate\Database\Capsule\Manager as Capsule;

Select rows

In order to select all rows from the table tblinvoices,
we can use the below code.

//get all rows
try {
    $data = Capsule::table('tblinvoices')
        ->get();
    echo "<pre>"; print_r($data);
} catch(\Illuminate\Database\QueryException $ex){
    echo $ex->getMessage();
} catch (Exception $e) {
    echo $e->getMessage();
}

To select just paid invoices, we can use the below query.

//get all rows
try {
    $data = Capsule::table('tblinvoices')
        ->where("status", "=", "Paid") // we can add multiple where conditions
        ->get();
    echo "<pre>"; print_r($data);
} catch(\Illuminate\Database\QueryException $ex){
    echo $ex->getMessage();
} catch (Exception $e) {
    echo $e->getMessage();
}

Select a single row

To select a single row, we can use the function first instead of the function get as shown below.

//get a single row, note we added a where condition too
try {
    $data = Capsule::table('tblinvoices')
        ->where("id", "=", 3)
        ->first();
    echo "<pre>"; print_r($data);
} catch(\Illuminate\Database\QueryException $ex){
    echo $ex->getMessage();
} catch (Exception $e) {
    echo $e->getMessage();
}

Get value of a single field.

Assume that we just need to know paymentmethod of an invoice with id=3,
we can use the below query. Note that we used ->value(‘paymentmethod’) instead of first().

try {
    $data = Capsule::table('tblinvoices')
        ->where("id", "=", 3)
        ->value('paymentmethod');
    echo $data;
} catch(\Illuminate\Database\QueryException $ex){
    echo $ex->getMessage();
} catch (Exception $e) {
    echo $e->getMessage();
}

Add a row

Now let’s add a row to the table tblconfiguration.

//insert a row
try {
    //key value pair.
    $insert_array = [
        "setting" => "Discount",
        "value" => "60",
        "created_at" => date("Y-m-d H:i:s", time()),
        "updated_at" => date("Y-m-d H:i:s", time()),
    ];
    Capsule::table('tblconfiguration')
        ->insert($insert_array);
} catch(\Illuminate\Database\QueryException $ex){
    echo $ex->getMessage();
} catch (Exception $e) {
    echo $e->getMessage();
}

Add a row and get the last insert id.

To get the last insert id, we have to use insertGetId function instead of the insert.

//get insert id
try {
    $insert_array = [
        "date" => date("Y-m-d H:i:s", time()),
        "description" => "Testing insert id stuff",
        "user" => "test",
        "userid" => 1,
        "ipaddr" => "127.0.0.1"
    ];
    $new_log_id = Capsule::table('tblactivitylog')
        ->insertGetId($insert_array);
    echo $new_log_id;
} catch(\Illuminate\Database\QueryException $ex){
    echo $ex->getMessage();
} catch (Exception $e) {
    echo $e->getMessage();
}

Update a table

To update a table, we can write the query as given below.

//update a table
try {
    $update_data =  [
        'status' => 'Paid',
        'paymentmethod' => 'paypal'
    ];
    Capsule::table('tblinvoices')
       ->where('id', '=', 9)
       ->update($update_data);
} catch(\Illuminate\Database\QueryException $ex){
    echo $ex->getMessage();
} catch (Exception $e) {
    echo $e->getMessage();
}

Delete from a table

#delete from a table
try {
    Capsule::table('tblactivitylog')
        ->where('id', '=', 12)
        ->delete();
} catch(\Illuminate\Database\QueryException $ex){
    echo $ex->getMessage();
} catch (Exception $e) {
    echo $e->getMessage();
}

Sample Join Query

If we have to join two tables tblinvoices and tblinvoiceitems on a condition tblinvoices.id = tblinvoiceitems.invoiceid, then we can write the join query as written below. For any select query we can just select interested field’s values. In the below query we are just selecting three field values, they are tblinvoices.id, tblinvoiceitems.description, and tblinvoiceitems.amount.

    //Join query
try {
    $invoice_details = Capsule::table('tblinvoices')
         ->join(
             'tblinvoiceitems',
             'tblinvoices.id',
                  '=', 'tblinvoiceitems.invoiceid'
         )
         ->select(
               'tblinvoices.id',
               'tblinvoiceitems.description',
               'tblinvoiceitems.amount'
        )
        ->get();
        echo "<pre>"; print_r($invoice_details);
} catch(\Illuminate\Database\QueryException $ex){
    echo $ex->getMessage();
} catch (Exception $e) {
    echo $e->getMessage();
}

To know more about the query,
Go to Laravel Query Tutorial