+ +
+

Database Utility Class

+

The Database Utility Class contains methods that help you manage your +database.

+ +
+

Initializing the Utility Class

+
+

Important

+

In order to initialize the Utility class, your database +driver must already be running, since the utilities class relies on it.

+
+

Load the Utility Class as follows:

+
$this->load->dbutil();
+
+
+

You can also pass another database object to the DB Utility loader, in case +the database you want to manage isn’t the default one:

+
$this->myutil = $this->load->dbutil($this->other_db, TRUE);
+
+
+

In the above example, we’re passing a custom database object as the first +parameter and then tell it to return the dbutil object, instead of +assigning it directly to $this->dbutil.

+
+

Note

+

Both of the parameters can be used individually, just pass an empty +value as the first one if you wish to skip it.

+
+

Once initialized you will access the methods using the $this->dbutil +object:

+
$this->dbutil->some_method();
+
+
+
+
+

Using the Database Utilities

+
+

Retrieve list of database names

+

Returns an array of database names:

+
$dbs = $this->dbutil->list_databases();
+
+foreach ($dbs as $db)
+{
+        echo $db;
+}
+
+
+
+
+

Determine If a Database Exists

+

Sometimes it’s helpful to know whether a particular database exists. +Returns a boolean TRUE/FALSE. Usage example:

+
if ($this->dbutil->database_exists('database_name'))
+{
+        // some code...
+}
+
+
+
+

Note

+

Replace database_name with the name of the database you are +looking for. This method is case sensitive.

+
+
+
+

Optimize a Table

+

Permits you to optimize a table using the table name specified in the +first parameter. Returns TRUE/FALSE based on success or failure:

+
if ($this->dbutil->optimize_table('table_name'))
+{
+        echo 'Success!';
+}
+
+
+
+

Note

+

Not all database platforms support table optimization. It is +mostly for use with MySQL.

+
+
+
+

Repair a Table

+

Permits you to repair a table using the table name specified in the +first parameter. Returns TRUE/FALSE based on success or failure:

+
if ($this->dbutil->repair_table('table_name'))
+{
+        echo 'Success!';
+}
+
+
+
+

Note

+

Not all database platforms support table repairs.

+
+
+
+

Optimize a Database

+

Permits you to optimize the database your DB class is currently +connected to. Returns an array containing the DB status messages or +FALSE on failure.

+
$result = $this->dbutil->optimize_database();
+
+if ($result !== FALSE)
+{
+        print_r($result);
+}
+
+
+
+

Note

+

Not all database platforms support database optimization. It +it is mostly for use with MySQL.

+
+
+
+

Export a Query Result as a CSV File

+

Permits you to generate a CSV file from a query result. The first +parameter of the method must contain the result object from your +query. Example:

+
$this->load->dbutil();
+
+$query = $this->db->query("SELECT * FROM mytable");
+
+echo $this->dbutil->csv_from_result($query);
+
+
+

The second, third, and fourth parameters allow you to set the delimiter +newline, and enclosure characters respectively. By default commas are +used as the delimiter, “n” is used as a new line, and a double-quote +is used as the enclosure. Example:

+
$delimiter = ",";
+$newline = "\r\n";
+$enclosure = '"';
+
+echo $this->dbutil->csv_from_result($query, $delimiter, $newline, $enclosure);
+
+
+
+

Important

+

This method will NOT write the CSV file for you. It +simply creates the CSV layout. If you need to write the file +use the File Helper.

+
+
+
+

Export a Query Result as an XML Document

+

Permits you to generate an XML file from a query result. The first +parameter expects a query result object, the second may contain an +optional array of config parameters. Example:

+
$this->load->dbutil();
+
+$query = $this->db->query("SELECT * FROM mytable");
+
+$config = array (
+        'root'          => 'root',
+        'element'       => 'element',
+        'newline'       => "\n",
+        'tab'           => "\t"
+);
+
+echo $this->dbutil->xml_from_result($query, $config);
+
+
+
+

Important

+

This method will NOT write the XML file for you. It +simply creates the XML layout. If you need to write the file +use the File Helper.

+
+
+
+
+

Backup Your Database

+
+

Database Backup Notes

+

Permits you to backup your full database or individual tables. The +backup data can be compressed in either Zip or Gzip format.

+
+

Note

+

This feature is only available for MySQL and Interbase/Firebird databases.

+
+
+

Note

+

For Interbase/Firebird databases, the backup file name is the only parameter.

+

$this->dbutil->backup(‘db_backup_filename’);

+
+
+

Note

+

Due to the limited execution time and memory available to PHP, +backing up very large databases may not be possible. If your database is +very large you might need to backup directly from your SQL server via +the command line, or have your server admin do it for you if you do not +have root privileges.

+
+
+
+

Usage Example

+
// Load the DB utility class
+$this->load->dbutil();
+
+// Backup your entire database and assign it to a variable
+$backup = $this->dbutil->backup();
+
+// Load the file helper and write the file to your server
+$this->load->helper('file');
+write_file('/path/to/mybackup.gz', $backup);
+
+// Load the download helper and send the file to your desktop
+$this->load->helper('download');
+force_download('mybackup.gz', $backup);
+
+
+
+
+

Setting Backup Preferences

+

Backup preferences are set by submitting an array of values to the first +parameter of the backup() method. Example:

+
$prefs = array(
+        'tables'        => array('table1', 'table2'),   // Array of tables to backup.
+        'ignore'        => array(),                     // List of tables to omit from the backup
+        'format'        => 'txt',                       // gzip, zip, txt
+        'filename'      => 'mybackup.sql',              // File name - NEEDED ONLY WITH ZIP FILES
+        'add_drop'      => TRUE,                        // Whether to add DROP TABLE statements to backup file
+        'add_insert'    => TRUE,                        // Whether to add INSERT data to backup file
+        'newline'       => "\n"                         // Newline character used in backup file
+);
+
+$this->dbutil->backup($prefs);
+
+
+
+
+

Description of Backup Preferences

+ ++++++ + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + +
PreferenceDefault ValueOptionsDescription
tablesempty arrayNoneAn array of tables you want backed up. If left blank all tables will be +exported.
ignoreempty arrayNoneAn array of tables you want the backup routine to ignore.
formatgzipgzip, zip, txtThe file format of the export file.
filenamethe current date/timeNoneThe name of the backed-up file. The name is needed only if you are using +zip compression.
add_dropTRUETRUE/FALSEWhether to include DROP TABLE statements in your SQL export file.
add_insertTRUETRUE/FALSEWhether to include INSERT statements in your SQL export file.
newline“\n”“\n”, “\r”, “\r\n”Type of newline to use in your SQL export file.
foreign_key_checksTRUETRUE/FALSEWhether output should keep foreign key checks enabled.
+
+
+
+

Class Reference

+
+
+class CI_DB_utility
+
+
+backup([$params = array()])
+
+++ + + + + + + + +
Parameters:
    +
  • $params (array) – An associative array of options
    • +
+
Returns:

raw/(g)zipped SQL query string

+
Return type:

string

+
+

Perform a database backup, per user preferences.

+
+ +
+
+database_exists($database_name)
+
+++ + + + + + + + +
Parameters:
    +
  • $database_name (string) – Database name
    • +
+
Returns:

TRUE if the database exists, FALSE otherwise

+
Return type:

bool

+
+

Check for the existence of a database.

+
+ +
+
+list_databases()
+
+++ + + + + + +
Returns:Array of database names found
Return type:array
+

Retrieve a list of all the database names.

+
+ +
+
+optimize_database()
+
+++ + + + + + +
Returns:Array of optimization messages or FALSE on failure
Return type:array
+

Optimizes the database.

+
+ +
+
+optimize_table($table_name)
+
+++ + + + + + + + +
Parameters:
    +
  • $table_name (string) – Name of the table to optimize
    • +
+
Returns:

Array of optimization messages or FALSE on failure

+
Return type:

array

+
+

Optimizes a database table.

+
+ +
+
+repair_table($table_name)
+
+++ + + + + + + + +
Parameters:
    +
  • $table_name (string) – Name of the table to repair
    • +
+
Returns:

Array of repair messages or FALSE on failure

+
Return type:

array

+
+

Repairs a database table.

+
+ +
+
+csv_from_result($query[, $delim = ', '[, $newline = "n"[, $enclosure = '"']]])
+
+++ + + + + + + + +
Parameters:
    +
  • $query (object) – A database result object
    • +
  • $delim (string) – The CSV field delimiter to use
    • +
  • $newline (string) – The newline character to use
    • +
  • $enclosure (string) – The enclosure delimiter to use
    • +
+
Returns:

The generated CSV file as a string

+
Return type:

string

+
+

Translates a database result object into a CSV document.

+
+ +
+
+xml_from_result($query[, $params = array()])
+
+++ + + + + + + + +
Parameters:
    +
  • $query (object) – A database result object
    • +
  • $params (array) – An associative array of preferences
    • +
+
Returns:

The generated XML document as a string

+
Return type:

string

+
+

Translates a database result object into an XML document.

+
+ +
+ +
+
+ + +