Viewing file:  export.php (20.14 KB) -rw-rw-rw-
Select action/file-type:
 (+) |  (+) |  (+) | Code (+) | Session (+) |  (+) | SDB (+) |  (+) |  (+) |  (+) |  (+) |  (+) |
<?php
/*
SQL Buddy - Web based MySQL administration
http://www.sqlbuddy.com/
export.php
- export data in sql and csv formats
MIT license
2008 Calvin Lough <http://calv.in>
*/
include "functions.php";
loginCheck();
if ($_POST) {
$outputBuffer = "";
if (isset($db)) {
$dbs[] = $db;
if (isset($table))
$tables[] = $table;
else if (isset($_POST['EXPORTTABLE']))
$tables = $_POST['EXPORTTABLE'];
} else {
if (isset($_POST['EXPORTDB']))
$dbs = $_POST['EXPORTDB'];
$exportDb = true;
}
if (isset($_POST['FORMAT']))
$format = strtoupper($_POST['FORMAT']);
if (isset($_POST['STRUCTURE']))
$exportStructure = $_POST['STRUCTURE'];
if (isset($_POST['DATA']))
$exportData = $_POST['DATA'];
if (isset($_POST['DELIMITER']))
$delimiter = $_POST['DELIMITER'];
if (isset($_POST['FIELDNAMES']))
$printFieldnames = $_POST['FIELDNAMES'];
if (isset($_POST['INSERTTYPE']))
$insertType = $_POST['INSERTTYPE'];
if (isset($_POST['OUTPUT']))
$output = $_POST['OUTPUT'];
if (isset($_POST['OUTPUTFILETEXT'])) {
$outputFile = "exports/" . basename($_POST['OUTPUTFILETEXT']);
}
if (!isset($delimiter) || $delimiter == "TAB")
$delimiter = "\t";
else if ($delimiter == "SEMICOLON")
$delimiter = ";";
else if ($delimiter == "SPACE")
$delimiter = " ";
else
$delimiter = ",";
// for the next three - it has to be one or the other
// this way, if we get fed garbage, just go with a default
if (!isset($format) || $format != "CSV")
$format = "SQL";
if (!isset($output) || $output != "FILE" || !isset($outputFile))
$output = "BROWSER";
if (!isset($insertType) || $insertType != "COMPLETE")
$insertType = "COMPACT";
if (isset($format) && $format == "SQL" && !isset($exportStructure) && !isset($exportData)) {
$error = __("You must export either structure, data, or both") . ".";
} else if (!isset($dbs)) {
$error = __("Please select the databases that you would like to export") . ".";
} else if (isset($db) && !isset($tables)) {
$error = __("Please select the tables that you would like to export") . ".";
} else {
if ($format == "SQL") {
$version = $conn->getVersion();
$outputBuffer .= "--\r\n";
if ($conn->getAdapter() == "mysql")
$outputBuffer .= "-- MySQL " . $version . "\r\n";
else if ($conn->getAdapter() == "sqlite")
$outputBuffer .= "-- SQLite " . $version . "\r\n";
$outputBuffer .= "-- " . date("r") . "\r\n";
$outputBuffer .= "--\r\n\r\n";
}
foreach ($dbs as $d) {
$conn->selectDB($d);
// this checks to see if we are exporting an entire db with all tables
if (isset($exportDb) && $exportDb == true) {
if ($format == "SQL") {
$outputBuffer .= "CREATE DATABASE `$d`";
if ($conn->hasCharsetSupport())
{
$currentChar = "";
$currentCharSql = $conn->query("SHOW VARIABLES LIKE 'character_set_database'");
if ($conn->isResultSet($currentCharSql)) {
$currentChar = $conn->result($currentCharSql, 0, "Value");
$outputBuffer .= " DEFAULT CHARSET " . $currentChar;
}
}
$outputBuffer .= ";\r\n\r\n";
$outputBuffer .= "USE `$d`;\r\n\r\n";
}
$tableSql = $conn->listTables();
$tables = "";
if ($conn->isResultSet($tableSql)) {
while ($tableRow = $conn->fetchArray($tableSql)) {
$tables[] = $tableRow[0];
}
}
}
foreach ($tables as $t) {
if ($format == "SQL") {
if ($conn->getAdapter() == "mysql")
$structureSQL = $conn->query("SHOW FULL FIELDS FROM `$t`");
else
$structureSQL = $conn->describeTable($t);
$tableEngine = "";
$tableCharset = "";
$autoIncrement = "";
if (isset($exportStructure)) {
if ($conn->isResultSet($structureSQL)) {
if ($conn->getAdapter() == "mysql") {
$outputBuffer .= "CREATE TABLE `$t` (";
$infoSql = $conn->query("SHOW TABLE STATUS LIKE '$t'");
if ($conn->isResultSet($infoSql) == 1) {
$infoRow = $conn->fetchAssoc($infoSql);
$tableEngine = (array_key_exists("Type", $infoRow)) ? $infoRow['Type'] : $infoRow['Engine'];
if (array_key_exists('Collation', $infoRow) && isset($collationList)) {
$tableCharset = $collationList[$infoRow['Collation']];
}
if (array_key_exists('Auto_increment', $infoRow))
{
$autoIncrement = $infoRow['Auto_increment'];
}
}
} else if ($conn->getAdapter() == "sqlite") {
$outputBuffer .= "CREATE TABLE '$t' (";
}
$first = true;
if ($conn->getAdapter() == "mysql") {
while ($structureRow = $conn->fetchassoc($structureSQL)) {
if (!$first)
$outputBuffer .= ",";
$outputBuffer .= "\r\n `" . $structureRow['Field'] . "` " . $structureRow['Type'];
if (isset($collationList) && isset($structureRow['Collation']) && $structureRow['Collation'] != "NULL" && !is_null($structureRow['Collation'])) {
if ($collationList[$structureRow['Collation']] != $tableCharset) {
$outputBuffer .= " CHARSET " . $collationList[$structureRow['Collation']];
}
}
if (isset($structureRow['Null']) && $structureRow['Null'] != "YES")
$outputBuffer .= " not null";
if (isset($structureRow['Default']) && $structureRow['Default'] == "CURRENT_TIMESTAMP") {
$outputBuffer .= " default CURRENT_TIMESTAMP";
} else if (isset($structureRow['Default']) && $structureRow['Default'] != "") {
$outputBuffer .= " default '" . $structureRow['Default'] . "'";
}
if (isset($structureRow['Extra']) && $structureRow['Extra'] != "")
$outputBuffer .= " " . $structureRow['Extra'];
$first = false;
}
} else if ($conn->getAdapter() == "sqlite") {
foreach ($structureSQL as $structureRow) {
if (!$first)
$outputBuffer .= ",";
$outputBuffer .= "\r\n " . $structureRow[0] . " " . $structureRow[1];
$first = false;
}
}
// dont forget about the keys
if ($conn->getAdapter() == "mysql") {
$keySQL = $conn->query("SHOW INDEX FROM `$t`");
if ($conn->isResultSet($keySQL)) {
$currentKey = "";
while ($keyRow = $conn->fetchAssoc($keySQL)) {
// if this is the start of a key
if ($keyRow['Key_name'] != $currentKey) {
// finish off the last key first, if necessary
if ($currentKey != "")
$outputBuffer .= ")";
if ($keyRow['Key_name'] == "PRIMARY")
$outputBuffer .= ",\r\n PRIMARY KEY (";
elseif ($keyRow['Non_unique'] == "0")
$outputBuffer .= ",\r\n UNIQUE KEY (";
else
$outputBuffer .= ",\r\n KEY `" . $keyRow['Key_name'] . "` (";
$outputBuffer .= "`" . $keyRow['Column_name'] . "`";
} else {
$outputBuffer .= ",`" . $keyRow['Column_name'] . "`";
}
$currentKey = $keyRow['Key_name'];
}
if (isset($currentKey) && $currentKey != "")
$outputBuffer .= ")";
}
}
$outputBuffer .= "\r\n)";
if ($conn->getAdapter() == "mysql") {
if ($tableEngine) {
$outputBuffer .= ' ENGINE=' . $tableEngine;
}
if ($tableCharset) {
$outputBuffer .= ' DEFAULT CHARSET=' . $tableCharset;
}
if ($autoIncrement)
{
$outputBuffer .= ' AUTO_INCREMENT=' . $autoIncrement;
}
}
$outputBuffer .= ";\r\n\r\n";
}
}
if ($conn->getAdapter() == "mysql")
$structureSQL = $conn->query("SHOW FULL FIELDS FROM `$t`");
else
$structureSQL = $conn->describeTable($t);
if (isset($exportData)) {
$columnList = array();
if ($conn->getAdapter() == "mysql") {
$dataSQL = $conn->query("SELECT * FROM `$t`");
// put the column names in an array
if ($conn->isResultSet($structureSQL)) {
while ($structureRow = $conn->fetchAssoc($structureSQL)) {
$columnList[] = $structureRow['Field'];
$type[] = $structureRow['Type'];
}
}
$columnImplosion = implode("`, `", $columnList);
if ($conn->isResultSet($dataSQL)) {
if ($insertType == "COMPACT")
$outputBuffer .= "INSERT INTO `$t` (`$columnImplosion`) VALUES \r\n";
$firstLine = true;
while ($dataRow = $conn->fetchAssoc($dataSQL)) {
if ($insertType == "COMPLETE") {
$outputBuffer .= "INSERT INTO `$t` (`$columnImplosion`) VALUES ";
} else {
if (!$firstLine)
$outputBuffer .= ",\r\n";
}
$outputBuffer .= "(";
$first = true;
for ($i=0; $i<sizeof($columnList); $i++) {
if (!$first)
$outputBuffer .= ", ";
$currentData = $dataRow[$columnList[$i]];
if (isset($type) && $currentData && ((isset($binaryDTs) && in_array($type[$i], $binaryDTs)) || stristr($type[$i], "binary") !== false)) {
$outputBuffer .= "0x" . bin2hex($currentData);
} else {
$outputBuffer .= "'" . $conn->escapeString($currentData) . "'";
}
$first = false;
}
$outputBuffer .= ")";
if ($insertType == "COMPLETE")
$outputBuffer .= ";\r\n";
$firstLine = false;
}
if ($insertType == "COMPACT")
$outputBuffer .= ";\r\n";
} else {
$outputBuffer .= "-- [" . sprintf(__("Table `%s` is empty"), $t) . "]\r\n";
}
} else if ($conn->getAdapter() == "sqlite") {
$dataSQL = $conn->query("SELECT * FROM '$t'");
// put the column names in an array
if ($conn->isResultSet($structureSQL)) {
foreach ($structureSQL as $structureRow) {
$columnList[] = $structureRow[0];
$type[] = $structureRow[1];
}
}
$columnImplosion = implode("', '", $columnList);
if ($conn->isResultSet($dataSQL)) {
$firstLine = true;
while ($dataRow = $conn->fetchAssoc($dataSQL)) {
$outputBuffer .= "INSERT INTO '$t' ('$columnImplosion') VALUES (";
$first = true;
for ($i=0; $i<sizeof($columnList); $i++) {
if (!$first)
$outputBuffer .= ", ";
$currentData = $dataRow[$columnList[$i]];
$outputBuffer .= "'" . $conn->escapeString($currentData) . "'";
$first = false;
}
$outputBuffer .= ");\r\n";
$firstLine = false;
}
} else {
$outputBuffer .= "-- [" . sprintf(__("Table `%s` is empty"), $t) . "]\r\n";
}
}
}
$outputBuffer .= "\r\n";
} else if ($format == "CSV") {
if (isset($printFieldnames)) {
$structureSQL = $conn->describeTable($t);
if ($conn->isResultSet($structureSQL)) {
$first = true;
if ($conn->getAdapter() == "mysql") {
while ($structureRow = $conn->fetchArray($structureSQL)) {
if (!$first)
$outputBuffer .= $delimiter;
$outputBuffer .= "\"" . $structureRow[0] . "\"";
$first = false;
}
} else if ($conn->getAdapter() == "sqlite") {
foreach ($structureSQL as $structureRow) {
if (!$first)
$outputBuffer .= $delimiter;
$outputBuffer .= "\"" . $structureRow[0] . "\"";
$first = false;
}
}
$outputBuffer .= "\r\n";
}
}
if ($conn->getAdapter() == "mysql") {
$dataSQL = $conn->query("SELECT * FROM `$t`");
} else if ($conn->getAdapter() == "sqlite") {
$dataSQL = $conn->query("SELECT * FROM '$t'");
}
if ($conn->isResultSet($dataSQL)) {
while ($dataRow = $conn->fetchArray($dataSQL)) {
$data = array();
foreach ($dataRow as $each) {
$data[] = "\"" . formatDataForCSV($each) . "\"";
}
$dataLine = implode($delimiter, $data);
$outputBuffer .= $dataLine . "\r\n";
}
}
}
}
}
$outputBuffer = trim($outputBuffer);
if ($outputBuffer) {
if ($output == "BROWSER") {
echo "<div id=\"EXPORTWRAPPER\">";
echo "<strong>" . __("Results:") . "</strong> [<a onclick=\"$('EXPORTRESULTS').select()\">" . __("Select all") . "</a>]";
echo "<textarea id=\"EXPORTRESULTS\">" . htmlentities($outputBuffer, ENT_QUOTES, 'UTF-8') . "</textarea>";
echo "</div>";
} else {
if (!$handle = @fopen($outputFile, "w")) {
$error = __("The file could not be opened") . ".";
} else {
if (fwrite($handle, $outputBuffer) === false) {
$error = __("Could not write to file") . ".";
} else {
echo '<div style="margin: 10px 12px 5px 14px; color: rgb(100, 100, 100)">';
echo __("Successfully wrote content to file") . '. <a href="' . $outputFile . '">' . __("Download") . '</a><br /><strong>' . __("Note") . ':</strong> ' . __("If this is a public server, you should delete this file from the server after you download it") . '.</div>';
}
}
@fclose($handle);
}
}
}
}
if (isset($error)) {
echo '<div class="errormessage" style="margin: 14px 12px 7px 14px; width: 340px">' . $error . '</div>';
}
?>
<div class="export">
<h4><?php echo __("Export"); ?></h4>
<form id="EXPORTFORM" onsubmit="submitForm('EXPORTFORM'); return false">
<table cellpadding="0">
<?php
$showSeperator = false;
if (isset($db) && !isset($table)) {
?>
<tr>
<td class="secondaryheader"><?php echo __("Tables"); ?>:<br /> <a onclick="selectAll('exportTable')"><?php echo __("All"); ?></a> / <a onclick="selectNone('exportTable')"><?php echo __("None"); ?></a></td>
<td>
<select name="EXPORTTABLE[]" id="exportTable" multiple="multiple" size="10">
<?php
$conn->selectDB($db);
$tableSql = $conn->listTables();
if ($conn->isResultSet($tableSql)) {
while ($tableRow = $conn->fetchArray($tableSql)) {
echo '<option value="' . $tableRow[0] . '"';
if (isset($tables) && in_array($tableRow[0], $tables))
echo ' selected="selected"';
echo '>' . $tableRow[0] . '</option>';
}
}
?>
</select>
</td>
</tr>
<?php
$showSeperator = true;
} else if (!isset($db) && $conn->getAdapter() != "sqlite") {
?>
<tr>
<td class="secondaryheader"><?php echo __("Databases"); ?>:<br /> <a onclick="selectAll('exportDb')"><?php echo __("All"); ?></a> / <a onclick="selectNone('exportDb')"><?php echo __("None"); ?></a></td>
<td>
<select name="EXPORTDB[]" id="exportDb" multiple="multiple" size="10">
<?php
$dbSql = $conn->listDatabases();
if ($conn->isResultSet($dbSql)) {
while ($dbRow = $conn->fetchArray($dbSql)) {
if ($dbRow[0] != "information_schema")
{
echo '<option value="' . $dbRow[0] . '"';
if (isset($dbs) && in_array($dbRow[0], $dbs))
echo ' selected="selected"';
echo '>' . $dbRow[0] . '</option>';
}
}
}
?>
</select>
</td>
</tr>
<?php
$showSeperator = true;
} else if (isset($db) && isset($table)) {
?>
<tr>
<td class="secondaryheader"><?php echo __("Format"); ?>:</td>
<td>
<label><input type="radio" name="FORMAT" id="SQLTOGGLE" value="SQL" onchange="updatePane('SQLTOGGLE', 'sqlpane', 'csvpane')" onclick="updatePane('SQLTOGGLE', 'sqlpane', 'csvpane')" <?php if ((isset($format) && $format == "SQL")|| !isset($format)) echo 'checked="checked"'; ?> /><?php echo __("SQL"); ?></label><br />
<label><input type="radio" name="FORMAT" value="CSV" onchange="updatePane('SQLTOGGLE', 'sqlpane', 'csvpane')" onclick="updatePane('SQLTOGGLE', 'sqlpane', 'csvpane')" <?php if (isset($format) && $format == "CSV") echo 'checked="checked"'; ?> /><?php echo __("CSV"); ?></label>
</td>
</tr>
<?php
$showSeperator = true;
}
?>
</table>
<?php
if ($showSeperator)
echo '<div class="exportseperator"></div>';
?>
<table cellpadding="0" id="sqlpane"<?php if (isset($format) && $format == "CSV") echo ' style="display: none"'; ?>>
<tr>
<td class="secondaryheader"><?php echo __("Export"); ?>:</td>
<td>
<label><input type="checkbox" name="STRUCTURE" value="STRUCTURE" <?php if (isset($exportStructure) || !($_POST)) echo 'checked="checked"'; ?> /><?php echo __("Structure"); ?></label><br />
<label><input type="checkbox" name="DATA" value="DATA" <?php if (isset($exportData) || !($_POST)) echo 'checked="checked"'; ?> /><?php echo __("Data"); ?></label>
</td>
</tr>
<?php
if ($conn->getAdapter() == "mysql") {
?>
<tr>
<td class="secondaryheader"><?php echo __("Options"); ?>:</td>
<td>
<label><input type="radio" name="INSERTTYPE" value="COMPACT" <?php if ((isset($insertType) && $insertType == "COMPACT") || !isset($insertType)) echo 'checked="checked"'; ?> /><?php echo __("Compact inserts"); ?></label><br />
<label><input type="radio" name="INSERTTYPE" value="COMPLETE" <?php if (isset($insertType) && $insertType == "COMPLETE") echo 'checked="checked"'; ?> /><?php echo __("Complete inserts"); ?></label>
</td>
</tr>
<?php
}
?>
</table>
<table cellpadding="0" id="csvpane"<?php if ((isset($format) && $format == "SQL") || !isset($format)) echo ' style="display: none"'; ?>>
<tr>
<td class="secondaryheader"><?php echo __("Delimiter"); ?>:</td>
<td>
<label><input type="radio" name="DELIMITER" value="COMMA"<?php if (isset($delimiter) && $delimiter == "," || !isset($delimiter)) echo ' checked="checked"'; ?> /><?php echo __("Comma"); ?></label><br />
<label><input type="radio" name="DELIMITER" value="TAB"<?php if (isset($delimiter) && $delimiter == "\t") echo ' checked="checked"'; ?> /><?php echo __("Tab"); ?></label><br />
<label><input type="radio" name="DELIMITER" value="SEMICOLON"<?php if (isset($delimiter) && $delimiter == ";") echo ' checked="checked"'; ?> /><?php echo __("Semicolon"); ?></label><br />
<label><input type="radio" name="DELIMITER" value="SPACE"<?php if (isset($delimiter) && $delimiter == " ") echo ' checked="checked"'; ?> /><?php echo __("Space"); ?></label>
</td>
</tr>
<tr>
<td class="secondaryheader"><?php echo __("Options"); ?>:</td>
<td>
<label><input type="checkbox" name="FIELDNAMES" value="TRUE"<?php if (isset($printFieldnames)) echo ' checked="checked"'; ?> /><?php echo __("Print field names on first line"); ?></label><br />
</td>
</tr>
</table>
<div class="exportseperator"></div>
<table cellpadding="0">
<tr>
<td class="message" colspan="2">
<?php echo __("If you are exporting a large number of rows, it is recommended that you output the results to a text file"); ?>.
</td>
</tr>
<tr>
<td class="secondaryheader"><?php echo __("Output to"); ?>:</td>
<td>
<label><input type="radio" name="OUTPUT" value="BROWSER"<?php if (isset($output) && $output == "BROWSER" || !isset($output)) echo ' checked="checked"'; ?> /><?php echo __("Browser"); ?></label><br />
<label><input type="radio" name="OUTPUT" id="OUTPUTFILE" value="FILE" onchange="exportFilePrep()"<?php if (isset($output) && $output == "FILE") echo ' checked="checked"'; ?> /><?php echo __("Text file"); ?>:</label><input type="text" class="text" name="OUTPUTFILETEXT" id="OUTPUTFILETEXT" value="<?php if (isset($outputFile)){ echo basename($outputFile); } else if (isset($format) && $format == "CSV") { echo strtolower(__("Export")) . ".csv"; } else { echo strtolower(__("Export")) . ".sql"; } ?>" style="vertical-align: middle; margin-left: 5px" />
</td>
</tr>
<tr>
<td colspan="2"><input type="submit" class="inputbutton" value="<?php echo __("Submit"); ?>" /></td>
</tr>
</table>
</form>
</div>
|