WHMCS SQL Helper Functions select_query

Wondering how to access your WHMCS database while developing templates, custom pages or addon modules? well dont look any further. WHMCS  offers a full array of SQL Helper Functions to SELECT, INSERT and UPDATE information right form within WHMCS itself.

Do I have to create a database connection?

WHMCS maintains a connection to the database throughout each user interaction which enables you to leverage and utilize this same connection using the  provided SQL Helper Functions.

Lets dive in and take a look at what is available

WHMCS provides a set of SQL Helper Functions for database interactions within the WHMCS environment, ideal for developers creating templates, custom pages, or addon modules. These functions allow for effective SELECT, INSERT, and UPDATE operations directly within WHMCS.

Full Queries

full_query($query)

Execute any SQL query, particularly useful for complex queries beyond basic operations.

Parameters

• $query (string): The raw SQL query to be executed.

Usage

full_query("SELECT * FROM tblclients WHERE status = 'Active'");

Notes

• No data sanitation or sanity checking. Sanitize inputs beforehand to prevent SQL injections.
• Suitable for complex SQL operations not supported by other helper functions.

Select Queries

select_query($table, $fields, $where, $sort, $sortorder, $limits, $join)

Retrieve data from the database with parameters to refine the query.

Parameters

• $table (string): Name of the table.
• $fields (string/array): Fields to select, comma-separated or array.
• $where (array): Conditions for the query.
• $sort (string, optional): Field to sort by.
• $sortorder (string, optional): ‘ASC’ or ‘DESC’.
• $limits (string, optional): Record selection range, e.g., “0,10”.
• $join (string, optional): For inner join with another table.

Usage

select_query('tblclients', 'id,firstname,lastname', array('status' => 'Active'), 'id', 'ASC', '0,10');

Notes

• $where is an associative array with keys as column names.
• $sort, $sortorder, and $limits are optional for refining the query.

Insert Queries

insert_query($table, $data)`

Simplify the process of inserting data into the database.

Parameters

• $table (string): Table name for data insertion.
• $data (array): Associative array with column names as keys and data to insert as values.

Usage

insert_query('tblclients', array('firstname' => 'John', 'lastname' => 'Doe'));

Notes

• Keys in $data should correspond to column names.
• Data is automatically escaped for SQL injection protection.

Exceptions

• Exception if the table does not exist.
• Exception for key-column mismatch.