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
FULL QUERIES
full_query(your query here)
While the functions below allow the ability to Query, Insert and Update the database, they are quite limited if you want to create complex queries. For this reason WHMCS offers the full_query() function where you have the ability to provide any SQL query to be executed.
It is important to note that there is no sanitation or sanity checking of the data. You will need to sanitize the data before hand when using this function to avoid possible SQL Injections or other errors.
This function is generally overlooked and missed which is why I have added it as the first function on the list.
SELECT QUERIES
select_query($table,$fields,$where,$sort,$sortorder,$limits,$join)
A select query is run as follows, with anything after the $where variable being optional. The $table should be the table name, $fields a comma separated list of fields to select, the $where var should be an array of criteria, $sort can be a field name to order by, $sortorder either ASC or DESC, $limits a range to select eg “0,1” “10,20” etc… and finally $join can be used for performing an inner join with another table.