SQL Helper Functions

These Functions are Deprecated
Use Interacting_With_The_Database to run any custom queries.

In all WHMCS templates, custom pages and addon modules, there will always be an active database connection. There will never be a need to connect to the WHMCS database again within custom code. All developers should use the existing connection.

Information

Use common PHP/MySQL 'mysql_query()' syntax to perform custom SQL queries with the WHMCS connection. There are also helper functions defined in WHMCS. Use of these functions will escape the input passed so a developer does not need to. The functions will also show an Activity Log error should a SQL Error occur when enabled in 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...
}

When joining two or more tables containing a field as part of the WHERE clause, it is necessary to use the table prefix for it to work as expected. This is in line with standard MySQL syntax.

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.

Documentation