PHP Classes

PHP MySQL Wrapper Class: MySQL database access wrapper

Recommend this page to a friend!
  Info   View files View files (12)   DownloadInstall with Composer Download .zip   Reputation   Support forum   Blog (1)    
Last Updated Ratings Unique User Downloads Download Rankings
2022-10-28 (1 month ago) RSS 2.0 feedNot yet rated by the usersTotal: 921 This week: 4All time: 3,848 This week: 221Up
Version License PHP version Categories
mysql-access-wrapper 1.20GNU General Publi...4.3Databases
Description Author

This class implements a generic MySQL database access wrapper. It can:

- Connect to a given MySQL server
- Set the connection character set encoding
- Execute arbitrary queries and return the results in arrays
- Retrieve the columns of a table
- Execute UPDATE or INSERT queries from parameters that define the tables, fields, field values and conditions
- Execute multiple INSERT or UPDATE queries at once
- Count the number of rows of a table that match a given condition
- Get the next value of an auto-incremented table field
- Delete table rows that match a given condition
- Export, import, update table using CSV files
- Create table from CSV file
- Export query to CSV file
- Replace values in a given table for defined columns
- Rename, copy, truncate or drop table
- Get database size
- Log queries and errors with backtrace information

Recommendations

What is the best PHP export mysql tab delimited class?
Export MySQL Table to tab delimited file

Picture of Radovan Janjic
  Performance   Level  
Name: Radovan Janjic is available for providing paid consulting. Contact Radovan Janjic .
Classes: 14 packages by
Country: Serbia Serbia
Age: 34
All time rank: 2482 in Serbia Serbia
Week rank: 5 Up1 in Serbia Serbia Up
Innovation award
Innovation award
Nominee: 3x

Details
PHP MySQL Wrapper Class ======================= This class implements a generic MySQL database access wrapper. * [Connect to a given MySQL server](#connect-to-a-given-mysql-server) * [Connection examples](#connection-examples) * [Connection example multi host, db manipulation](#connection-example-multi-host-db-manipulation) * [Set the connection character set encoding](#set-the-connection-character-set-encoding) * [Execute arbitrary queries and return the results in arrays](#execute-arbitrary-queries-and-return-the-results-in-arrays) * [Select example with fetch result](#select-example-with-fetch-result) * [Prepared statements](#prepared-statements-works-only-with-mysqli) * [Prepared statements - mysqlnd driver not installed](#prepared-statements-works-only-with-mysqli---if-mysqlnd-driver-is-not-installed) * [Fetch query to array](#fetch-query-to-array) * [Multi results](#multi-results) * [Rows, Cols num](#rows-cols-num) * [Count rows](#count-rows) * [Execute UPDATE or INSERT queries from parameters that define the tables, fields, field values and conditions](#execute-update-or-insert-queries-from-parameters-that-define-the-tables-fields-field-values-and-conditions) * [Array to insert](#array-to-insert) * [Multiple array to insert](#array-to-insert-multirow) * [Array to update](#array-to-update) * [Multiple array to update](#array-to-update-multirow) * [Count the number of rows of a table that match a given condition](#count-rows) * [Delete table rows that match a given condition](#delete-rows) * [Operations with CSV files](#operations-with-csv-files) * [Export table to CSV](#export-table-to-csv) * [Export query to CSV](#export-query-to-csv) * [Export table / export query to CSV using fputcsv](#export-table--export-query-to-csv-using-fputcsv) * [Download CSV file from query](#download-csv-file-from-query) * [Import CSV to Table](#import-csv-to-table) * [Import and update CSV to Table](#import-and-update-csv-to-table) * [Create table from CSV file](#create-table-from-csv-file) * [Operations with XML files](#operations-with-xml-files) * [Export query to XML](#export-query-to-xml) * [Download XML file from query](#download-xml-file-from-query) * [Do str_replace in given database, table or defined columns in table](#string-search-and-replace-in-all-or-defined-table-columns) * [Search string & replace string](#string-search-and-replace-in-all-or-defined-table-columns) * [Search array & replace string](#string-search-and-replace-in-all-or-defined-table-columns) * [Search array & replace array](#string-search-and-replace-in-all-or-defined-table-columns) * [Search array of columns (search array & replace array) return count of updated](#string-search-and-replace-in-all-or-defined-table-columns) * [String multiple columns](#string-search-and-replace-in-all-or-defined-table-columns) * [All columns in table](#string-search-and-replace-in-all-or-defined-table-columns) * [Whole database](#string-search-and-replace-in-all-or-defined-table-columns) * [Basic table operations](#basic-table-operation) * [Copy table (with data included)](#basic-table-operation) * [Copy table structure](#basic-table-operation) * [Rename table](#basic-table-operation) * [Swap table names](#basic-table-operation) * [Truncate table (empty)](#basic-table-operation) * [Drop one table](#basic-table-operation) * [Drop multiple tables](#basic-table-operation) * [Get table columns](#get-table-columns) * [Get database size](#get-database-size) * [Get the next value of an auto-incremented table field](#next-autoincrement) * [Table revision](#table-revision) * [Logging / debug](#loging--debug) * [Logging errors](#logging-errors) * [Logging queries](#logging-queries) * [E-mail on error / die on error](#e-mail-on-error--die-on-error) * [Errors backtrace and debug](#errors-backtrace-and-debug) ### Connectivity settings ```php // Set your connectivity settings define('MySQL_HOST', 'localhost'); // localhost:3306 define('MySQL_USER', 'root'); define('MySQL_PASS', ''); define('MySQL_DB', 'test'); ``` ### Connect to a given MySQL server ```php $db = MySQL_wrapper::getInstance(MySQL_HOST, MySQL_USER, MySQL_PASS, MySQL_DB); // Connect $db->connect(); // // ... do queries // // Close connection $db->close(); ``` #### Connection examples ```php $db = MySQL_wrapper::getInstance(MySQL_HOST, MySQL_USER, MySQL_PASS, MySQL_DB); // Connect 1 $db->connect(); // // Connection 1 queries ... // // Close connection 1 $db->close(); // Connect 2 $db->connect(MySQL_HOST, MySQL_USER, MySQL_PASS, MySQL_DB); // // Connection 2 queries ... // // Close connection 2 $db->close(); // Connection 3 $db->connect(); // // Connection 3 queries // // Close connection 3 $db->close(); ``` #### Connection example multi host, db manipulation ```php // Inst. 1 $db1 = MySQL_wrapper::getInstance('host1', MySQL_USER, MySQL_PASS, MySQL_DB); // Inst. 2 $db2 = MySQL_wrapper::getInstance('host2', MySQL_USER, MySQL_PASS, MySQL_DB); // Connect host 1 $db1->connect(); // Connect host 2 $db2->connect(); // // ... do queries of cennection 1 or connection 2 // // Close connection host 1 $db1->close(); // Close connection host 2 $db2->close(); ``` ### Set the connection character set encoding #### Example 1 ```php $db = MySQL_wrapper::getInstance(MySQL_HOST, MySQL_USER, MySQL_PASS, MySQL_DB); // Connect $db->connect(); // Set charset $db->charset = 'utf8';; // Close connection $db->close(); ``` #### Example 2 ```php $db = MySQL_wrapper::getInstance(MySQL_HOST, MySQL_USER, MySQL_PASS, MySQL_DB); // Connect $db->connect(); // Set charset $db->setCharset('utf8'); // Close connection $db->close(); ``` ### Execute arbitrary queries and return the results in arrays #### Select example with fetch result ```php $db = MySQL_wrapper::getInstance(MySQL_HOST, MySQL_USER, MySQL_PASS, MySQL_DB); // Connect to host $db->connect(); // MySQL query $db->query('SELECT * FROM `table`'); // Int affected rows if ($db->affected > 0) { while ($row = $db->fetchArray()) { // Result print_r($row); } } // Free result memory $db->freeResult(); // Escape string $var = '\''; // Do query $db->query("SELECT * FROM `table` WHERE `firstname` LIKE '{$db->escape($var)}';"); // Param to be escaped $db->query("SELECT * FROM `table` WHERE `firstname` LIKE '@1%' OR `surname` LIKE '%@1%';", 'rado'); // Params as args $db->query("SELECT * FROM `table` WHERE `firstname` LIKE '@1%' AND `surname` LIKE '%@2%' OR id = @3;", 'rado', 'janjic', 3 /* , ... */); // Array of params $params = array(); $params['id'] = 1; $params['name'] = 'rado'; $params['lname'] = 'janjic'; $params['limit'] = 5; // Exec query $db->query("SELECT * FROM `table` WHERE `firstname` LIKE '@name%' AND `surname` LIKE '%@lname%' OR `id` = @id LIMIT @limit;", $params); // Int affected rows if ($db->affected > 0) { while ($row = $db->fetchArray()) { // Print result row print_r($row); } } // Free result memory $db->freeResult(); // Close connection $db->close(); ``` #### Prepared statements (works only with MySQLi!) ```php $db = MySQL_wrapper::getInstance(MySQL_HOST, MySQL_USER, MySQL_PASS, MySQL_DB); // Works only with MySQLi! $db->extension = 'mysqli'; // Connect $db->connect(); $name = 'Radovan'; $stmt = $db->call('prepare', 'SELECT * FROM `table` WHERE `firstname` = ?;'); $stmt->bind_param('s', $name); $stmt->execute(); $result = $stmt->get_result(); while ($row = $result->fetch_assoc()) { // do something // print_r($row); // ... } // Close connection $db->close(); ``` #### Prepared statements (works only with MySQLi!) - if mysqlnd driver is not installed ```php $db = MySQL_wrapper::getInstance(MySQL_HOST, MySQL_USER, MySQL_PASS, MySQL_DB); // Connect $db->connect(); $stmt = $db->call('prepare', 'SELECT `id`, `firstname`, `surname`, `email` FROM `table` WHERE `level` = ?;'); $stmt->bind_param('i', $level); $stmt->execute(); $stmt->bind_result($id, $firstname, $surname, $email); $data = array(); while ($stmt->fetch()) { $data[] = array( 'id' => $id, 'firstname' => $firstname, 'surname' => $surname, 'email' => $email ); } // Print data print_r($data); // Close connection $db->close(); ``` #### Fetch query to array ```php $db = MySQL_wrapper::getInstance(MySQL_HOST, MySQL_USER, MySQL_PASS, MySQL_DB); // Connect $db->connect(); // Fetch query to array $array = $db->fetchQueryToArray('SELECT * FROM `table`'); // Print array print_r($array); // Returns only first row $array = $db->fetchQueryToArray('SELECT * FROM `table`', TRUE); // Print array print_r($array); // Close connection $db->close(); ``` #### Multi results ```php $db = MySQL_wrapper::getInstance(MySQL_HOST, MySQL_USER, MySQL_PASS, MySQL_DB); // Connect to host $db->connect(); // Result 1 $r1 = $db->query('SELECT * FROM `table`'); // Result 2 $r2 = $db->query('SELECT * FROM `table` LIMIT 2'); // Result 1 data if ($db->numRows($r1)) { while ($row = $db->fetchArray($r1)) { // Print rows print_r($row); } } // Result 2 data if ($db->numRows($r2)) { while ($row = $db->fetchArray($r2)) { // Print rows print_r($row); } } // Free relust 1 $db->freeResult($r1); // Free relust 2 $db->freeResult($r2); // Close connection $db->close(); ``` #### Rows, Cols num ```php $db = MySQL_wrapper::getInstance(MySQL_HOST, MySQL_USER, MySQL_PASS, MySQL_DB); // Connect to host $db->connect(); // Do query $db->query('SELECT * FROM `table`'); $cols = $db->numFields(); $rows = $db->numRows(); // ... echo "Cols: {$cols}, Rows: {$rows}"; // Free result memory $db->freeResult(); // Close connection $db->close(); ``` #### Count rows ```php $db = MySQL_wrapper::getInstance(MySQL_HOST, MySQL_USER, MySQL_PASS, MySQL_DB); // Connect to host $db->connect(); // Count all $count = $db->countRows('table'); // Count with condition $count2 = $db->countRows('table', "`date` = '" . date("Y-m-d") . "'"); // ... echo "Count all: {$count}, Count today: {$count2}"; // More info /** Retrieves the number of rows from table based on certain conditions. * @param string $table - Table name * @param string $where - WHERE Clause * @return integer or false * * function countRows($table, $where = NULL); */ // Close connection $db->close(); ``` ### Execute UPDATE or INSERT queries from parameters that define the tables, fields, field values and conditions #### Array to insert ```php $db = MySQL_wrapper::getInstance(MySQL_HOST, MySQL_USER, MySQL_PASS, MySQL_DB); // Connect to host $db->connect(); // Array data // [fealdname] = feald value $data = array(); $data['firstname'] = 'Radovan'; $data['surname'] = 'Janjic'; $data['email'] = 'rade@it-radionica.com'; // reserved values 'null', 'now()', 'curtime()', 'localtime()', 'localtime', 'utc_date()', 'utc_time()', 'utc_timestamp()' $data['date'] = 'now()'; // $db->arrayToInsert( ... ) returns insert id $insert_id = $db->arrayToInsert('table', $data); echo "Last insert id is: {$insert_id}"; // More options /** Creates an sql string from an associate array * @param string $table - Table name * @param array $data - Data array Eg. $data['column'] = 'val'; * @param boolean $ingore - INSERT IGNORE (row won't actually be inserted if it results in a duplicate key) * @param string $duplicateupdate - ON DUPLICATE KEY UPDATE (The ON DUPLICATE KEY UPDATE clause can contain multiple column assignments, separated by commas.) * @return insert id or false * * function arrayToInsert($table, $data, $ignore = FALSE, $duplicateupdate = NULL); */ // Close connection $db->close(); ``` #### Array to insert multirow ```php $db = MySQL_wrapper::getInstance(MySQL_HOST, MySQL_USER, MySQL_PASS, MySQL_DB); // Connect to host $db->connect(); // Array data // [fealdname] = feald value $data = array(); // Data set 1 $data[] = array( 'firstname' => 'foo', 'surname' => 'bar', 'email' => 'hi@radovanjanjic.com', 'date' => 'now()' ); // Data set 2 $data[] = array( 'firstname' => 'baz', 'surname' => 'qux', 'email' => 'hi@radovanjanjic.com', 'date' => 'now()' ); // Data set ... // $db->arrayToInsert( ... ) multirow returns TRUE on success $db->arrayToInsert('table', $data); // Close connection $db->close(); ``` #### Array to update ```php $db = MySQL_wrapper::getInstance(MySQL_HOST, MySQL_USER, MySQL_PASS, MySQL_DB); // Connect to host $db->connect(); // Array data // [fealdname] = feald value $data = array(); $data['firstname'] = 'Radovan'; $data['surname'] = 'Janjic'; // Reserved values: null, now(), curtime(), localtime(), localtime, utc_date(), utc_time(), utc_timestamp() $data['email'] = 'null'; $data['date'] = 'now()'; $db->arrayToUpdate('table', $data, "`id` = {$insert_id}"); if ($db->affected > 0) { echo "Updated: {$db->affected} row(s)."; } // More options /** Creates an sql string from an associate array * @param string $table - Table name * @param array $data - Data array Eg. $data['column'] = 'val'; * @param string $where - MySQL WHERE Clause * @param integer $limit - Limit offset * @param resource $link - link identifier * @return number of updated rows or false * * function arrayToUpdate($table, $data, $where = NULL, $limit = 0, $link = 0); */ // Close connection $db->close(); ``` #### Array to update multirow ```php $db = MySQL_wrapper::getInstance(MySQL_HOST, MySQL_USER, MySQL_PASS, MySQL_DB); // Connect to host $db->connect(); // Array data // [fealdname] = feald value $data = array(); // Data set 1 $data[] = array( // Condition 'id' => 1, // One of the fields has to be primary or unique key in order to update // Data to update 'firstname' => 'foooo', 'surname' => 'barrr' // ... ); // Data set 2 $data[] = array( // Condition 'id' => 2, // One of the fields has to be primary or unique key in order to update // Data to update 'firstname' => 'bazzz', 'surname' => 'quxxx' // ... ); // Data set ... // $db->arrayToUpdate( ... ) multirow returns TRUE on success $db->arrayToUpdate('table', $data); // Close connection $db->close(); ``` ### Delete row(s) ```php $db = MySQL_wrapper::getInstance(MySQL_HOST, MySQL_USER, MySQL_PASS, MySQL_DB); // Connect to host $db->connect(); // Delete row $db->deleteRow('table', "`id` = {$insert_id}"); if ($db->affected > 0) { echo "Deleted: {$db->affected} row(s)."; } // More options /** Delete row(s) from table based on certain conditions. * @param string $table - Table name * @param string $where - WHERE Clause * @param integer $limit - Limit offset * @param resource $link - link identifier * @return number of deleted rows or false * * function deleteRow($table, $where = NULL, $limit = 0, $link = 0); */ // Close connection $db->close(); ``` ### Operations with CSV files #### Export Table to CSV ```php $db = MySQL_wrapper::getInstance(MySQL_HOST, MySQL_USER, MySQL_PASS, MySQL_DB); // Connect $db->connect(); // Export all data $db->exportTable2CSV('table', 'test_files/test-1.txt'); // Export two or more columns $db->exportTable2CSV('table', 'test_files/test-2.txt', 'firstname, surname'); // Export two or more columns using array $db->exportTable2CSV('table', 'test_files/test-3.txt', array('firstname', 'surname', 'date')); // Export all columns where id < 8 and limit 1, 5 $db->exportTable2CSV('table', 'test_files/test-4.txt', '*', 'id < 8', '1,5'); // More options /** Export table data to CSV file. * @param string $table - Table name * @param string $file - CSV File path * @param mixed $columns - SQL ( * or column names or array with column names) * @param string $where - MySQL WHERE Clause * @param integer $limit - Limit offset * @param string $delimiter - COLUMNS TERMINATED BY (Default: ',') * @param string $enclosure - OPTIONALLY ENCLOSED BY (Default: '"') * @param string $escape - ESCAPED BY (Default: '\') * @param string $newLine - New line detelimiter (Default: \n) * @param boolean $showColumns - Columns names in first line * @return number of inserted rows or false * * function exportTable2CSV($table, $file, $columns = '*', $where = NULL, $limit = 0, $delimiter = ',', $enclosure = '"', $escape = '\\', $newLine = '\n', $showColumns = TRUE); */ // Close connection $db->close(); ``` #### Export query to CSV ```php $db = MySQL_wrapper::getInstance(MySQL_HOST, MySQL_USER, MySQL_PASS, MySQL_DB); // Connect $db->connect(); $path = $db->query2CSV('select * from `table` limit 10', 'test_files/test-query2csv.csv'); echo 'Query exported to CSV file: ', $path; // Example 2 $path = $db->query2CSV('select * from `table` limit 2,2', 'test_files/test-query2csv.csv'); /** Export query to CSV file. * @param string $sql - MySQL Query * @param string $file - CSV File path * @param string $delimiter - COLUMNS TERMINATED BY (Default: ',') * @param string $enclosure - OPTIONALLY ENCLOSED BY (Default: '"') * @param string $escape - ESCAPED BY (Default: '\') * @param string $newLine - New line delimiter (Default: \n) * @param boolean $showColumns - Columns names in first line * @return - File path * * function query2CSV($sql, $file, $delimiter = ',', $enclosure = '"', $escape = '\\', $newLine = '\n', $showColumns = TRUE); */ // Close connection $db->close(); ``` #### Export table / export query to CSV using fputcsv ```php $db = MySQL_wrapper::getInstance(MySQL_HOST, MySQL_USER, MySQL_PASS, MySQL_DB); // Connect $db->connect(); // Don't use mysql outfile $db->mysqlOutFile = FALSE; // Table to CSV $db->exportTable2CSV('table', 'test_files/test-1.txt'); // Query to CSV $path = $db->query2CSV('select * from `table` limit 10', 'test_files/test-query2csv.csv'); // Close connection $db->close(); ``` #### Download CSV file from query ```php $db = MySQL_wrapper::getInstance(MySQL_HOST, MySQL_USER, MySQL_PASS, MySQL_DB); // Connect $db->connect(); // Set as attachment and execute $db->attachment()->query2CSV('select * from `table`', 'test.csv'); // Close connection $db->close(); ``` #### Import CSV to Table ```php $db = MySQL_wrapper::getInstance(MySQL_HOST, MySQL_USER, MySQL_PASS, MySQL_DB); // Connect $db->connect(); // Import all data $db->importCSV2Table('test_files/test-1.txt', 'table'); // More options /** Imports CSV data to Table with possibility to update rows while import. * @param string $file - CSV File path * @param string $table - Table name * @param string $delimiter - COLUMNS TERMINATED BY (Default: ',') * @param string $enclosure - OPTIONALLY ENCLOSED BY (Default: '"') * @param string $escape - ESCAPED BY (Defaul: '\') * @param integer $ignore - Number of ignored rows (Default: 1) * @param array $update - If row fields needed to be updated eg date format or increment (SQL format only @FIELD is variable with content of that field in CSV row) $update = array('SOME_DATE' => 'STR_TO_DATE(@SOME_DATE, "%d/%m/%Y")', 'SOME_INCREMENT' => '@SOME_INCREMENT + 1') * @param string $getColumnsFrom - Get Columns Names from (file or table) - this is important if there is update while inserting (Default: file) * @param string $newLine - New line detelimiter (Default: \n) * @return number of inserted rows or false * * function importCSV2Table($file, $table, $delimiter = ',', $enclosure = '"', $escape = '\\', $ignore = 1, $update = array(), $getColumnsFrom = 'file', $newLine = '\n'); */ // Close connection $db->close(); ``` #### Import and update CSV to Table ```php $db = MySQL_wrapper::getInstance(MySQL_HOST, MySQL_USER, MySQL_PASS, MySQL_DB); // Connect $db->connect(); // Import and update all data $db->importUpdateCSV2Table('test_files/countrylist.csv', 'csv_to_table_test'); // Import and update all data $db->importUpdateCSV2Table('test_files/countrylist.csv', 'csv_to_table_test', ',', '"', '\\', 1, array(), 'file', '\r\n'); // More options /** Imports (ON DUPLICATE KEY UPDATE) CSV data in Table with possibility to update rows while import. * @param string $file - CSV File path * @param string $table - Table name * @param string $delimiter - COLUMNS TERMINATED BY (Default: ',') * @param string $enclosure - OPTIONALLY ENCLOSED BY (Default: '"') * @param string $escape - ESCAPED BY (Defaul: '\') * @param integer $ignore - Number of ignored rows (Default: 1) * @param array $update - If row fields needed to be updated eg date format or increment (SQL format only @FIELD is variable with content of that field in CSV row) $update = array('SOME_DATE' => 'STR_TO_DATE(@SOME_DATE, "%d/%m/%Y")', 'SOME_INCREMENT' => '@SOME_INCREMENT + 1') * @param string $getColumnsFrom - Get Columns Names from (file or table) - this is important if there is update while inserting (Default: file) * @param string $newLine - New line detelimiter (Default: \n) * @return number of inserted rows or false * * function importUpdateCSV2Table($file, $table, $delimiter = ',', $enclosure = '"', $escape = '\\', $ignore = 1, $update = array(), $getColumnsFrom = 'file', $newLine = '\n'); */ // Close connection $db->close(); ``` #### Create table from CSV file ```php $db = MySQL_wrapper::getInstance(MySQL_HOST, MySQL_USER, MySQL_PASS, MySQL_DB); // Connect to host $db->connect(); $db->dropTable('csv_to_table_test'); $db->createTableFromCSV('test_files/countrylist.csv', 'csv_to_table_test'); $db->dropTable('csv_to_table_test_no_column_names'); $db->createTableFromCSV('test_files/countrylist1.csv', 'csv_to_table_test_no_column_names', ',', '"', '\\', 0, array(), 'generate', '\r\n'); /** Create table from CSV file and imports CSV data to Table with possibility to update rows while import. * @param string $file - CSV File path * @param string $table - Table name * @param string $delimiter - COLUMNS TERMINATED BY (Default: ',') * @param string $enclosure - OPTIONALLY ENCLOSED BY (Default: '"') * @param string $escape - ESCAPED BY (Default: '\') * @param integer $ignore - Number of ignored rows (Default: 1) * @param array $update - If row fields needed to be updated eg date format or increment (SQL format only @FIELD is variable with content of that field in CSV row) $update = array('SOME_DATE' => 'STR_TO_DATE(@SOME_DATE, "%d/%m/%Y")', 'SOME_INCREMENT' => '@SOME_INCREMENT + 1') * @param string $getColumnsFrom - Get Columns Names from (file or generate) - this is important if there is update while inserting (Default: file) * @param string $newLine - New line delimiter (Default: \n) * @return number of inserted rows or false * * function createTableFromCSV($file, $table, $delimiter = ',', $enclosure = '"', $escape = '\\', $ignore = 1, $update = array(), $getColumnsFrom = 'file', $newLine = '\r\n'); */ // Close connection $db->close(); ``` ### Operations with XML files #### Export query to XML ```php $db = MySQL_wrapper::getInstance(MySQL_HOST, MySQL_USER, MySQL_PASS, MySQL_DB); // Connect $db->connect(); // Save result as file $db->query2XML('select * from `table` limit 10', 'items', 'item', 'test_files/test-query2xml.csv'); // Return result as XML $xml = $db->query2XML('select * from `table` limit 10', 'items', 'item'); /** Export query to XML file or return as XML string * @param string $query - mysql query * @param string $rootElementName - root element name * @param string $childElementName - child element name * @return string - XML * * function query2XML($query, $rootElementName, $childElementName, $file = NULL); */ // Close connection $db->close(); ``` #### Download XML file from query ```php $db = MySQL_wrapper::getInstance(MySQL_HOST, MySQL_USER, MySQL_PASS, MySQL_DB); // Connect $db->connect(); // Set as attachment and execute $db->attachment()->query2XML('select * from `table`', 'root', 'item', 'test.xml'); // Close connection $db->close(); ``` ### Transactions ```php $db = MySQL_wrapper::getInstance(MySQL_HOST, MySQL_USER, MySQL_PASS, MySQL_DB); // Connect $db->connect(); // Queries $queries = array(); $queries[] = 'SELECT ...'; $queries[] = 'INSERT ...'; $queries[] = 'DELETE ...'; $queries[] = '...'; // Do Transaction $db->transaction($queries); // Get more info on: http://dev.mysql.com/doc/refman/5.0/en/commit.html /** Transaction * @param array $qarr - Array with Queries * @link http://dev.mysql.com/doc/refman/5.0/en/commit.html * * function transaction($qarr = array()); */ // Close connection $db->close(); ``` ### String Search and Replace in all or defined Table Columns ```php $db = MySQL_wrapper::getInstance(MySQL_HOST, MySQL_USER, MySQL_PASS, MySQL_DB); // Connect $db->connect(); // Simple $db->strReplace('table', 'firstname', 'search', 'replace'); // Search array & Replace string $db->strReplace('table', 'firstname', array('search1', 'search2'), 'replace'); // Search array & Replace array $db->strReplace('table', 'firstname', array('search1', 'search2'), array('replace1', 'replace2')); // Search array of columns (Search array & Replace array) return count of updated fielsd $count = $db->strReplace('table', array('firstname', 'surname'), array('search1', 'search2'), array('replace1', 'replace2')); // String multiple columns $db->strReplace('table', 'firstname, surname', 'search', 'replace'); // You can set all columns in table as well $db->strReplace('table', '*', 'search', 'replace'); // Whole database $db->strReplace('*', '*', 'search', 'replace'); // More options /** Replace all occurrences of the search string with the replacement string in MySQL Table Column(s). * @param string $table - Table name * @param mixed $columns - Search & Replace affected Table columns. An array may be used to designate multiple replacements. * @param mixed $search - The value being searched for, otherwise known as the needle. An array may be used to designate multiple needles. * @param mixed $replace - The replacement value that replaces found search values. An array may be used to designate multiple replacements. * @param string $where - WHERE Clause * @param integer $limit - Limit offset * @return integer - Affected rows * * function strReplace($table, $columns, $search, $replace, $where = NULL, $limit = 0); */ // Close connection $db->close(); ``` ### Basic Table Operation ```php $db = MySQL_wrapper::getInstance(MySQL_HOST, MySQL_USER, MySQL_PASS, MySQL_DB); // Connect to host $db->connect(); // Copy table (with data included) $db->copyTable('table', 'table_copy'); // Copy table (with data included) $db->copyTable('table', 'table_copy4'); // Copy table structure $db->copyTable('table', 'table_copy2', FALSE); // Rename table $db->renameTable(array('table_copy' => 'table_copy3')); // Swap table names $db->renameTable(array('table_copy3' => 'tmp_table', 'table_copy2' => 'table_copy3', 'tmp_table' => 'table_copy3')); // Truncate table (empty) $db->truncateTable('table_copy2'); // Drop one table $db->dropTable('table_copy4'); // Drop multiple tables $db->dropTable(array('table_copy3', 'table_copy2')); // Close connection $db->close(); ``` #### Get table columns ```php $db = MySQL_wrapper::getInstance(MySQL_HOST, MySQL_USER, MySQL_PASS, MySQL_DB); // Connect $db->connect(); // Get table columns into array $array = $db->getColumns('table'); print_r($array); // Close connection $db->close(); ``` #### Get database size ```php $db = MySQL_wrapper::getInstance(MySQL_HOST, MySQL_USER, MySQL_PASS, MySQL_DB); // Connect $db->connect(); /** Data Base size in B / KB / MB / GB / TB * @param string $sizeIn - Size in B / KB / MB / GB / TB * @param integer $round - Round on decimals * @param resource $link - Link identifier * @return - Size in B / KB / MB / GB / TB * * function getDataBaseSize($sizeIn = 'MB', $round = 2, $link = 0); */ echo 'Database size is: ', $db->getDataBaseSize('mb', 2), ' MB'; // Close connection $db->close(); ``` #### Next AutoIncrement ```php $db = MySQL_wrapper::getInstance(MySQL_HOST, MySQL_USER, MySQL_PASS, MySQL_DB); // Connect to host $db->connect(); // Returns next auto increment value $auto_increment = $db->nextAutoIncrement('table'); echo "Next auto increment id is: {$auto_increment}"; // Close connection $db->close(); ``` #### Table revision ```php $db = MySQL_wrapper::getInstance(MySQL_HOST, MySQL_USER, MySQL_PASS, MySQL_DB); // Connect $db->connect(); // Init table revision (do this only once!) $db->initTableRevision('rev-table'); // Time to restore to ... $time = '2014-06-25 14:26:03'; /** Create table from current revision time * @param string $table - New table name * @param string $rev_table - Revision table (origin table) * @param string $id_field - Unique field name * @param datetime - Revision time * * function createTableFromRevisionTime($table, $rev_table, $id_field, $time); */ $db->createTableFromRevisionTime('rev-table' . '-' . $time, 'rev-table', 'id', $time); /** Restore table from current revision time * @param string $table - New table name * @param string $id_field - Unique field name * @param datetime - Revision time * * function restoreTableFromRevisionTime($table, $id_field, $time); */ $db->restoreTableFromRevisionTime('rev-table', 'id', $time); // Close connection $db->close(); ``` ### Logging / debug #### Logging errors ```php $db = MySQL_wrapper::getInstance(MySQL_HOST, MySQL_USER, MySQL_PASS, MySQL_DB); // Connect to host $db->connect(); // This is useful to be TRUE! $db->logErrors = TRUE; // Default is FALSE, use TRUE only for debuging (security reasons!) $db->displayError = TRUE; // Date / Time format for log $db->dateFormat = "Y-m-d H:i:s"; // Log file $db->logFilePath = 'log-mysql.txt'; // This query has error $db->query('SELECT * FROM `table` asfd!@#$'); // Close connection $db->close(); ``` #### Logging queries ```php $db = MySQL_wrapper::getInstance(MySQL_HOST, MySQL_USER, MySQL_PASS, MySQL_DB); // Connect to host $db->connect(); // Default is FALSE, use TRUE only for debuging $db->logQueries = TRUE; // Log file $db->logFilePath = 'log-mysql.txt'; // Query for this function will be logged $db->getColumns('table'); // Query will be logged as well ... $db->query('SELECT * FROM `table`;'); // Close connection $db->close(); ``` #### E-mail on error / die on error ```php $db = MySQL_wrapper::getInstance(MySQL_HOST, MySQL_USER, MySQL_PASS, MySQL_DB); // Connect $db->connect(); // Send mail on error $db->emailErrors = TRUE; // Die on errors $db->dieOnError = TRUE; // Array of emails $db->emailErrorsTo = array('rade@it-radionica.com'); // Do first query $db->query("select * from asdf"); // This one will not be executed if first query have error and dieOnError is TRUE $db->query("select * from asdf2"); // Close connection $db->close(); ``` #### Errors backtrace and debug ```php $db = MySQL_wrapper::getInstance(MySQL_HOST, MySQL_USER, MySQL_PASS, MySQL_DB); // Connect to host $db->connect(); // Default is FALSE, use TRUE only for debuging (security reasons!) $db->displayError = TRUE; // This query has error $db->query('SELECT * FROM `table` asfd!@#$'); // Close connection $db->close(); ``` Display error example: ``` Query fail: SELECT * FROM `table` asfd!@#$ - Error No: 1064 - Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '!@#$' at line 1 - Call: Function query in C:\xampp\htdocs\Git\PHP_MySQL_wrapper\test.php on line 29 ``` Draw result / describe table / explain query: ```php $db = MySQL_wrapper::getInstance(MySQL_HOST, MySQL_USER, MySQL_PASS, MySQL_DB); // Connect $db->connect(); // Draw query result data in table $array = $db->fetchQueryToArray('SELECT * FROM `table` LIMIT 2;'); $db->drawTable($array, 'Test table contents'); /** Draw ascii table * @param array $data - Multidimensional array of data * @param string $title - Table header * @return void * * function drawTable($data, $title = NULL); */ // Draw query execution plan in table $db->explain('SELECT * FROM `table`;'); // Draw information about the columns in a table $db->describe('table'); // Close connection $db->close(); ``` Draw table output: ``` +---------------------------------------------------------------+ | Test table contents | +----+-----------+---------+-----------------------+------------+ | id | firstname | surname | email | date | +----+-----------+---------+-----------------------+------------+ | 1 | foo | bar | rade@it-radionica.com | 2014-10-02 | | 2 | Radovan | Janjic | rade@it-radionica.com | 2014-10-02 | +----+-----------+---------+-----------------------+------------+ ``` Explain output: ``` +--------------------------------------------------------------------------------------+ | Explain MySQL Query | +----+-------------+-------+------+---------------+-----+---------+-----+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+-----+---------+-----+------+-------+ | 1 | SIMPLE | table | ALL | | | | | 98 | | +----+-------------+-------+------+---------------+-----+---------+-----+------+-------+ ``` Describe output: ``` +------------------------------------------------------------------+ | test | +-----------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-----------+--------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | | auto_increment | | firstname | varchar(100) | NO | | | | | surname | varchar(100) | NO | | | | +-----------+--------------+------+-----+---------+----------------+ ```
  Files folder image Files  
File Role Description
Files folder imagetest_files (7 files)
Accessible without login Plain text file example.php Example Example script
Accessible without login Plain text file LICENSE Data Auxiliary data
Accessible without login Plain text file log-mysql.txt Output Log file
Plain text file MySQL_wrapper.class.php Class class
Accessible without login Plain text file README.md Data Auxiliary data

  Files folder image Files  /  test_files  
File Role Description
  Accessible without login Plain text file countrylist.csv Data Auxiliary data
  Accessible without login Plain text file countrylist1.csv Data Auxiliary data
  Accessible without login Plain text file test-1.txt Doc. Documentation
  Accessible without login Plain text file test-2.txt Doc. Documentation
  Accessible without login Plain text file test-3.txt Doc. Documentation
  Accessible without login Plain text file test-4.txt Doc. Documentation
  Accessible without login Plain text file test-query2csv.csv Data Auxiliary data

 Version Control Reuses Unique User Downloads Download Rankings  
 100%1
Total:921
This week:4
All time:3,848
This week:221Up