View source for Interacting With The Database

=Database connectivity changes in WHMCS 6.0=

The mysql PHP extension was deprecated when PHP 5.5 was released in June 2013<ref>http://php.net/ChangeLog-5.php#5.5.0</ref> and is scheduled to be removed in PHP 7<ref>https://wiki.php.net/rfc/remove_deprecated_functionality_in_php7</ref>. WHMCS 6.0 introduces a new database connection and library to ensure compatibility with modern PHP environments and best practices.

==New functionality==

WHMCS 6.0 incorporates the Laravel framework 4.1's database component (''WHMCS 7.0 incorporates 5.2''). This library includes a Database Abstraction Layer (DBAL) called "Capsule" and an Object Relational Mapping (ORM) library called "Eloquent". The new DBAL is based on the PHP Data Objects (PDO MySQL) extension and uses WHMCS's existing <tt>configuration.php</tt> file. No configuration file changes are required to use the new database connection.

The Capsule DBAL component introduces two libraries to WHMCS, a query manager for running database queries and a schema manager for an abstracted API to table management. Capsule's underlying PDO connection is also available for advanced database usage. Capsule has three static methods to get to these components:

* '''Capsule::table(string $tableName)''': Access the query manager for the given table.
* '''Capsule::schema()''': Access the schema manager for the WHMCS database. 
* '''Capsule::connection()''': Access the connection manager to interact with the underlying database connection.

'''Note:''' WHMCS 6.0 makes two connections to the database. One connection is made through the legacy mysql extension to handle existing hooks, modules, and other customizations. The other connection is made by PDO to drive new DBAL and model based functionality in the program. 

==Deprecated functionality==

The current [[SQL Helper Functions]] are present in WHMCS 6.0 and above, but are now deprecated and may be removed in a later version of the product:
* '''select_query()'''
* '''update_query()'''
* '''insert_query()'''
* '''full_query()'''

The mysql extension driven database connection is now deprecated and may be discontinued in a later version of the product. WHMCS encourages all third party developers to use the Capsule DBAL and PDO connection for all new database interaction.

'''Note:''' WHMCS 7.0 incorporates the Laravel framework's 5.2 database component, which deprecates as well as removes many features present in 4.1  Please see the upgrade guide for more information:
* [https://laravel.com/docs/5.2/upgrade- Laravel 5.2 Upgrade Guide]

=Using Capsule=

Declare an alias to Laravel's database manager in your project file's <tt>use</tt> block to access Capsule:

<syntaxhighlight lang="php">
<?php

use Illuminate\Database\Capsule\Manager as Capsule;

// Run queries or modify tables as you like.
</syntaxhighlight>

==The Query Manager==

: ''Please see [https://laravel.com/docs/5.2/queries Laravel's query documentation] for more information.''

The '''Capsule::table(string $tableName)''' method provides access to the query manager. Declare it with the name of the table you wish to query as it's first parameter to interact with that table. The query manager has a wide range of functionality to perform advanced select, join, insert, update, and delete statements. Capsule's select calls return rows as ''stdClass'' objects.

Capsule escapes all input, so it is not necessary to add escaping slashes to variables passed to these methods.

All of Capsule's methods throw an exception on failure. Please place Capusle calls in <tt>try</tt>/<tt>catch</tt> blocks for graceful error handling and to avoid potential fatal errors in your hook, module, or other customization.

<syntaxhighlight lang="php">
<?php

use Illuminate\Database\Capsule\Manager as Capsule;

// Print all client first names using a simple select.

/** @var stdClass $client */
foreach (Capsule::table('tblclients')->get() as $client) {
    echo $client->firstname . PHP_EOL;
}

// Rename all clients named "John Deo" to "John Doe" using an update statement. 
try {
    $updatedUserCount = Capsule::table('tblclients')
        ->where('firstname', 'John')
        ->where('lastname', 'Deo')
        ->update(
            [
                'lastname' => 'Doe',
            ]
        );

    echo "Fixed {$updatedUserCount} misspelled last names.";
} catch (\Exception $e) {
    echo "I couldn't update client names. {$e->getMessage()}";
}
</syntaxhighlight>

==The Schema Manager==

Use the '''Capsule::schema()''' method to access the schema manager to modify table schema if necessary. The schema manager has support for creating, dropping and truncating tables and for modifying columns, indexes, and keys. 

'''Note''': WHMCS does not recommend changing default table schema as that can affect product functionality.

<syntaxhighlight lang="php">
<?php

use Illuminate\Database\Capsule\Manager as Capsule;

// Create a new table.
try {
    Capsule::schema()->create(
        'my_table',
        function ($table) {
            /** @var \Illuminate\Database\Schema\Blueprint $table */
            $table->increments('id');
            $table->string('name');
            $table->integer('serial_number');
            $table->boolean('is_required');
            $table->timestamps();
        }
    );
} catch (\Exception $e) {
    echo "Unable to create my_table: {$e->getMessage()}";
}
</syntaxhighlight>

==The Connection Manager==

The '''Capsule::connection()''' method provides low-level access to the database connection itself. Use it to initiate transactions with automatic commit and rollback or to access the underlying PDO connection to perform manual database queries outside the DBAL. The connection manager also has methods to retrieve query and schema managers.

<syntaxhighlight lang="php">
<?php

use Illuminate\Database\Capsule\Manager as Capsule;

// Perform potentially risky queries in a transaction for easy rollback.
try {
    Capsule::connection()->transaction(
        function ($connectionManager)
        {
            /** @var \Illuminate\Database\Connection $connectionManager */
            $connectionManager->table('my_table')->insert(
                [
                    'name' => $_POST['name'],
                    'serial_number' => $_POST['serialNumber'],
                    'is_required' => (int)(bool) $_POST['isRequired'],
                ]
            );
        }
    );
} catch (\Exception $e) {
    echo "Uh oh! Inserting didn't work, but I was able to rollback. {$e->getMessage()}";
}
</syntaxhighlight>

===Getting to PDO===

Use the connection manager's '''getPdo()''' method to retrieve the underlying PDO connection instance. Use the PDO connection to perform manual queries and advanced database usage.

<syntaxhighlight lang="php">
<?php

use Illuminate\Database\Capsule\Manager as Capsule;

// Perform potentially risky queries in a transaction for easy rollback.    
$pdo = Capsule::connection()->getPdo();
$pdo->beginTransaction();

try {
    $statement = $pdo->prepare(
        'insert into my_table (name, serial_number, is_required) values (:name, :serialNumber, :isRequired)'
    );

    $statement->execute(
        [
            ':name' => $_POST['name'],
            ':serialNumber' => $_POST['serialNumber'],
            ':isRequired' => (bool) $_POST['isRequired'],
        ]
    );

    $pdo->commit();
} catch (\Exception $e) {
    echo "Uh oh! {$e->getMessage()}";
    $pdo->rollBack();
}
</syntaxhighlight>

=Troubleshooting=

==Exceptions==

All Capsule methods throw an exception on failure. Catch these exceptions and analyze their messages and stack traces to help determine the nature of the failure. WHMCS recommends placing all database interactivity in <tt>try</tt>/<tt>catch</tt> blocks for graceful error handling.

==The Capsule Query Log==

The connection manager's '''getQueryLog()''' method returns an array of all queries made during the life of the page request. Queries are stored in the log as an array containing the query run, the parameter bindings passed to the query, and the time it took for the query to execute, measured in milliseconds.

<syntaxhighlight lang="php">
<?php

use Illuminate\Database\Capsule\Manager as Capsule;

// Loop through each Capsule query made during the page request.
foreach (Capsule::connection()->getQueryLog() as $query) {
    echo "Query: {$query['query']}" . PHP_EOL;
    echo "Execution Time: {$query['time']}ms" . PHP_EOL;
    echo "Parameters: " . PHP_EOL;

    foreach ($query['bindings'] as $key => $value) {
        echo "{$key} => {$value}" . PHP_EOL;
    }
}
</syntaxhighlight>

==The WHMCS Activity Log==

All uncaught PDO-based query failures, including those made by Capsule and manual PDO queries, are written to to the WHMCS activity log. View the system activity log to view the details of these failed queries.

=See Also=
* [[SQL Helper Functions]] (functional, but deprecated in WHMCS 6.0)

=External Links=
* [http://laravel.com/docs/4.2/queries Query Builder - Laravel 4.2]
* [http://laravel.com/docs/5.2/queries Query Builder - Laravel 5.2]
* [http://laravel.com/docs/4.2/schema Schema Builder - Laravel 4.2]
* [https://laravel.com/docs/5.0/schema - Schema Builder 5.0]
* [http://php.net/manual/en/book.pdo.php PHP: PDO]

=References=
<references />
No results found.