Custom SQL Settings & RegEx Pattern Matching

The Custom SQL Settings/RegEx Pattern Matching feature allows you to replace specific terms or strings that may be present in your custom database.sql file or insert/update records in your provisioned application's database (such as site name, default user account properties, site URL, account username and password, etc.).

RegEx Patten Matching

FORMAT:
$regex = array(RegExValue => NewValue);

VARIABLES:
The following variables can be used to replace data with values generated in the module:

Quick Values

%tempurl% = Site Temporary URL
%customurl% = Custom URL as specified in your application site configuration settings.
%domainurl% = The domain assigned to the client.
%autourl% = The URL assigned to the application site (any of the above) as specified in your application site configuration settings.
%urlprefix% = The URL prefix assigned to the application site (e.g. either http:// or https://).
%serverip%
 =
The client's server IP address.
%firstname%
= The client's first name.
%lastname% = The client's last name.
%email% = The client's e-mail address.
%companyname% =
The client's company name.
%address1% =
The client's primary address line.
%address2% =
The client's secondary address line.
%city% =
The client's city.
%state% =
The client's state/region.
%postcode% =
The client's postcode.
%country% =
The client's country.
%phonenumber% =
The client's phone number.
%username% =
The client's username for the hosting server.
%password% =
The client's password for the hosting server.
%appuser% = 
The username for the application site being provisioned.
%apppw%
 =
The password for the application site being provisioned.
%dbname% = The name of the database used for the application.
%dbuser% = The name of the database user created for the application.
%dbpass% = The database password used for the application.
%tableprefix% = The database table prefix used for the application.

Be sure to enclose any of the quick values in single quotes when adding to the regex array string (e.g. '%firstname%').

System Values

$customproductfields["field_name"] = Replace field_name with the name of the custom product field you want to retrieve the value for.
$customclientfields["field_name"] = Replace field_name with the name of the custom client field you want to retrieve the value for.
$configurableoptions["group_name"]["option_name"]["item"] = The item name of the specified configurable option. Replace group_name and option_name with the specific group and corresponding option that you want to retrieve the value for.
$configurableoptions["group_name"]["option_name"]["qty"] = The quantity of the specified configurable option. Replace group_name and option_name with the specific group and corresponding option that you want to retrieve the value for. Note: Yes/No fields return a quantity value of 1 for "checked" and 0 for "unchecked".

EXAMPLE 1:
The default RegEx provided below will replace an existing temporary URL referenced in your database file with the temporary URL for the provisioned site:

$regex = array(
'/(\d{1,3}\.\d{1,3}\.\d{1,3}\.\d{1,3}\/~\b[a-zA-Z0-9_]+\b)/i'=>'%tempurl%',
);

So if your client's temporary URL was http://111.222.333.444/~youruser and the site you used to create the application site package from was using http://555.666.777.888/~originalsite (the value in the template database file), the above RegEx code would replace http://555.666.777.888/~originalsite with http://111.222.333.444/~youruser.

EXAMPLE 2:
The below example would replace any reference to the word WordPress (case-insensitive) to the term Awesome Site:

$regex = array(
'/WordPress/i'=>'Awesome Site',
);

EXAMPLE 3:
Multiple replacement terms or patterns can be added by simply creating a seperate array key and value for each one:

$regex = array(
'/FirstWord/i'=>'Cooler Phrase',
'/SecondWord/i'=>'Better Phrase',
);

For assistance with constructing a proper RegEx string, please visit http://regex101.com or contact us with any questions.


Custom SQL Updates


To make updates to the databae using MySQL strings (e.g. UPDATE, INSERT, DELETE, etc.), please follow these guidelines:

Update Existing Records

$table = $sitedb->get_table_prefix() . "table_to_update";
$values = array("[column_to_update]"=>"value_to_set", "[second_column_to_update]"=>"value_to_set");
$where = array("[some_column_value]"=>"equals_some_other_value");
$sitedb->update_query($table, $values, $where);

Insert New Records


$table = $sitedb->get_table_prefix() . "table_to_update";
$values = array("[first_column_to_update]"=>"value_to_set", "[second_column_to_update]"=>"value_to_set");
$sitedb->insert_query($table, $values);

Custom SQL Query


Use the following format to enter any custom SQL statement (e.g. delete records, insert new records, etc.)

$sitedb->full_query("DELETE FROM [table_name] WHERE id = 1");



Excluded Tables
(For the IBG App Installer - WP module only)

In the IBG App Installer - WP module, the following tables are excluded by default: _options, _usermeta, _users. Including these tables in the RegEx filtering process was causing site templates or user accounts to break in some instances. If you would like to include these tables in the RegEx search function, please add one of the following lines of code to the bottom of the sqlregex.php file:

To include the _options table, add this line:

$regex_options_table = 1;

To include the _usermeta and _users tables, add this line:

$regex_users_table = 1;
  • 0 Users Found This Useful
Was this answer helpful?

Related Articles

Install Database

This option is currently only available in our IBG App Installer - Generic module.Selecting this...

Display Control Panel Login

Enable this option to display the hosting server control panel login buttons on the client...

Alternate Admin E-Mail

If you would like to add an additional administrator for the application, enter an e-mail address...

Provision Sites Using Cron Jobs (Staging)

Added in WordPress Auto-Provision v3.0 and WHMCS Auto-Provision v1.2If you are running WHMCS 7.0...

Custom Domain Action

Use this option to select the action for the custom domain variable. If you have selected the...