Revision as of 17:28, 28 July 2014

In all WHMCS templates, custom pages and addon modules, there will always be an active database connection established by WHMCS so you never need to connect to the WHMCS database again within your custom code - just use the already existing connection.

Information

You can use the common PHP/MySQL mysql_query() syntax to perform your SQL queries & updates with that connection, but there are also some helper functions defined by the core of WHMCS that you can make use of to have escaping of input automatically taken care of. Use of these functions will also show an error in the Activity Log should a SQL error occur and you have the appropriate option enabled in your General Settings.

Select Queries

Syntax: 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.

Examples:

$table = "tblclients";
$fields = "id,firstname,lastname";
$where = array("id"=>$userid);
$result = select_query($table,$fields,$where);
$data = mysql_fetch_array($result);
$id = $data['id'];
$firstname = $data['firstname'];
$lastname = $data['lastname'];

$table = "tblclients";
$fields = "id,firstname,lastname,domain";
$where = array(
    "lastname"=>array("sqltype"=>"LIKE","value"=>"Demo"),
    "companyname"=>array("sqltype"=>"NEQ","value"=>""),
);
$sort = "id";
$sortorder = "ASC";
$limits = "0,5";
$join = "tblhosting ON tblhosting.userid=tblclients.id";
$result = select_query($table,$fields,$where,$sort,$sortorder,$limits,$join);
while ($data = mysql_fetch_array($result)) {
    $id = $data['id'];
    $firstname = $data['firstname'];
    # Your code goes here...
}

Update Queries

Syntax: update_query($table,$update,$where)

An update query can be run as follows, accepting a table name, array of fields to update, and a where clause for the criteria.

Example:

$table = "tblclients";
$update = array("companyname"=>"Test2");
$where = array("firstname"=>"Test","companyname"=>"Test1");
update_query($table,$update,$where);

Insert Queries

Syntax: insert_query($table,$values)

Insert queries are the simplest of them all requiring just the table name and an array of fields to insert. This function call will return the ID of the newly created record.

$table = "tblclients";
$values = array("firstname"=>"x","lastname"=>"y","companyname"=>"z");
$newid = insert_query($table,$values);

Full Queries

Syntax: full_query(your query here)

This will run a full MySQL query with no sanitation or sanity checking.