WHMCS 6 Database Queries

5 Dec

WHMCS 6 Database Queries

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

To perform the query, we should include the below code to the php file. You should replace the string path_to_init_folder with actual path to the init.php file within the WHMCS folder.

require "path_to_init_folder/init.php";
use Illuminate\Database\Capsule\Manager as Capsule;

Now let’s see how we can add a row to a table named my_sample_table.
We have two fields to insert. one is key and other one is value.

#Insert a row

$insert_array = array(
      "name" => $_REQUEST['name'],
      "relid" => $_REQUEST['productid']
);

Capsule::table('my_sample_table')
    ->insert(
         $insert_array
);

/*
  If you would like to get unique id of
  last inserted row
*/

$insert_array = array(
   "name" => $_REQUEST['name'],
   "relid" => $_REQUEST['productid']
);
$row_id = Capsule::table('my_sample_table')
    ->insertGetId(
        $insert_array
);

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

#Update a row
Capsule::table('tblinvoices')
   ->where('id', '=', $invoice_id)
   ->update(array('status' => 'Paid'));
#Select multiple rows
$data = Capsule::table('tbldomains')
   ->where('domain', '=', 'mydomain.com')
   ->get();
#Select a row
$data = Capsule::table('tbldomains')
   ->where('domain', '=', 'mydomain.com')
   ->first();
#delete  row
Capsule::table('my_sample_table')
    ->where('id', '=', 22)
    ->delete();

Sample Join Query

If we have to join two tables discount_system and tblproductgroups on a condition tblproductgroups.id = discount_system.pg_id, then we can write the join query as written below. For any select query we can just select intrested field’s values. In the below query we are just selecting three field values, they are tblproductgroups.name, discount_system.discount_type and discount_system.id.

    $discount_details = Capsule::table('discount_system')
         ->join(
             'tblproductgroups',
             'tblproductgroups.id', 
                  '=', 'discount_system.pg_id'
         )
         ->select(                             
               'tblproductgroups.name',
               'discount_system.discount_type',
               'discount_system.id'
        )
        ->get();

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

Leave a Reply

Your email address will not be published. Required fields are marked *