<?php
/*
Lou Wrapper_mysql Examples
PHP version required (PHP 8.2)
*/
require "Lou_Wrapper_mysql.class.php";
// set your connectivity settings here
define('MySQL_HOST', 'localhost');
define('MySQL_USER', 'root');
define('MySQL_PASS', 'mysql');
define('MySQL_DB', 'testes');
define('MySQL_DB2', 'example');
echo "<br># Example 1 - Connection example <br>";
$db = Wrapper_mysql::getInstance(MySQL_HOST, MySQL_USER, MySQL_PASS, MySQL_DB);
$db->connect();
/* ... do queries */
echo "Connected to MySQL database: ".MySQL_DB."<br>";
/* Close connection 1 */
$db->close();
echo "<br># Example 1.1 - Connection example - New link<br>";
$db = Wrapper_mysql::getInstance();
$db->connect(MySQL_HOST, MySQL_USER, MySQL_PASS, MySQL_DB); /* You can use connection info here as well */
/* Connection 1 queries */
echo "Connected to MySQL database: ".MySQL_DB."<br>";
$db->close();
/* Connect with new link */
$db->connect(TRUE);
echo "Connected to MySQL database: ".MySQL_DB."<br>";
$db->close();/* Close connection */
echo "<br># Example 1.2 - Create Table struture 'table'<br>";
$db = Wrapper_mysql::getInstance(MySQL_HOST, MySQL_USER, MySQL_PASS, MySQL_DB);
$db->connect();
/* Create Table estruture */
$db->query("CREATE TABLE IF NOT EXISTS `table` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`firstname` varchar(250) NOT NULL,
`surname` varchar(250) NOT NULL,
`email` varchar(500) NOT NULL,
`date` date NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;");
echo "CREATE TABLE IF NOT EXISTS `table` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`firstname` varchar(250) NOT NULL,
`surname` varchar(250) NOT NULL,
`email` varchar(500) NOT NULL,
`date` date NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ; <br>";
echo "Table 'table' created.<br>";
echo "<br># Example 1.3 - Connection example multi host, db manipulation <br>";
$db1 = Wrapper_mysql::getInstance(); /* Host 1 instance */
/* Host 2 instance */
$db2 = Wrapper_mysql::getInstance(MySQL_HOST, MySQL_USER, MySQL_PASS, MySQL_DB2); /*use another connection info */
/* Connect host 1 */
$db1->connect(MySQL_HOST, MySQL_USER, MySQL_PASS, MySQL_DB);
echo "Connected to MySQL database_1: ".MySQL_DB."<br>";
/* Connect host 2 */
$db2->connect();
echo "Connected to MySQL database_2: ".MySQL_DB2."<br>";
/* ... do queries of cennection 1 or connection 2 */
$db1->close(); /* Close connection host 1 */
$db2->close(); /* Close connection host 2 */
echo "<br>** Example 2 - Insert data into table<br>";
$db = Wrapper_mysql::getInstance(MySQL_HOST, MySQL_USER, MySQL_PASS, MySQL_DB);
$db->connect();
$db->query("INSERT INTO `table` (`id`, `firstname`, `surname`, `email`, `date`) VALUES
(1, 'Jonh', 'knopfer', '', '2024-11-04'),
(2, 'Donald', 'Jan', ' [email protected]', '2024-12-15'),
(3, 'Antlou', 'Louc', ' [email protected]', '2025-02-23');");
echo "INSERT INTO `table` (`id`, `firstname`, `surname`, `email`, `date`) VALUES
(1, 'Jonh', 'knopfer', '', '2024-11-04'),
(2, 'Donald', 'Jan', ' [email protected]', '2024-12-15'),
(3, 'Antlou', 'Louc', ' [email protected]', '2025-02-23')<br> Data inserted<br>";
/* Close connection */
$db->close();
echo "<br># Example 3 - Array to insert <br>";
$db = Wrapper_mysql::getInstance(MySQL_HOST, MySQL_USER, MySQL_PASS, MySQL_DB);
$db->connect();
/* Array data // [fealdname] = feald value */
$data = array();
$data['id'] = 4;
$data['firstname'] = 'Manuel';
$data['surname'] = 'Marques';
$data['email'] = ' [email protected]';
/* 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}<br />";
$db->close();
echo "<br>** Example 3.1 - Array to insert(multirow) <br>";
$db = Wrapper_mysql::getInstance(MySQL_HOST, MySQL_USER, MySQL_PASS, MySQL_DB);
$db->connect();
/* Array data // [fealdname] = feald value */
$data = array();
$data['firstname'] = 'Antony';
$data['surname'] = 'Lour';
$data['email'] = ' [email protected]';
$data['date'] = 'now()';
/* [fealdname] = feald value */
$data2 = array();
$data2['firstname'] = 'Carmo';
$data2['surname'] = 'Oliv';
$data2['email'] = ' [email protected]';
$data2['date'] = 'now()';
/* $db->arrayToInsert( ... ) multirow returns TRUE on success */
$insert2 = $db->arrayToInsert('table', array($data, $data2 /*, $data3 .... */ ));
echo "Multirow inserted - Insert is: {$insert2} (1 - TRUE; 0 - FALSE)<br>";
$db->close();
echo "<br># Example 3.2 - Array to insert (more options) <br>";
$db = Wrapper_mysql::getInstance(MySQL_HOST, MySQL_USER, MySQL_PASS, MySQL_DB);
$db->connect();
/* 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
*/
$data = array();
$data['firstname'] = 'Carmo';
$data['surname'] = 'Oliv';
$data['email'] = ' [email protected]';
$data['date'] = 'now()';
$data = array();
$data['firstname'] = 'Adrian';
$data['surname'] = 'Carvalho';
$data['email'] = ' [email protected]';
$data['date'] = 'now()';
$insert3 = $db->arrayToInsert('table', $data, $ignore = FALSE, $duplicateupdate = NULL);
echo "Last insert id is: {$insert3} <br />";
$db->close();
echo "<br>* Example 4 - Select example with fetch result <br>";
$db = Wrapper_mysql::getInstance(MySQL_HOST, MySQL_USER, MySQL_PASS, MySQL_DB);
$db->connect();
/* MySQL query */
$db->query('SELECT * FROM `table`;');
/* Int affected rows */
if ($db->affected) {
echo "DB afected ". $db->affected .' registos<br>';
}
echo "<br># Example 4.1 (fetch row - array) <br>";
echo "<pre>";
while ($row = $db->fetchArray()) {
print_r($row);
}
echo "</pre>";
$db->freeResult();
$db->close();
echo "<br>* Example 4.2 - Select example with fetch result - Escape string <br>";
$db = Wrapper_mysql::getInstance(MySQL_HOST, MySQL_USER, MySQL_PASS, MySQL_DB);
$db->connect();
$var = '\'';
$db->query("SELECT * FROM `table` WHERE `firstname` LIKE '{$db->escape($var)}';");
echo "DB afected ". $db->affected .' registos<br>';
echo "<br>* Example 4.3 - Select example with fetch result - Param to be escaped <br>";
$db->query("SELECT * FROM `table` WHERE `firstname` LIKE '@1%' OR `surname` LIKE '%@1%';", 'rado');
/*
Explicação -
%: Este é um caractere wildcard no SQL. Ele representa zero ou mais caracteres. <br>
@1 e @2: Esses são placeholders. Eles indicam que um valor específico será inserido ali posteriormente.<br>
Estes carateres juntos significam que estamos procurando por valores em firstname e surname que comecem com o valor que substituirá @1 e @2 e sejam seguidos por zero ou mais caracteres.<br>
Concluindo: Se @1 fosse substituído por "Jo", então Jo% significaria que os registros com firstname igual a "Jo", "João", "Jonas", "Jocasta", etc., seriam correspondências.
*/
echo "DB afected ". $db->affected .' registos<br>';
echo "<br>* Example 4.3.1 - Select example with fetch result - Params as args <br>";
$db->query("SELECT * FROM `table` WHERE `firstname` LIKE '%@1%' AND `surname` LIKE '%@2%' OR id = @5;", '', 'Lou', 6 /* , ... */);
if ($db->affected) {
echo "DB afected ". $db->affected .' registos<br>';
echo "<pre>";
while ($row = $db->fetchArray()) {
print_r($row);
}
echo "</pre>";
}
echo "* Example 4.3.2- Select example with fetch result - Params as args <br>";
$db->query("SELECT * FROM `table` WHERE `firstname` LIKE 'Don' AND `surname` LIKE '%@2%' OR id = @3;", 'Don', 'Lou', 3 /* , ... */);
if ($db->affected) {
echo "DB afected:". $db->affected .' registos<br>';
echo "<pre>";
while ($row = $db->fetchArray()) {
print_r($row);
}
echo "</pre>";
}
echo "* Example 4.3.3- Select example with fetch result - Array of params <br>";
$params = array();
$params['id'] = 1;
$params['name'] = 'on';
$params['lname'] = '';
$params['limit'] = 5;
$db->query("SELECT * FROM `table` WHERE `firstname` LIKE '@name%' AND `surname` LIKE '%@lname%' OR `id` = @id LIMIT @limit;", $params);
if ($db->affected) {
echo "fetch row - array <br>";
echo "<pre>";
while ($row = $db->fetchArray()) {
print_r($row);
}
echo "</pre>";
}
$db->freeResult();
$db->close();
echo "<br># Example 5 - Prepared statements (works only with MySQLi!) <br>";
$db = Wrapper_mysql::getInstance();
$db->connect(MySQL_HOST, MySQL_USER, MySQL_PASS, MySQL_DB);
$name = 'Jose';
$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);
}
echo $result->num_rows ." registos<br>";
$db->close();
echo "<br>* Example 5.1 - Faster select example (fetch query to array) <br>";
$db = Wrapper_mysql::getInstance();
$db->connect(MySQL_HOST, MySQL_USER, MySQL_PASS, MySQL_DB);
print_r($db->fetchQueryToArray('SELECT * FROM `table`'));
echo "<br>";
echo "<br># Example 5.2 - Faster select example (Returns only first row) <br>";
print_r($db->fetchQueryToArray('SELECT * FROM `table`', TRUE));
$db->close();
echo "<br><br>* Example 5.3 - Multi results";
$db = Wrapper_mysql::getInstance();
$db->connect(MySQL_HOST, MySQL_USER, MySQL_PASS, MySQL_DB);
echo "<pre>";
$r1 = $db->query('SELECT * FROM `table`');
if ($db->numRows($r1)) {
while ($row = $db->fetchArray($r1)) {
print_r($row);
}
}
echo "</pre>";
$db->freeResult($r1);
echo "<br>* Example 5.4 - Multi results <br>";
$db = Wrapper_mysql::getInstance();
$db->connect(MySQL_HOST, MySQL_USER, MySQL_PASS, MySQL_DB);
echo "<pre>";
$r2 = $db->query('SELECT * FROM `table` LIMIT 2');
if ($db->numRows($r2)) {
while ($row = $db->fetchArray($r2)) {
print_r($row);
}
}
echo "</pre>";
$db->freeResult($r2);
$db->close();
echo "<br># Example 6 - Rows, Cols num <br>";
$db = Wrapper_mysql::getInstance(MySQL_HOST, MySQL_USER, MySQL_PASS, MySQL_DB);
$db->connect();
$db->query('SELECT * FROM `table`;');
$cols = $db->numFields();
$rows = $db->numRows();
echo "Cols: {$cols}, Rows: {$rows}<br />";
$db->freeResult();
$db->close();
echo "<br>* Example 6.1 - Count rows <br>";
$db = Wrapper_mysql::getInstance(MySQL_HOST, MySQL_USER, MySQL_PASS, MySQL_DB);
$db->connect();
/* Count all */
$count = $db->countRows('table');
echo $count ." rows <br>";
echo "<br># Example 6.2.1 - Count rows (Count with condition)<br>";
$count2 = $db->countRows('table', "`date` = '".date("Y-m-d")."'");
echo "Count all: {$count} registos, Count today: {$count2} registos inseridos hoje<br>";
echo "<br>* Example 6.2.2 - Count rows (Count with certain condition)<br>";
/** 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
*/
$db->countRows('table', $where = NULL);
echo $count . " registos<br>";
$db->close();
echo "<br>& Example 6.3 - Get Columns <br>";
$db = Wrapper_mysql::getInstance(MySQL_HOST, MySQL_USER, MySQL_PASS, MySQL_DB);
$db->connect();
$db->query('SELECT * FROM `table`');
echo "Table columns are: <br />";
print_r($db->getColumns('table'));
echo "<br>";
$db->close();
echo "<br># Example 7 - Next AutoIncrement (return) <br>";
$db = Wrapper_mysql::getInstance(MySQL_HOST, MySQL_USER, MySQL_PASS, MySQL_DB);
$db->connect();
$auto_increment = $db->nextAutoIncrement('table');
echo "Next auto increment id is: {$auto_increment}<br />";
$db->close();
echo "<br>* Example 8.1 - Array to update <br>";
$db = Wrapper_mysql::getInstance(MySQL_HOST, MySQL_USER, MySQL_PASS, MySQL_DB);
$db->connect();
/* Array data // [fealdname] = feald value */
$data = array();
$data['firstname'] = 'Dario';
$data['surname'] = 'Antonio';
$data['email'] = ' [email protected]';
$data['date'] = 'now()';
/* Reserved values: null, now(), curtime(), localtime(), localtime, utc_date(), utc_time(), utc_timestamp() */
$db->arrayToUpdate('table', $data, "`id` = {$insert_id}");
if ($db->affected) {
echo "Updated: {$db->affected} row(s).<br />";
}
/* Foi inserido na posição do ultimo insert id -> linha 144 eg.3.2 */
echo "<br># Example 8.2 - Array to update(multirow) <br>";
/* Array data // [fealdname] = feald value */
$data = array();
$data['id'] = 10; // key
$data['firstname'] = 'foo';
$data['surname'] = 'bar';
$data['email'] = ' [email protected]';
$data['date'] = 'now()';
/* */
$data2 = array();
$data2['id'] = 11;
$data2['firstname'] = 'Rui';
$data2['surname'] = 'Monteiro';
$data2['email'] = ' [email protected]';
$data2['date'] = 'now()';
// $db->arrayToUpdate( ... ) multirow returns TRUE on success
$db->arrayToUpdate('table', array($data, $data2 /*, $data3 .... */ ));
echo "Updated: {$db->affected} row(s) <br>";
/* Neste situação quando não existe registo na row 10 e na row 11 , o update assume-se como insert */
$db->close();
echo "<br>* Example 8.3 - Array to update (more options)<br>";
$db = Wrapper_mysql::getInstance(MySQL_HOST, MySQL_USER, MySQL_PASS, MySQL_DB);
$db->connect();
/* 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
*/
/* Array data // [fealdname] = feald value */
echo "<pre>
\$data = array();
\$data['firstname'] = 'Dario';
\$data['surname'] = 'Neves';
\$data['email'] = 'dayto@ infoscript.eu';
\$data['date'] = 'now()';
\$db->arrayToUpdate('table', \$data, \$where = NULL, \$limit = 0, \$link = 0);</pre>";
echo "<pre>
/* ATENÇÃO: Neste caso, o update afeta todas as linhas da tabela.
Actualiza todas as linhas, com este array de dados, porque a tabela não está não tem chave única definida.
Se o where for definido (diferente de null), o update afeta somente as linhas que obedecerem à condição do where.
</pre>";
echo "Updated: {$db->affected} row(s)<br>";
$db->close();
echo "<br># Example 9.1 - Delete row <br>";
$db = Wrapper_mysql::getInstance(MySQL_HOST, MySQL_USER, MySQL_PASS, MySQL_DB);
$db->connect();
$db->deleteRow('table', "`id` = {$insert_id}");
if ($db->affected) {
echo "Deleted: {$db->affected} row(s).<br />";
echo " A linha com o id = 4 foi eliminada<br>";
}
echo "<br># Example 9.2 - Delete row (More options)<br>";
/*
* 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
*/
echo "<pre>";
$db->deleteRow('table', $where = NULL, $limit = 0, $link = 0);
$db->affected;
echo "Deleted: {$db->affected} row(s).</pre>";
echo "ATENÇÃO: Neste caso, o DELETE afeta todas as linhas da tabela.<br>
Como o WHERE não foi definido (null), o delete afeta toda a tabela.<br>
Portanto, se o where for definido (diferente de null), o delete afeta somente as linhas que obedecerem à condição do where.<br>" ;
$db->close();
echo "<br><b>BASIC TABLE OPERATIONS</b><br>";
echo "<br>* Example 10 - Get table columns <br>";
$db = Wrapper_mysql::getInstance(MySQL_HOST, MySQL_USER, MySQL_PASS, MySQL_DB);
$db->connect();
echo "Table columns are:<br />";
print_r($db->getColumns('table'));
$db->close();
echo "<br><br># Example 11.1 - Copy table (with data included) <br>";
$db = Wrapper_mysql::getInstance(MySQL_HOST, MySQL_USER, MySQL_PASS, MySQL_DB);
$db->connect();
/* Como a tabela está vazia de dados, a titulo de exemplo vamos introdizir alguns dados */
$db->query("INSERT INTO `table` (`id`, `firstname`, `surname`, `email`, `date`) VALUES
(1, 'Jonh', 'knopfer', '', '2024-11-04'),
(2, 'Donald', 'Jan', ' [email protected]', '2024-12-15'),
(3, 'Antlou', 'Louc', ' [email protected]', '2025-02-23');");
$db->copyTable('table', 'table_copy');
echo "table (estrutura e dados) copiada para table_copy<br>";
/* Se a tabela já existir, o método retorna false (erro de SQL) */
echo "<br>* Example 11.2 - Copy table with data included ##<br>";
// Copy table (with data included)
$db->copyTable('table', 'table_2');
echo "table (estrutura e dados) copiada para table_2 com sucesso<br>";
echo "<br># Example 11.3 - Copy table structure <br>";
$db->copyTable('table', 'table_3', FALSE);
echo "table (estrutura) copiada para table_3 com sucesso<br>";
echo "<br>*Example 12.1 - Rename table) <br>";
$db->renameTable(array('table_copy' => 'table_copy_1'));
echo "table_copy renomeada para table_copy_1<br>";
echo "<br># Example 12.2 - Swap table names (trocar nomes de tabelas)<br>";
$db->renameTable(array('table_copy_1' => 'tmp_table', 'table_2' => 'table_4', 'tmp_table' => 'tmp_table_5'));
echo "table_copy_1 renomeada para tmp_table, table_2 renomeada para table_4, tmp_table renomeada para tmp_table_5<br>";
echo "<br>* Example 13 - Truncate table (vaziar dados)] <br>";
$db->truncateTable('table_4');
echo "Tabela table_4 truncada<br>";
/*
echo "<br># Example 14.1 - Drop one table <br>";
$db->dropTable('table_4');
echo "Tabela table_4 eliminada<br>";
*/
/*
echo "<br>* Example 14.2 - Drop multiple tables) <br>";
$db->dropTable(array('table_3', 'tmp_table_5'));
echo " Drop table_3 e tmp_table_5<br>";
$db->close();
*/
echo "<br># Example 15 - Get database size <br>";
$db = Wrapper_mysql::getInstance(MySQL_HOST, MySQL_USER, MySQL_PASS, MySQL_DB);
$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
*/
/*getDataBaseSize($sizeIn = 'MB', $round = 2, $link = 0) */
echo 'Database size is: ', $db->getDataBaseSize('mb', 2), ' MB<br>';
$db->close();
echo "<br>* Example 16 - Loging queries and errors <br>";
$db = Wrapper_mysql::getInstance(MySQL_HOST, MySQL_USER, MySQL_PASS, MySQL_DB);
$db->connect();
$db->logQueries = TRUE; /* Default is FALSE, use TRUE only for debuging */
$db->logErrors = TRUE; /* This is useful to be TRUE! */
$db->displayError = TRUE; /* Default is FALSE, use TRUE only for debuging (security reasons!) */
$db->dateFormat = "Y-m-d H:i:s"; /* Date - Time format for log */
$db->logFilePath = date("d.m.Y").'-log-mysql.txt'; /* Log file */
echo "Loging queries and errors.<br>";
$db->getColumns('table');
$db->query('SELECT * FROM `table`');
echo "Log file: {$db->logFilePath}<br>";
$db->close();
echo "<br># Example 17.1 - Export Table to CSV (Export all data)<br>";
$db = Wrapper_mysql::getInstance(MySQL_HOST, MySQL_USER, MySQL_PASS, MySQL_DB);
$db->connect();
$db->query('SELECT * FROM `table`');
echo '<pre> MUITO IMPORTANTE: Antes de exportar a tabela, verifique <b> MYSQL.conf</b> na seccão [mysqld] se a regra <b> secure-file-priv </b> existe.
Se existe ela deve estar com valor vazio/limpo; Se tem outro valor, exemplo NULL deve alterar para secure-file-priv = "" .
Se existe e estiver comentada, descomente-a, e reenicie o mysql.
Conclusão: Este deve ser o valor para <b> secure-file-priv = "" </b>. No final renicie o mysql. </pre>';
// Check if the directory exists, and create it if it doesn't
$exportDir = 'export_table';
if (!is_dir($exportDir)) {
if (!mkdir($exportDir, 0777, true)) {
die('Failed to create folders...');
}
}
/*
* 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
*/
$db->exportTable2CSV('table', 'export_table/table_1.txt');
echo "Exported table to export_table/table_1.txt<br>";
echo "<br>$ Example 17.2 - Export Table to CSV (Export two or more columns)<br>";
$db->exportTable2CSV('table', 'export_table/table_2.txt', 'firstname, surname');
echo "Exported table to export_table/table_2.txt<br>";
echo "<br>& Example 17.3 - Export Table to CSV (Export two or more columns using array)<br>";
$db->exportTable2CSV('table', 'export_table/table_3.txt', array('firstname', 'surname', 'date'));
echo "Exported table to export_table/table_3.txt<br>";
echo "<br># Example 17.4 - Export Table to CSV (Export all columns where id < 8 and limit 1, 5) <br>";
$db->exportTable2CSV('table', 'export_table/table_4.txt', '*', 'id < 8', '1,5');
echo "Exported table to export_table/table_4.txt<br>";
echo "<br>$ Example 17.5 - Export Table to CSV ( More options)<br>";
$table = 'table';
$file = 'export_table/table_5.txt';
$db->exportTable2CSV($table, $file, $columns = '*', $where = NULL, $limit = 0, $delimiter = ',', $enclosure = '"', $escape = '\\', $newLine = '\n', $showColumns = TRUE);
echo "Exported table_5.txt with more options<br>";
$db->close();
echo "<br>$ Example 18.1 - Query to CSV <br>";
$db = Wrapper_mysql::getInstance(MySQL_HOST, MySQL_USER, MySQL_PASS, MySQL_DB);
$db->connect();
/*
* 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)
$path = $db->query2CSV('select * from `table` limit 10', 'export_table/table-query2csv.csv');
echo '<br>File csv exported to : ', $path, '</br>';
echo "<br>* Example 18.2 - Query to CSV (exemplo 2)<br>";
$path = $db->query2CSV('select * from `table` limit 2,2', 'export_table/table-query3csv.csv');
echo 'File csv exported to : ', $path, '</br>';
$db->close();
echo "<br>& Example 19 - Table exists <br>";
$db = Wrapper_mysql::getInstance(MySQL_HOST, MySQL_USER, MySQL_PASS, MySQL_DB);
$db->connect();
$db->tableExists('table');
echo "Table 'table' exists bool is: ".$db->affected. "<br>";
$db->close();
echo "<br># Example 20.1 - Import CSV to Table (Import all data) <br>";
//$db = Wrapper_mysql::getInstance(MySQL_HOST, MySQL_USER, MySQL_PASS, MySQL_DB);
//$db->connect();
/* Create Table estruture */
/*$db->query("CREATE TABLE IF NOT EXISTS `table_6` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`firstname` varchar(250) NOT NULL,
`surname` varchar(250) NOT NULL,
`email` varchar(500) NOT NULL,
`date` date NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;");*/
echo '<pre>
IMPORTANTE: Se não funcionar é porque o carregamento de dados locais está desativado;
isto deve estar ativado tanto no lado do cliente como no servidor,
pois indica que a funcionalidade <b> LOAD DATA LOCAL INFILE </b> está desativada na configuração do MySQL.
Esta funcionalidade é necessária para que a <i> função importCSV2Table() </i>funcione
porque está a tentar importar um ficheiro do sistema de ficheiros local para a base de dados<br>
SET GLOBAL local_infile = 1;
SET SESSION local_infile = 1;
<br>Solução: O código tem agora uma nova secção que verifica se o local_infile está OFF.
Se estiver desativado, ativa-o temporariamente para a sessão atual. Também gera um aviso:
Aviso: "local_infile" estava desativado. Agora activado temporariamente para a sessão.
Também mostra uma mensagem indicando que a configuração do local_infile deverá ser permanentemente alterada no servidor.
<br><br>
How to fix <b>local_infile and secure_file_priv Permanently (Server-Side)</b>
1 - Edite <b>my.cnf ou my.ini</b> e adicione ou edite a seguinte linha
-> Normalmente em Linux: /etc/mysql/my.cnf ou /etc/my.cnf
-> Normalmente em Windows: C:\ProgramData\MySQL\MySQL Server X.X\my.ini ou pesquisar por my.ini
-> Abra o ficheiro com um editor de texto (como administrador).
2 - Ativar <b> local_infile </b>. Na secção [mysqld], adicione ou edite a seguinte linha
-> local-infile=1
3 - Defina <b> secure_file_priv </b> como vazio: na secção [mysqld], adicione ou edite a seguinte linha:
-> secure_file_priv = ""
4 - Salve o ficheiro e reinicie o servidor MySQL.
Lembre-se de que a ativação do local_infile tem algumas implicações de segurança, por isso certifique-se de que compreende os riscos antes de o fazer.
Se o seu ambiente exigir que seja desativado, poderá ter de encontrar um método de importação alternativo - como carregar o ficheiro primeiro para o servidor
</pre>';
/*
$db->importCSV2Table('export_table/table_1.txt', 'table_6');
echo "Imported CSV to table_6<br>"; */
// 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
*/
//echo "<br>& Example 20.2 - Import CSV to Table (more options) <br>";
/*
$file = 'export_table/table_2.txt';
$table = 'table_7';
$db->importCSV2Table($file, $table, $delimiter = ',', $enclosure = '"', $escape = '\\', $ignore = 1, $update = array(), $getColumnsFrom = 'file', $newLine = '\n');
echo "Imported CSV to table_6<br>";
$db->close();
*/
##############################
echo "<br># Example 21.1 - Create table from CSV file <br>";
$db = Wrapper_mysql::getInstance(MySQL_HOST, MySQL_USER, MySQL_PASS, MySQL_DB);
$db->connect();
//$db->dropTable('csv_to_table_test');
//$db->createTableFromCSV('export_table/countrylist.csv', 'csv_to_table_test');
//echo "Created table csv_to_table_test from CSV file<br>";
echo "<br># Example 21.2 - Create table from CSV file <br>";
//$db->dropTable('csv_to_table_test_no_column_names');
//$db->createTableFromCSV('export_table/countrylist.csv', 'csv_to_table_test1_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')
$db->close();
echo "<br># Example 22.1 - Import CSV to Table <br>";
$db = Wrapper_mysql::getInstance(MySQL_HOST, MySQL_USER, MySQL_PASS, MySQL_DB);
$db->connect();
// Import and update all data
//$db->importUpdateCSV2Table('export_table/countrylist.csv', 'csv_to_table_test');
//echo "Imported CSV to table csv_to_table_test<br>";
echo "<br># Example 22.2 - Import CSV to Table <br>";
// Import and update all data
//$db->importUpdateCSV2Table('export_table/countrylist.csv', 'csv_to_table_test2', ',', '"', '\\', 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
*/
// $db->importUpdateCSV2Table($file, $table, $delimiter = ',', $enclosure = '"', $escape = '\\', $ignore = 1, $update = array(), $getColumnsFrom = 'file', $newLine = '\n')
// Close connection
$db->close();
echo "<br># Example 23 - Transactions <br>";
$db = Wrapper_mysql::getInstance(MySQL_HOST, MySQL_USER, MySQL_PASS, MySQL_DB);
$db->connect();
//$queries = array();
//$queries[] = 'SELECT ...';
//$queries[] = 'INSERT ...';
//$queries[] = 'DELETE ...';
//$queries[] = '...';
//$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())
$db->close();
/*
echo "<br># Example 24.1 - String Search and Replace in all or defined Table Columns - Simple <br>";
$db = Wrapper_mysql::getInstance(MySQL_HOST, MySQL_USER, MySQL_PASS, MySQL_DB);
$db->connect();
// Simple
$db->strReplace('table', 'firstname', 'search', 'replace');
echo "<br># Example 24.2 - String Search and Replace in all or defined Table Columns - Search array & Replace string<br>";
// Search array & Replace string
$db->strReplace('table', 'firstname', array('search1', 'search2'), 'replace');
echo "<br># Example 24.3 - String Search and Replace in all or defined Table Columns - Search array & Replace string<br>";
// Search array & Replace array
$db->strReplace('table', 'firstname', array('search1', 'search2'), array('replace1', 'replace2'));
echo "<br># Example 24.4 - String Search and Replace in all or defined Table Columns - Search array of columns<br>";
// 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'));
echo "<br># Example 24.5 - String Search and Replace in all or defined Table Columns - String multiple columns<br>";
// String multiple columns
$db->strReplace('table', 'firstname, surname', 'search', 'replace');
echo "<br># Example 24.6 - String Search and Replace in all or defined Table Columns<br>";
// You can set all columns in table as well
$db->strReplace('table', '*', 'search', 'replace');
echo "<br># Example 24.7 - String Search and Replace in all or defined Table Columns<br>"; */
// 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);
//$db->close();
echo "<br># Example 24.1 - E-mail on error / die on error<br>";
$db = Wrapper_mysql::getInstance(MySQL_HOST, MySQL_USER, MySQL_PASS, MySQL_DB);
$db->connect();
/*$db->emailErrors = TRUE;
$db->dieOnError = TRUE;
$db->emailErrorsTo = array(' [email protected]');
$db->emailErrorsSubject = 'MySQL Error';
$db->query("select * from table"); // this one will be executed
echo "<br># Example 24.1 - E-mail on error / die on error<br>";
$db->emailErrors = TRUE;
$db->dieOnError = TRUE;
$db->emailErrorsTo = array(' [email protected]');
$db->emailErrorsSubject = 'MySQL Error';
$db->query("select * from table10"); // this one will not be executed because dieOnError = TRUE
*/
$db->close();
?>
|
Download
