/**
* Outputs the content of a table in CSV format
*
* @param string the database name
* @param string the table name
* @param string the end of line sequence
* @param string the url to go back in case of error
* @param string SQL query for obtaining data
*
* @return bool Whether it suceeded
*
* @access public
*/
function PMA_exportData($db, $table, $crlf, $error_url, $sql_query)
{
global $what;
// Gets the data from the database
$result = PMA_DBI_query($sql_query, null, PMA_DBI_QUERY_UNBUFFERED);
$fields_cnt = PMA_DBI_num_fields($result);
$fields_meta = PMA_DBI_get_fields_meta($result);
$field_flags = array();
for ($j = 0; $j < $fields_cnt; $j++) {
$field_flags[$j] = PMA_DBI_field_flags($result, $j);
}
$GLOBALS['odt_buffer'] .= '<text:h text:outline-level="2" text:style-name="Heading_2" text:is-list-header="true">' . htmlspecialchars(__('Dumping data for table') . ' ' . $table) . '</text:h>';
$GLOBALS['odt_buffer'] .= '<table:table table:name="' . htmlspecialchars($table) . '_structure">';
$GLOBALS['odt_buffer'] .= '<table:table-column table:number-columns-repeated="' . $fields_cnt . '"/>';
// If required, get fields name at the first line
if (isset($GLOBALS[$what . '_columns'])) {
$GLOBALS['odt_buffer'] .= '<table:table-row>';
for ($i = 0; $i < $fields_cnt; $i++) {
$GLOBALS['odt_buffer'] .= '<table:table-cell office:value-type="string">' . '<text:p>' . htmlspecialchars(stripslashes(PMA_DBI_field_name($result, $i))) . '</text:p>' . '</table:table-cell>';
}
// end for
$GLOBALS['odt_buffer'] .= '</table:table-row>';
}
// end if
// Format the data
while ($row = PMA_DBI_fetch_row($result)) {
$GLOBALS['odt_buffer'] .= '<table:table-row>';
for ($j = 0; $j < $fields_cnt; $j++) {
if (!isset($row[$j]) || is_null($row[$j])) {
$GLOBALS['odt_buffer'] .= '<table:table-cell office:value-type="string">' . '<text:p>' . htmlspecialchars($GLOBALS[$what . '_null']) . '</text:p>' . '</table:table-cell>';
// ignore BLOB
} elseif (stristr($field_flags[$j], 'BINARY') && $fields_meta[$j]->blob) {
$GLOBALS['odt_buffer'] .= '<table:table-cell office:value-type="string">' . '<text:p></text:p>' . '</table:table-cell>';
} elseif ($fields_meta[$j]->numeric && $fields_meta[$j]->type != 'timestamp' && !$fields_meta[$j]->blob) {
$GLOBALS['odt_buffer'] .= '<table:table-cell office:value-type="float" office:value="' . $row[$j] . '" >' . '<text:p>' . htmlspecialchars($row[$j]) . '</text:p>' . '</table:table-cell>';
} else {
$GLOBALS['odt_buffer'] .= '<table:table-cell office:value-type="string">' . '<text:p>' . htmlspecialchars($row[$j]) . '</text:p>' . '</table:table-cell>';
}
}
// end for
$GLOBALS['odt_buffer'] .= '</table:table-row>';
}
// end while
PMA_DBI_free_result($result);
$GLOBALS['odt_buffer'] .= '</table:table>';
return TRUE;
}
/**
* Outputs the content of a table in CSV format
*
* @param string the database name
* @param string the table name
* @param string the end of line sequence
* @param string the url to go back in case of error
* @param string SQL query for obtaining data
*
* @return bool Whether it suceeded
*
* @access public
*/
function PMA_exportData($db, $table, $crlf, $error_url, $sql_query)
{
global $what;
global $add_character;
global $separator;
global $enclosed;
global $escaped;
// Gets the data from the database
$result = PMA_DBI_query($sql_query, NULL, PMA_DBI_QUERY_UNBUFFERED);
$fields_cnt = PMA_DBI_num_fields($result);
// If required, get fields name at the first line
if (isset($GLOBALS['showcsvnames']) && $GLOBALS['showcsvnames'] == 'yes') {
$schema_insert = '';
for ($i = 0; $i < $fields_cnt; $i++) {
if ($enclosed == '') {
$schema_insert .= stripslashes(PMA_DBI_field_name($result, $i));
} else {
$schema_insert .= $enclosed . str_replace($enclosed, $escaped . $enclosed, stripslashes(PMA_DBI_field_name($result, $i))) . $enclosed;
}
$schema_insert .= $separator;
}
// end for
$schema_insert = trim(substr($schema_insert, 0, -1));
if (!PMA_exportOutputHandler($schema_insert . $add_character)) {
return FALSE;
}
}
// end if
// Format the data
while ($row = PMA_DBI_fetch_row($result)) {
$schema_insert = '';
for ($j = 0; $j < $fields_cnt; $j++) {
if (!isset($row[$j]) || is_null($row[$j])) {
$schema_insert .= $GLOBALS[$what . '_replace_null'];
} else {
if ($row[$j] == '0' || $row[$j] != '') {
// loic1 : always enclose fields
if ($what == 'excel') {
$row[$j] = ereg_replace("\r(\n)?", "\n", $row[$j]);
}
if ($enclosed == '') {
$schema_insert .= $row[$j];
} else {
$schema_insert .= $enclosed . str_replace($enclosed, $escaped . $enclosed, $row[$j]) . $enclosed;
}
} else {
$schema_insert .= '';
}
}
if ($j < $fields_cnt - 1) {
$schema_insert .= $separator;
}
}
// end for
if (!PMA_exportOutputHandler($schema_insert . $add_character)) {
return FALSE;
}
}
// end while
PMA_DBI_free_result($result);
return TRUE;
}
/**
* Outputs the content of a table in MediaWiki format
*
* @param string $db database name
* @param string $table table name
* @param string $crlf the end of line sequence
* @param string $error_url the url to go back in case of error
* @param string $sql_query SQL query for obtaining data
*
* @return bool Whether it succeeded
*/
public function exportData($db, $table, $crlf, $error_url, $sql_query)
{
// Print data comment
$output = $this->_exportComment("Table data for " . PMA_CommonFunctions::getInstance()->backquote($table));
// Begin the table construction
// Use the "wikitable" class for style
// Use the "sortable" class for allowing tables to be sorted by column
$output .= "{| class=\"wikitable sortable\" style=\"text-align:center;\"" . $this->_exportCRLF();
// Add the table name
if ($GLOBALS['mediawiki_caption']) {
$output .= "|+'''" . $table . "'''" . $this->_exportCRLF();
}
// Add the table headers
if ($GLOBALS['mediawiki_headers']) {
// Get column names
$column_names = PMA_DBI_get_column_names($db, $table);
// Add column names as table headers
if (!is_null($column_names)) {
// Use '|-' for separating rows
$output .= "|-" . $this->_exportCRLF();
// Use '!' for separating table headers
foreach ($column_names as $column) {
$output .= " ! " . $column . "" . $this->_exportCRLF();
}
}
}
// Get the table data from the database
$result = PMA_DBI_query($sql_query, null, PMA_DBI_QUERY_UNBUFFERED);
$fields_cnt = PMA_DBI_num_fields($result);
while ($row = PMA_DBI_fetch_row($result)) {
$output .= "|-" . $this->_exportCRLF();
// Use '|' for separating table columns
for ($i = 0; $i < $fields_cnt; ++$i) {
$output .= " | " . $row[$i] . "" . $this->_exportCRLF();
}
}
// End table construction
$output .= "|}" . str_repeat($this->_exportCRLF(), 2);
return PMA_exportOutputHandler($output);
}
/**
* returns all rows in the resultset in one array
*
* <code>
* $sql = 'SELECT * FROM `user`';
* $users = PMA_DBI_fetch_result($sql);
* // produces
* // $users[] = array('id' => 123, 'name' => 'John Doe')
*
* $sql = 'SELECT `id`, `name` FROM `user`';
* $users = PMA_DBI_fetch_result($sql, 'id');
* // produces
* // $users['123'] = array('id' => 123, 'name' => 'John Doe')
*
* $sql = 'SELECT `id`, `name` FROM `user`';
* $users = PMA_DBI_fetch_result($sql, 0);
* // produces
* // $users['123'] = array(0 => 123, 1 => 'John Doe')
*
* $sql = 'SELECT `id`, `name` FROM `user`';
* $users = PMA_DBI_fetch_result($sql, 'id', 'name');
* // or
* $users = PMA_DBI_fetch_result($sql, 0, 1);
* // produces
* // $users['123'] = 'John Doe'
*
* $sql = 'SELECT `name` FROM `user`';
* $users = PMA_DBI_fetch_result($sql);
* // produces
* // $users[] = 'John Doe'
*
* $sql = 'SELECT `group`, `name` FROM `user`'
* $users = PMA_DBI_fetch_result($sql, array('group', null), 'name');
* // produces
* // $users['admin'][] = 'John Doe'
*
* $sql = 'SELECT `group`, `name` FROM `user`'
* $users = PMA_DBI_fetch_result($sql, array('group', 'name'), 'id');
* // produces
* // $users['admin']['John Doe'] = '123'
* </code>
*
* @uses is_string()
* @uses is_int()
* @uses PMA_DBI_try_query()
* @uses PMA_DBI_num_rows()
* @uses PMA_DBI_num_fields()
* @uses PMA_DBI_fetch_row()
* @uses PMA_DBI_fetch_assoc()
* @uses PMA_DBI_free_result()
* @param string|mysql_result $result query or mysql result
* @param string|integer $key field-name or offset
* used as key for array
* @param string|integer $value value-name or offset
* used as value for array
* @param resource $link mysql link
* @param mixed $options
* @return array resultrows or values indexed by $key
*/
function PMA_DBI_fetch_result($result, $key = null, $value = null, $link = null, $options = 0)
{
$resultrows = array();
if (is_string($result)) {
$result = PMA_DBI_try_query($result, $link, $options);
}
// return empty array if result is empty or false
if (!$result) {
return $resultrows;
}
$fetch_function = 'PMA_DBI_fetch_assoc';
// no nested array if only one field is in result
if (null === $key && 1 === PMA_DBI_num_fields($result)) {
$value = 0;
$fetch_function = 'PMA_DBI_fetch_row';
}
// if $key is an integer use non associative mysql fetch function
if (is_int($key)) {
$fetch_function = 'PMA_DBI_fetch_row';
}
if (null === $key && null === $value) {
while ($row = $fetch_function($result)) {
$resultrows[] = $row;
}
} elseif (null === $key) {
while ($row = $fetch_function($result)) {
$resultrows[] = $row[$value];
}
} elseif (null === $value) {
if (is_array($key)) {
while ($row = $fetch_function($result)) {
$result_target =& $resultrows;
foreach ($key as $key_index) {
if (null === $key_index) {
$result_target =& $result_target[];
continue;
}
if (!isset($result_target[$row[$key_index]])) {
$result_target[$row[$key_index]] = array();
}
$result_target =& $result_target[$row[$key_index]];
}
$result_target = $row;
}
} else {
while ($row = $fetch_function($result)) {
$resultrows[$row[$key]] = $row;
}
}
} else {
if (is_array($key)) {
while ($row = $fetch_function($result)) {
$result_target =& $resultrows;
foreach ($key as $key_index) {
if (null === $key_index) {
$result_target =& $result_target[];
continue;
}
if (!isset($result_target[$row[$key_index]])) {
$result_target[$row[$key_index]] = array();
}
$result_target =& $result_target[$row[$key_index]];
}
$result_target = $row[$value];
}
} else {
while ($row = $fetch_function($result)) {
$resultrows[$row[$key]] = $row[$value];
}
}
}
PMA_DBI_free_result($result);
return $resultrows;
}
/**
* Outputs the content of a table in CSV format
*
* @param string $db database name
* @param string $table table name
* @param string $crlf the end of line sequence
* @param string $error_url the url to go back in case of error
* @param string $sql_query SQL query for obtaining data
* @return bool Whether it succeeded
*
* @access public
*/
function PMA_exportData($db, $table, $crlf, $error_url, $sql_query)
{
global $what;
global $csv_terminated;
global $csv_separator;
global $csv_enclosed;
global $csv_escaped;
// Gets the data from the database
$result = PMA_DBI_query($sql_query, null, PMA_DBI_QUERY_UNBUFFERED);
$fields_cnt = PMA_DBI_num_fields($result);
// If required, get fields name at the first line
if (isset($GLOBALS['csv_columns'])) {
$schema_insert = '';
for ($i = 0; $i < $fields_cnt; $i++) {
if ($csv_enclosed == '') {
$schema_insert .= stripslashes(PMA_DBI_field_name($result, $i));
} else {
$schema_insert .= $csv_enclosed . str_replace($csv_enclosed, $csv_escaped . $csv_enclosed, stripslashes(PMA_DBI_field_name($result, $i))) . $csv_enclosed;
}
$schema_insert .= $csv_separator;
}
// end for
$schema_insert = trim(substr($schema_insert, 0, -1));
if (!PMA_exportOutputHandler($schema_insert . $csv_terminated)) {
return false;
}
}
// end if
// Format the data
while ($row = PMA_DBI_fetch_row($result)) {
$schema_insert = '';
for ($j = 0; $j < $fields_cnt; $j++) {
if (!isset($row[$j]) || is_null($row[$j])) {
$schema_insert .= $GLOBALS[$what . '_null'];
} elseif ($row[$j] == '0' || $row[$j] != '') {
// always enclose fields
if ($what == 'excel') {
$row[$j] = preg_replace("/\r(\n)?/", "\n", $row[$j]);
}
// remove CRLF characters within field
if (isset($GLOBALS[$what . '_removeCRLF']) && $GLOBALS[$what . '_removeCRLF']) {
$row[$j] = str_replace("\n", "", str_replace("\r", "", $row[$j]));
}
if ($csv_enclosed == '') {
$schema_insert .= $row[$j];
} else {
// also double the escape string if found in the data
if ($csv_escaped != $csv_enclosed) {
$schema_insert .= $csv_enclosed . str_replace($csv_enclosed, $csv_escaped . $csv_enclosed, str_replace($csv_escaped, $csv_escaped . $csv_escaped, $row[$j])) . $csv_enclosed;
} else {
// avoid a problem when escape string equals enclose
$schema_insert .= $csv_enclosed . str_replace($csv_enclosed, $csv_escaped . $csv_enclosed, $row[$j]) . $csv_enclosed;
}
}
} else {
$schema_insert .= '';
}
if ($j < $fields_cnt - 1) {
$schema_insert .= $csv_separator;
}
}
// end for
if (!PMA_exportOutputHandler($schema_insert . $csv_terminated)) {
return false;
}
}
// end while
PMA_DBI_free_result($result);
return true;
}
/**
* Outputs the content of a table in CSV format
*
* @param string the database name
* @param string the table name
* @param string the end of line sequence
* @param string the url to go back in case of error
* @param string SQL query for obtaining data
*
* @return bool Whether it suceeded
*
* @access public
*/
function PMA_exportData($db, $table, $crlf, $error_url, $sql_query)
{
global $what;
// Gets the data from the database
$result = PMA_DBI_query($sql_query, null, PMA_DBI_QUERY_UNBUFFERED);
$fields_cnt = PMA_DBI_num_fields($result);
// If required, get fields name at the first line
if (isset($GLOBALS[$what . '_shownames']) && $GLOBALS[$what . '_shownames'] == 'yes') {
$schema_insert = '<tr>';
for ($i = 0; $i < $fields_cnt; $i++) {
$schema_insert .= '<td class=xl2216681 nowrap><b>' . htmlspecialchars(stripslashes(PMA_DBI_field_name($result, $i))) . '</b></td>';
}
// end for
$schema_insert .= '</tr>';
if (!PMA_exportOutputHandler($schema_insert)) {
return FALSE;
}
}
// end if
// Format the data
while ($row = PMA_DBI_fetch_row($result)) {
$schema_insert = '<tr>';
for ($j = 0; $j < $fields_cnt; $j++) {
if (!isset($row[$j]) || is_null($row[$j])) {
$value = $GLOBALS[$what . '_replace_null'];
} elseif ($row[$j] == '0' || $row[$j] != '') {
$value = $row[$j];
} else {
$value = '';
}
$schema_insert .= '<td class=xl2216681 nowrap>' . htmlspecialchars($value) . '</td>';
}
// end for
$schema_insert .= '</tr>';
if (!PMA_exportOutputHandler($schema_insert)) {
return FALSE;
}
}
// end while
PMA_DBI_free_result($result);
return TRUE;
}
/**
* Outputs the content of a table in CSV format
*
* @param string the database name
* @param string the table name
* @param string the end of line sequence
* @param string the url to go back in case of error
* @param string SQL query for obtaining data
*
* @return bool Whether it suceeded
*
* @access public
*/
function PMA_exportData($db, $table, $crlf, $error_url, $sql_query)
{
global $what;
if (!PMA_exportOutputHandler('<h2>' . $GLOBALS['strDumpingData'] . ' ' . $table . '</h2>')) {
return FALSE;
}
if (!PMA_exportOutputHandler('<table class="width100" cellspacing="1">')) {
return FALSE;
}
// Gets the data from the database
$result = PMA_DBI_query($sql_query, null, PMA_DBI_QUERY_UNBUFFERED);
$fields_cnt = PMA_DBI_num_fields($result);
// If required, get fields name at the first line
if (isset($GLOBALS['htmlword_columns'])) {
$schema_insert = '<tr class="print-category">';
for ($i = 0; $i < $fields_cnt; $i++) {
$schema_insert .= '<td class="print"><b>' . htmlspecialchars(stripslashes(PMA_DBI_field_name($result, $i))) . '</b></td>';
}
// end for
$schema_insert .= '</tr>';
if (!PMA_exportOutputHandler($schema_insert)) {
return FALSE;
}
}
// end if
// Format the data
while ($row = PMA_DBI_fetch_row($result)) {
$schema_insert = '<tr class="print-category">';
for ($j = 0; $j < $fields_cnt; $j++) {
if (!isset($row[$j]) || is_null($row[$j])) {
$value = $GLOBALS[$what . '_null'];
} elseif ($row[$j] == '0' || $row[$j] != '') {
$value = $row[$j];
} else {
$value = '';
}
$schema_insert .= '<td class="print">' . htmlspecialchars($value) . '</td>';
}
// end for
$schema_insert .= '</tr>';
if (!PMA_exportOutputHandler($schema_insert)) {
return FALSE;
}
}
// end while
PMA_DBI_free_result($result);
if (!PMA_exportOutputHandler('</table>')) {
return FALSE;
}
return TRUE;
}
/**
* Outputs the content of a table in ODS format
*
* @param string $db database name
* @param string $table table name
* @param string $crlf the end of line sequence
* @param string $error_url the url to go back in case of error
* @param string $sql_query SQL query for obtaining data
* @return bool Whether it succeeded
*
* @access public
*/
function PMA_exportData($db, $table, $crlf, $error_url, $sql_query)
{
global $what;
// Gets the data from the database
$result = PMA_DBI_query($sql_query, null, PMA_DBI_QUERY_UNBUFFERED);
$fields_cnt = PMA_DBI_num_fields($result);
$fields_meta = PMA_DBI_get_fields_meta($result);
$field_flags = array();
for ($j = 0; $j < $fields_cnt; $j++) {
$field_flags[$j] = PMA_DBI_field_flags($result, $j);
}
$GLOBALS['ods_buffer'] .= '<table:table table:name="' . htmlspecialchars($table) . '">';
// If required, get fields name at the first line
if (isset($GLOBALS[$what . '_columns'])) {
$GLOBALS['ods_buffer'] .= '<table:table-row>';
for ($i = 0; $i < $fields_cnt; $i++) {
$GLOBALS['ods_buffer'] .= '<table:table-cell office:value-type="string">' . '<text:p>' . htmlspecialchars(stripslashes(PMA_DBI_field_name($result, $i))) . '</text:p>' . '</table:table-cell>';
}
// end for
$GLOBALS['ods_buffer'] .= '</table:table-row>';
}
// end if
// Format the data
while ($row = PMA_DBI_fetch_row($result)) {
$GLOBALS['ods_buffer'] .= '<table:table-row>';
for ($j = 0; $j < $fields_cnt; $j++) {
if (!isset($row[$j]) || is_null($row[$j])) {
$GLOBALS['ods_buffer'] .= '<table:table-cell office:value-type="string">' . '<text:p>' . htmlspecialchars($GLOBALS[$what . '_null']) . '</text:p>' . '</table:table-cell>';
// ignore BLOB
} elseif (stristr($field_flags[$j], 'BINARY') && $fields_meta[$j]->blob) {
$GLOBALS['ods_buffer'] .= '<table:table-cell office:value-type="string">' . '<text:p></text:p>' . '</table:table-cell>';
} elseif ($fields_meta[$j]->type == "date") {
$GLOBALS['ods_buffer'] .= '<table:table-cell office:value-type="date" office:date-value="' . date("Y-m-d", strtotime($row[$j])) . '" table:style-name="DateCell">' . '<text:p>' . htmlspecialchars($row[$j]) . '</text:p>' . '</table:table-cell>';
} elseif ($fields_meta[$j]->type == "time") {
$GLOBALS['ods_buffer'] .= '<table:table-cell office:value-type="time" office:time-value="' . date("\\P\\TH\\Hi\\Ms\\S", strtotime($row[$j])) . '" table:style-name="TimeCell">' . '<text:p>' . htmlspecialchars($row[$j]) . '</text:p>' . '</table:table-cell>';
} elseif ($fields_meta[$j]->type == "datetime") {
$GLOBALS['ods_buffer'] .= '<table:table-cell office:value-type="date" office:date-value="' . date("Y-m-d\\TH:i:s", strtotime($row[$j])) . '" table:style-name="DateTimeCell">' . '<text:p>' . htmlspecialchars($row[$j]) . '</text:p>' . '</table:table-cell>';
} elseif ($fields_meta[$j]->numeric && $fields_meta[$j]->type != 'timestamp' && !$fields_meta[$j]->blob) {
$GLOBALS['ods_buffer'] .= '<table:table-cell office:value-type="float" office:value="' . $row[$j] . '" >' . '<text:p>' . htmlspecialchars($row[$j]) . '</text:p>' . '</table:table-cell>';
} else {
$GLOBALS['ods_buffer'] .= '<table:table-cell office:value-type="string">' . '<text:p>' . htmlspecialchars($row[$j]) . '</text:p>' . '</table:table-cell>';
}
}
// end for
$GLOBALS['ods_buffer'] .= '</table:table-row>';
}
// end while
PMA_DBI_free_result($result);
$GLOBALS['ods_buffer'] .= '</table:table>';
return true;
}
/**
* Outputs the content of a table in YAML format
*
* @param string the database name
* @param string the table name
* @param string the end of line sequence
* @param string the url to go back in case of error
* @param string SQL query for obtaining data
*
* @return bool Whether it suceeded
*
* @access public
*/
function PMA_exportData($db, $table, $crlf, $error_url, $sql_query)
{
$result = PMA_DBI_query($sql_query, null, PMA_DBI_QUERY_UNBUFFERED);
$columns_cnt = PMA_DBI_num_fields($result);
for ($i = 0; $i < $columns_cnt; $i++) {
$columns[$i] = stripslashes(PMA_DBI_field_name($result, $i));
}
unset($i);
// fix variable names (based on http://www.php.net/manual/language.variables.basics.php)
if (preg_match('/^[a-zA-Z_\\x7f-\\xff][a-zA-Z0-9_\\x7f-\\xff]*$/', $table) == false) {
// fix invalid chars in variable names by replacing them with underscores
$tablefixed = preg_replace('/[^a-zA-Z0-9_\\x7f-\\xff]/', '_', $table);
// variable name must not start with a number or dash...
if (preg_match('/^[a-zA-Z_\\x7f-\\xff]/', $tablefixed) == false) {
$tablefixed = '_' . $tablefixed;
}
} else {
$tablefixed = $table;
}
$buffer = '';
$record_cnt = 0;
while ($record = PMA_DBI_fetch_row($result)) {
$record_cnt++;
// Output table name as comment if this is the first record of the table
if ($record_cnt == 1) {
$buffer .= $crlf . '// ' . PMA_backquote($db) . '.' . PMA_backquote($table) . $crlf;
$buffer .= '$' . $tablefixed . ' = array(' . $crlf;
$buffer .= ' array(';
} else {
$buffer .= ',' . $crlf . ' array(';
}
for ($i = 0; $i < $columns_cnt; $i++) {
$buffer .= var_export($columns[$i], true) . " => " . var_export($record[$i], true) . ($i + 1 >= $columns_cnt ? '' : ',');
}
$buffer .= ')';
}
$buffer .= $crlf . ');' . $crlf;
if (!PMA_exportOutputHandler($buffer)) {
return FALSE;
}
PMA_DBI_free_result($result);
return true;
}
/**
* Outputs the content of a table in YAML format
*
* @param string the database name
* @param string the table name
* @param string the end of line sequence
* @param string the url to go back in case of error
* @param string SQL query for obtaining data
*
* @return bool Whether it suceeded
*
* @access public
*/
function PMA_exportData($db, $table, $crlf, $error_url, $sql_query)
{
$result = PMA_DBI_query($sql_query, null, PMA_DBI_QUERY_UNBUFFERED);
$columns_cnt = PMA_DBI_num_fields($result);
for ($i = 0; $i < $columns_cnt; $i++) {
$columns[$i] = stripslashes(PMA_DBI_field_name($result, $i));
}
unset($i);
$cnt = 0;
$buffer = '';
while ($record = PMA_DBI_fetch_row($result)) {
$cnt++;
$buffer = $cnt . ":{$crlf}";
for ($i = 0; $i < $columns_cnt; $i++) {
if (isset($record[$i]) && !is_null($record[$i])) {
$buffer .= ' ' . $columns[$i] . ': ' . htmlspecialchars($record[$i]) . $crlf;
}
}
if (!PMA_exportOutputHandler($buffer)) {
return FALSE;
}
}
PMA_DBI_free_result($result);
return TRUE;
}
/**
* Outputs the content of a table in CSV format
*
* @param string the database name
* @param string the table name
* @param string the end of line sequence
* @param string the url to go back in case of error
* @param string SQL query for obtaining data
*
* @return bool Whether it suceeded
*
* @access public
*/
function PMA_exportData($db, $table, $crlf, $error_url, $sql_query)
{
global $what;
if (!PMA_exportOutputHandler('== ' . $GLOBALS['strDumpingData'] . ' ' . $table . "\n\n")) {
return FALSE;
}
// Gets the data from the database
$result = PMA_DBI_query($sql_query, null, PMA_DBI_QUERY_UNBUFFERED);
$fields_cnt = PMA_DBI_num_fields($result);
// If required, get fields name at the first line
if (isset($GLOBALS[$what . '_columns'])) {
$text_output = "|------\n";
for ($i = 0; $i < $fields_cnt; $i++) {
$text_output .= '|' . htmlspecialchars(stripslashes(PMA_DBI_field_name($result, $i)));
}
// end for
$text_output .= "\n|------\n";
if (!PMA_exportOutputHandler($text_output)) {
return FALSE;
}
}
// end if
// Format the data
while ($row = PMA_DBI_fetch_row($result)) {
$text_output = '';
for ($j = 0; $j < $fields_cnt; $j++) {
if (!isset($row[$j]) || is_null($row[$j])) {
$value = $GLOBALS[$what . '_null'];
} elseif ($row[$j] == '0' || $row[$j] != '') {
$value = $row[$j];
} else {
$value = ' ';
}
$text_output .= '|' . htmlspecialchars($value);
}
// end for
$text_output .= "\n";
if (!PMA_exportOutputHandler($text_output)) {
return FALSE;
}
}
// end while
PMA_DBI_free_result($result);
return TRUE;
}
/**
* Outputs the content of a table in CSV format
*
* @param string the database name
* @param string the table name
* @param string the end of line sequence
* @param string the url to go back in case of error
* @param string SQL query for obtaining data
*
* @return bool Whether it suceeded
*
* @access public
*/
function PMA_exportData($db, $table, $crlf, $error_url, $sql_query)
{
global $what;
// Gets the data from the database
$result = PMA_DBI_query($sql_query, null, PMA_DBI_QUERY_UNBUFFERED);
$fields_cnt = PMA_DBI_num_fields($result);
$fields_meta = PMA_DBI_get_fields_meta($result);
$field_flags = array();
for ($j = 0; $j < $fields_cnt; $j++) {
$field_flags[$j] = PMA_DBI_field_flags($result, $j);
}
$GLOBALS['ods_buffer'] .= '<table:table table:name="' . htmlspecialchars($table) . '">';
// If required, get fields name at the first line
if (isset($GLOBALS[$what . '_columns'])) {
$GLOBALS['ods_buffer'] .= '<table:table-row>';
for ($i = 0; $i < $fields_cnt; $i++) {
$GLOBALS['ods_buffer'] .= '<table:table-cell office:value-type="string">' . '<text:p>' . htmlspecialchars(stripslashes(PMA_DBI_field_name($result, $i))) . '</text:p>' . '</table:table-cell>';
}
// end for
$GLOBALS['ods_buffer'] .= '</table:table-row>';
}
// end if
// Format the data
while ($row = PMA_DBI_fetch_row($result)) {
$GLOBALS['ods_buffer'] .= '<table:table-row>';
for ($j = 0; $j < $fields_cnt; $j++) {
if (!isset($row[$j]) || is_null($row[$j])) {
$GLOBALS['ods_buffer'] .= '<table:table-cell office:value-type="string">' . '<text:p>' . htmlspecialchars($GLOBALS[$what . '_null']) . '</text:p>' . '</table:table-cell>';
// ignore binary field
// Note: with mysqli, under MySQL 4.1.3, we get the flag
// "binary" for those field types (I don't know why)
} elseif (stristr($field_flags[$j], 'BINARY') && isset($GLOBALS['sql_hex_for_binary']) && $fields_meta[$j]->type != 'datetime' && $fields_meta[$j]->type != 'date' && $fields_meta[$j]->type != 'time' && $fields_meta[$j]->type != 'timestamp') {
$GLOBALS['ods_buffer'] .= '<table:table-cell office:value-type="string">' . '<text:p></text:p>' . '</table:table-cell>';
} elseif ($fields_meta[$j]->numeric && $fields_meta[$j]->type != 'timestamp' && !$fields_meta[$j]->blob) {
$GLOBALS['ods_buffer'] .= '<table:table-cell office:value-type="float" office:value="' . $row[$j] . '" >' . '<text:p>' . htmlspecialchars($row[$j]) . '</text:p>' . '</table:table-cell>';
} else {
$GLOBALS['ods_buffer'] .= '<table:table-cell office:value-type="string">' . '<text:p>' . htmlspecialchars($row[$j]) . '</text:p>' . '</table:table-cell>';
}
}
// end for
$GLOBALS['ods_buffer'] .= '</table:table-row>';
}
// end while
PMA_DBI_free_result($result);
$GLOBALS['ods_buffer'] .= '</table:table>';
return TRUE;
}
function mysql_report($query, $attr = array())
{
foreach ($attr as $key => $val) {
$this->{$key} = $val;
}
// Pass 1 for column widths
// TODO: force here a LIMIT to speed up pass 1 ?
$this->results = PMA_DBI_query($query, null, PMA_DBI_QUERY_UNBUFFERED);
$this->numFields = PMA_DBI_num_fields($this->results);
$this->fields = PMA_DBI_get_fields_meta($this->results);
// if column widths not set
if (!isset($this->tablewidths)) {
// starting col width
$this->sColWidth = ($this->w - $this->lMargin - $this->rMargin) / $this->numFields;
// loop through results header and set initial col widths/ titles/ alignment
// if a col title is less than the starting col width / reduce that column size
for ($i = 0; $i < $this->numFields; $i++) {
$stringWidth = $this->getstringwidth($this->fields[$i]->name) + 6;
// set any column titles less than the start width to the column title width
if ($stringWidth < $this->sColWidth) {
$colFits[$i] = $stringWidth;
}
$this->colTitles[$i] = $this->fields[$i]->name;
$this->display_column[$i] = true;
switch ($this->fields[$i]->type) {
case 'int':
$this->colAlign[$i] = 'R';
break;
case 'blob':
case 'tinyblob':
case 'mediumblob':
case 'longblob':
//TODO: do not deactivate completely the display
// but show the field's name and [BLOB]
if (stristr($this->fields[$i]->flags, 'BINARY')) {
$this->display_column[$i] = false;
unset($this->colTitles[$i]);
}
$this->colAlign[$i] = 'L';
break;
default:
$this->colAlign[$i] = 'L';
}
}
// loop through the data, any column whose contents is bigger
// than the col size is resized
// TODO: force here a LIMIT to avoid reading all rows
while ($row = PMA_DBI_fetch_row($this->results)) {
foreach ($colFits as $key => $val) {
$stringWidth = $this->getstringwidth($row[$key]) + 6;
if ($stringWidth > $this->sColWidth) {
// any col where row is bigger than the start width is now discarded
unset($colFits[$key]);
} else {
// if text is not bigger than the current column width setting enlarge the column
if ($stringWidth > $val) {
$colFits[$key] = $stringWidth;
}
}
}
}
$totAlreadyFitted = 0;
foreach ($colFits as $key => $val) {
// set fitted columns to smallest size
$this->tablewidths[$key] = $val;
// to work out how much (if any) space has been freed up
$totAlreadyFitted += $val;
}
$surplus = sizeof($colFits) * $this->sColWidth - $totAlreadyFitted;
for ($i = 0; $i < $this->numFields; $i++) {
if (!in_array($i, array_keys($colFits))) {
$this->tablewidths[$i] = $this->sColWidth + $surplus / ($this->numFields - sizeof($colFits));
}
if ($this->display_column[$i] == false) {
$this->tablewidths[$i] = 0;
}
}
ksort($this->tablewidths);
}
PMA_DBI_free_result($this->results);
// Pass 2
$this->results = PMA_DBI_query($query, null, PMA_DBI_QUERY_UNBUFFERED);
$this->Open();
$this->setY($this->tMargin);
$this->AddPage();
$this->morepagestable($this->FontSizePt);
PMA_DBI_free_result($this->results);
}
/**
* Outputs the content of a table in JSON format
*
* @param string $db database name
* @param string $table table name
* @param string $crlf the end of line sequence
* @param string $error_url the url to go back in case of error
* @param string $sql_query SQL query for obtaining data
*
* @return bool Whether it succeeded
*/
public function exportData($db, $table, $crlf, $error_url, $sql_query)
{
$common_functions = PMA_CommonFunctions::getInstance();
$result = PMA_DBI_try_query($sql_query, null, PMA_DBI_QUERY_UNBUFFERED);
$columns_cnt = PMA_DBI_num_fields($result);
for ($i = 0; $i < $columns_cnt; $i++) {
$columns[$i] = PMA_DBI_field_name($result, $i);
}
unset($i);
$buffer = $crlf . '%' . $crlf . '% ' . __('Data') . ': ' . $table . $crlf . '%' . $crlf . ' \\begin{longtable}{|';
for ($index = 0; $index < $columns_cnt; $index++) {
$buffer .= 'l|';
}
$buffer .= '} ' . $crlf;
$buffer .= ' \\hline \\endhead \\hline \\endfoot \\hline ' . $crlf;
if (isset($GLOBALS['latex_caption'])) {
$buffer .= ' \\caption{' . $common_functions->expandUserString($GLOBALS['latex_data_caption'], 'texEscape', get_class($this), array('table' => $table, 'database' => $db)) . '} \\label{' . $common_functions->expandUserString($GLOBALS['latex_data_label'], null, null, array('table' => $table, 'database' => $db)) . '} \\\\';
}
if (!PMA_exportOutputHandler($buffer)) {
return false;
}
// show column names
if (isset($GLOBALS['latex_columns'])) {
$buffer = '\\hline ';
for ($i = 0; $i < $columns_cnt; $i++) {
$buffer .= '\\multicolumn{1}{|c|}{\\textbf{' . $this::texEscape(stripslashes($columns[$i])) . '}} & ';
}
$buffer = substr($buffer, 0, -2) . '\\\\ \\hline \\hline ';
if (!PMA_exportOutputHandler($buffer . ' \\endfirsthead ' . $crlf)) {
return false;
}
if (isset($GLOBALS['latex_caption'])) {
if (!PMA_exportOutputHandler('\\caption{' . $common_functions->expandUserString($GLOBALS['latex_data_continued_caption'], 'texEscape', get_class($this), array('table' => $table, 'database' => $db)) . '} \\\\ ')) {
return false;
}
}
if (!PMA_exportOutputHandler($buffer . '\\endhead \\endfoot' . $crlf)) {
return false;
}
} else {
if (!PMA_exportOutputHandler('\\\\ \\hline')) {
return false;
}
}
// print the whole table
while ($record = PMA_DBI_fetch_assoc($result)) {
$buffer = '';
// print each row
for ($i = 0; $i < $columns_cnt; $i++) {
if ((!function_exists('is_null') || !is_null($record[$columns[$i]])) && isset($record[$columns[$i]])) {
$column_value = $this::texEscape(stripslashes($record[$columns[$i]]));
} else {
$column_value = $GLOBALS['latex_null'];
}
// last column ... no need for & character
if ($i == $columns_cnt - 1) {
$buffer .= $column_value;
} else {
$buffer .= $column_value . " & ";
}
}
$buffer .= ' \\\\ \\hline ' . $crlf;
if (!PMA_exportOutputHandler($buffer)) {
return false;
}
}
$buffer = ' \\end{longtable}' . $crlf;
if (!PMA_exportOutputHandler($buffer)) {
return false;
}
PMA_DBI_free_result($result);
return true;
}
/**
* Outputs the content of a table in YAML format
*
* @param string the database name
* @param string the table name
* @param string the end of line sequence
* @param string the url to go back in case of error
* @param string SQL query for obtaining data
*
* @return bool Whether it suceeded
*
* @access public
*/
function PMA_exportData($db, $table, $crlf, $error_url, $sql_query)
{
$result = PMA_DBI_query($sql_query, null, PMA_DBI_QUERY_UNBUFFERED);
$columns_cnt = PMA_DBI_num_fields($result);
for ($i = 0; $i < $columns_cnt; $i++) {
$columns[$i] = stripslashes(PMA_DBI_field_name($result, $i));
}
unset($i);
$buffer = '';
$record_cnt = 0;
while ($record = PMA_DBI_fetch_row($result)) {
$record_cnt++;
// Output table name as comment if this is the first record of the table
if ($record_cnt == 1) {
$buffer .= $crlf . '// ' . $db . '.' . $table . $crlf;
$buffer .= '$' . $table . ' = array(' . $crlf;
$buffer .= ' array(';
} else {
$buffer .= ',' . $crlf . ' array(';
}
for ($i = 0; $i < $columns_cnt; $i++) {
$buffer .= "'" . $columns[$i] . "'=>" . var_export($record[$i], true) . ($i + 1 >= $columns_cnt ? '' : ',');
}
$buffer .= ')';
}
$buffer .= $crlf . ');' . $crlf;
if (!PMA_exportOutputHandler($buffer)) {
return FALSE;
}
PMA_DBI_free_result($result);
return true;
}
/**
* Outputs the content of a table in JSON format
*
* @param string $db database name
* @param string $table table name
* @param string $crlf the end of line sequence
* @param string $error_url the url to go back in case of error
* @param string $sql_query SQL query for obtaining data
*
* @return bool Whether it succeeded
*/
public function exportData($db, $table, $crlf, $error_url, $sql_query)
{
$result = PMA_DBI_query($sql_query, null, PMA_DBI_QUERY_UNBUFFERED);
$columns_cnt = PMA_DBI_num_fields($result);
// Get field information
$fields_meta = PMA_DBI_get_fields_meta($result);
for ($i = 0; $i < $columns_cnt; $i++) {
$columns[$i] = stripslashes(PMA_DBI_field_name($result, $i));
}
unset($i);
$buffer = '';
$record_cnt = 0;
while ($record = PMA_DBI_fetch_row($result)) {
$record_cnt++;
// Output table name as comment if this is the first record of the table
if ($record_cnt == 1) {
$buffer .= '// ' . $db . '.' . $table . $crlf . $crlf;
$buffer .= '[{';
} else {
$buffer .= ', {';
}
for ($i = 0; $i < $columns_cnt; $i++) {
$isLastLine = $i + 1 >= $columns_cnt;
$column = $columns[$i];
if (is_null($record[$i])) {
$buffer .= '"' . addslashes($column) . '": null' . (!$isLastLine ? ',' : '');
} elseif ($fields_meta[$i]->numeric) {
$buffer .= '"' . addslashes($column) . '": ' . $record[$i] . (!$isLastLine ? ',' : '');
} else {
$buffer .= '"' . addslashes($column) . '": "' . addslashes($record[$i]) . '"' . (!$isLastLine ? ',' : '');
}
}
$buffer .= '}';
}
if ($record_cnt) {
$buffer .= ']';
}
if (!PMA_exportOutputHandler($buffer)) {
return false;
}
PMA_DBI_free_result($result);
return true;
}
/**
* Outputs the content of a table in CSV format
*
* @param string the database name
* @param string the table name
* @param string the end of line sequence
* @param string the url to go back in case of error
* @param string SQL query for obtaining data
*
* @return bool Whether it suceeded
*
* @access public
*/
function PMA_exportData($db, $table, $crlf, $error_url, $sql_query)
{
global $what;
global $workbook;
$worksheet =& $workbook->addWorksheet($table);
$workbook->setTempDir(realpath($GLOBALS['cfg']['TempDir']));
// Gets the data from the database
$result = PMA_DBI_query($sql_query, null, PMA_DBI_QUERY_UNBUFFERED);
$fields_cnt = PMA_DBI_num_fields($result);
$col = 0;
// If required, get fields name at the first line
if (isset($GLOBALS['xls_columns']) && $GLOBALS['xls_columns'] == 'yes') {
$schema_insert = '';
for ($i = 0; $i < $fields_cnt; $i++) {
$worksheet->write(0, $i, stripslashes(PMA_DBI_field_name($result, $i)));
}
// end for
$col++;
}
// end if
// Format the data
while ($row = PMA_DBI_fetch_row($result)) {
$schema_insert = '';
for ($j = 0; $j < $fields_cnt; $j++) {
if (!isset($row[$j]) || is_null($row[$j])) {
$worksheet->write($col, $j, $GLOBALS['xls_null']);
} elseif ($row[$j] == '0' || $row[$j] != '') {
// FIXME: we should somehow handle character set here!
$worksheet->write($col, $j, $row[$j]);
} else {
$worksheet->write($col, $j, '');
}
}
// end for
$col++;
}
// end while
PMA_DBI_free_result($result);
return TRUE;
}
/**
* Dispatches between the versions of 'getTableContent' to use depending
* on the php version
*
* @param string the database name
* @param string the table name
* @param string the end of line sequence
* @param string the url to go back in case of error
* @param string SQL query for obtaining data
*
* @return bool Whether it suceeded
*
* @global boolean whether to use backquotes to allow the use of special
* characters in database, table and fields names or not
* @global integer the number of records
* @global integer the current record position
*
* @access public
*
* @see PMA_getTableContentFast(), PMA_getTableContentOld()
*
* @author staybyte
*/
function PMA_exportData($db, $table, $crlf, $error_url, $sql_query)
{
global $sql_backquotes;
global $rows_cnt;
global $current_row;
$formatted_table_name = isset($GLOBALS['sql_backquotes']) ? PMA_backquote($table) : '\'' . $table . '\'';
$head = $crlf . $GLOBALS['comment_marker'] . $crlf . $GLOBALS['comment_marker'] . $GLOBALS['strDumpingData'] . ' ' . $formatted_table_name . $crlf . $GLOBALS['comment_marker'] . $crlf . $crlf;
if (!PMA_exportOutputHandler($head)) {
return FALSE;
}
$buffer = '';
// analyze the query to get the true column names, not the aliases
// (this fixes an undefined index, also if Complete inserts
// are used, we did not get the true column name in case of aliases)
$analyzed_sql = PMA_SQP_analyze(PMA_SQP_parse($sql_query));
$result = PMA_DBI_query($sql_query, null, PMA_DBI_QUERY_UNBUFFERED);
if ($result != FALSE) {
$fields_cnt = PMA_DBI_num_fields($result);
// Get field information
$fields_meta = PMA_DBI_get_fields_meta($result);
$field_flags = array();
for ($j = 0; $j < $fields_cnt; $j++) {
$field_flags[$j] = PMA_DBI_field_flags($result, $j);
}
for ($j = 0; $j < $fields_cnt; $j++) {
if (isset($analyzed_sql[0]['select_expr'][$j]['column'])) {
$field_set[$j] = PMA_backquote($analyzed_sql[0]['select_expr'][$j]['column'], $sql_backquotes);
} else {
$field_set[$j] = PMA_backquote($fields_meta[$j]->name, $sql_backquotes);
}
}
if (isset($GLOBALS['sql_type']) && $GLOBALS['sql_type'] == 'UPDATE') {
// update
$schema_insert = 'UPDATE ';
if (isset($GLOBALS['sql_ignore'])) {
$schema_insert .= 'IGNORE ';
}
$schema_insert .= PMA_backquote($table, $sql_backquotes) . ' SET ';
} else {
// insert or replace
if (isset($GLOBALS['sql_type']) && $GLOBALS['sql_type'] == 'REPLACE') {
$sql_command = 'REPLACE';
} else {
$sql_command = 'INSERT';
}
// delayed inserts?
if (isset($GLOBALS['sql_delayed'])) {
$insert_delayed = ' DELAYED';
} else {
$insert_delayed = '';
}
// insert ignore?
if (isset($GLOBALS['sql_type']) && $GLOBALS['sql_type'] == 'INSERT' && isset($GLOBALS['sql_ignore'])) {
$insert_delayed .= ' IGNORE';
}
// scheme for inserting fields
if (isset($GLOBALS['sql_columns'])) {
$fields = implode(', ', $field_set);
$schema_insert = $sql_command . $insert_delayed . ' INTO ' . PMA_backquote($table, $sql_backquotes) . ' (' . $fields . ') VALUES ';
} else {
$schema_insert = $sql_command . $insert_delayed . ' INTO ' . PMA_backquote($table, $sql_backquotes) . ' VALUES ';
}
}
$search = array("", "\n", "\r", "");
//\x08\\x09, not required
$replace = array('\\0', '\\n', '\\r', '\\Z');
$current_row = 0;
$query_size = 0;
if (isset($GLOBALS['sql_extended']) && (!isset($GLOBALS['sql_type']) || $GLOBALS['sql_type'] != 'UPDATE')) {
$separator = ',';
$schema_insert .= $crlf;
} else {
$separator = ';';
}
while ($row = PMA_DBI_fetch_row($result)) {
$current_row++;
for ($j = 0; $j < $fields_cnt; $j++) {
// NULL
if (!isset($row[$j]) || is_null($row[$j])) {
$values[] = 'NULL';
// a number
// timestamp is numeric on some MySQL 4.1, BLOBs are sometimes numeric
} elseif ($fields_meta[$j]->numeric && $fields_meta[$j]->type != 'timestamp' && !$fields_meta[$j]->blob) {
$values[] = $row[$j];
// a binary field
// Note: with mysqli, under MySQL 4.1.3, we get the flag
// "binary" for those field types (I don't know why)
} elseif (stristr($field_flags[$j], 'BINARY') && isset($GLOBALS['sql_hex_for_binary']) && $fields_meta[$j]->type != 'datetime' && $fields_meta[$j]->type != 'date' && $fields_meta[$j]->type != 'time' && $fields_meta[$j]->type != 'timestamp') {
// empty blobs need to be different, but '0' is also empty :-(
if (empty($row[$j]) && $row[$j] != '0') {
$values[] = '\'\'';
} else {
$values[] = '0x' . bin2hex($row[$j]);
}
// something else -> treat as a string
} else {
$values[] = '\'' . str_replace($search, $replace, PMA_sqlAddslashes($row[$j])) . '\'';
}
// end if
}
// end for
// should we make update?
if (isset($GLOBALS['sql_type']) && $GLOBALS['sql_type'] == 'UPDATE') {
$insert_line = $schema_insert;
for ($i = 0; $i < $fields_cnt; $i++) {
if ($i > 0) {
$insert_line .= ', ';
}
$insert_line .= $field_set[$i] . ' = ' . $values[$i];
}
$insert_line .= ' WHERE ' . PMA_getUniqueCondition($result, $fields_cnt, $fields_meta, $row);
} else {
// Extended inserts case
if (isset($GLOBALS['sql_extended'])) {
if ($current_row == 1) {
$insert_line = $schema_insert . '(' . implode(', ', $values) . ')';
} else {
$insert_line = '(' . implode(', ', $values) . ')';
if (isset($GLOBALS['sql_max_query_size']) && $GLOBALS['sql_max_query_size'] > 0 && $query_size + strlen($insert_line) > $GLOBALS['sql_max_query_size']) {
if (!PMA_exportOutputHandler(';' . $crlf)) {
return FALSE;
}
$query_size = 0;
$current_row = 1;
$insert_line = $schema_insert . $insert_line;
}
}
$query_size += strlen($insert_line);
} else {
$insert_line = $schema_insert . '(' . implode(', ', $values) . ')';
}
}
unset($values);
if (!PMA_exportOutputHandler(($current_row == 1 ? '' : $separator . $crlf) . $insert_line)) {
return FALSE;
}
}
// end while
if ($current_row > 0) {
if (!PMA_exportOutputHandler(';' . $crlf)) {
return FALSE;
}
}
}
// end if ($result != FALSE)
PMA_DBI_free_result($result);
return TRUE;
}
function mysql_report($query, $attr = array())
{
foreach ($attr as $key => $val) {
$this->{$key} = $val;
}
/**
* Pass 1 for column widths
*/
$this->results = PMA_DBI_query($query, null, PMA_DBI_QUERY_UNBUFFERED);
$this->numFields = PMA_DBI_num_fields($this->results);
$this->fields = PMA_DBI_get_fields_meta($this->results);
// if column widths not set
if (!isset($this->tablewidths)) {
// sColWidth = starting col width (an average size width)
$availableWidth = $this->w - $this->lMargin - $this->rMargin;
$this->sColWidth = $availableWidth / $this->numFields;
$totalTitleWidth = 0;
// loop through results header and set initial col widths/ titles/ alignment
// if a col title is less than the starting col width, reduce that column size
for ($i = 0; $i < $this->numFields; $i++) {
$stringWidth = $this->getstringwidth($this->fields[$i]->name) + 6;
// save the real title's width
$titleWidth[$i] = $stringWidth;
$totalTitleWidth += $stringWidth;
// set any column titles less than the start width to the column title width
if ($stringWidth < $this->sColWidth) {
$colFits[$i] = $stringWidth;
}
$this->colTitles[$i] = $this->fields[$i]->name;
$this->display_column[$i] = true;
switch ($this->fields[$i]->type) {
case 'int':
$this->colAlign[$i] = 'R';
break;
case 'blob':
case 'tinyblob':
case 'mediumblob':
case 'longblob':
/**
* @todo do not deactivate completely the display
* but show the field's name and [BLOB]
*/
if (stristr($this->fields[$i]->flags, 'BINARY')) {
$this->display_column[$i] = false;
unset($this->colTitles[$i]);
}
$this->colAlign[$i] = 'L';
break;
default:
$this->colAlign[$i] = 'L';
}
}
// title width verification
if ($totalTitleWidth > $availableWidth) {
$adjustingMode = true;
} else {
$adjustingMode = false;
// we have enough space for all the titles at their
// original width so use the true title's width
foreach ($titleWidth as $key => $val) {
$colFits[$key] = $val;
}
}
// loop through the data, any column whose contents is bigger
// than the col size is resized
/**
* @todo force here a LIMIT to avoid reading all rows
*/
while ($row = PMA_DBI_fetch_row($this->results)) {
foreach ($colFits as $key => $val) {
$stringWidth = $this->getstringwidth($row[$key]) + 6;
if ($adjustingMode && $stringWidth > $this->sColWidth) {
// any column whose data's width is bigger than the start width is now discarded
unset($colFits[$key]);
} else {
// if data's width is bigger than the current column width,
// enlarge the column (but avoid enlarging it if the
// data's width is very big)
if ($stringWidth > $val && $stringWidth < $this->sColWidth * 3) {
$colFits[$key] = $stringWidth;
}
}
}
}
$totAlreadyFitted = 0;
foreach ($colFits as $key => $val) {
// set fitted columns to smallest size
$this->tablewidths[$key] = $val;
// to work out how much (if any) space has been freed up
$totAlreadyFitted += $val;
}
if ($adjustingMode) {
$surplus = sizeof($colFits) * $this->sColWidth - $totAlreadyFitted;
$surplusToAdd = $surplus / ($this->numFields - sizeof($colFits));
} else {
$surplusToAdd = 0;
}
for ($i = 0; $i < $this->numFields; $i++) {
if (!in_array($i, array_keys($colFits))) {
$this->tablewidths[$i] = $this->sColWidth + $surplusToAdd;
}
if ($this->display_column[$i] == false) {
$this->tablewidths[$i] = 0;
}
}
ksort($this->tablewidths);
}
PMA_DBI_free_result($this->results);
// Pass 2
$this->results = PMA_DBI_query($query, null, PMA_DBI_QUERY_UNBUFFERED);
$this->Open();
$this->setY($this->tMargin);
$this->AddPage();
$this->morepagestable($this->FontSizePt);
PMA_DBI_free_result($this->results);
}
/**
* Dispatches between the versions of 'getTableContent' to use depending
* on the php version
*
* @param string the database name
* @param string the table name
* @param string the end of line sequence
* @param string the url to go back in case of error
* @param string SQL query for obtaining data
*
* @return bool Whether it suceeded
*
* @global boolean whether to use backquotes to allow the use of special
* characters in database, table and fields names or not
* @global integer the number of records
* @global integer the current record position
*
* @access public
*
* @see PMA_getTableContentFast(), PMA_getTableContentOld()
*
* @author staybyte
*/
function PMA_exportData($db, $table, $crlf, $error_url, $sql_query)
{
global $sql_backquotes;
global $rows_cnt;
global $current_row;
$formatted_table_name = isset($GLOBALS['sql_backquotes']) ? PMA_backquote($table) : '\'' . $table . '\'';
// Do not export data for a VIEW
// (For a VIEW, this is called only when exporting a single VIEW)
if (PMA_Table::_isView($db, $table)) {
$head = $crlf . PMA_exportComment() . PMA_exportComment('VIEW ' . ' ' . $formatted_table_name) . PMA_exportComment($GLOBALS['strData'] . ': ' . $GLOBALS['strNone']) . PMA_exportComment() . $crlf;
if (!PMA_exportOutputHandler($head)) {
return FALSE;
}
return true;
}
// it's not a VIEW
$head = $crlf . PMA_exportComment() . PMA_exportComment($GLOBALS['strDumpingData'] . ' ' . $formatted_table_name) . PMA_exportComment() . $crlf;
if (!PMA_exportOutputHandler($head)) {
return FALSE;
}
$buffer = '';
// analyze the query to get the true column names, not the aliases
// (this fixes an undefined index, also if Complete inserts
// are used, we did not get the true column name in case of aliases)
$analyzed_sql = PMA_SQP_analyze(PMA_SQP_parse($sql_query));
$result = PMA_DBI_query($sql_query, null, PMA_DBI_QUERY_UNBUFFERED);
if ($result != FALSE) {
$fields_cnt = PMA_DBI_num_fields($result);
// Get field information
$fields_meta = PMA_DBI_get_fields_meta($result);
$field_flags = array();
for ($j = 0; $j < $fields_cnt; $j++) {
$field_flags[$j] = PMA_DBI_field_flags($result, $j);
}
for ($j = 0; $j < $fields_cnt; $j++) {
if (isset($analyzed_sql[0]['select_expr'][$j]['column'])) {
$field_set[$j] = PMA_backquote($analyzed_sql[0]['select_expr'][$j]['column'], $sql_backquotes);
} else {
$field_set[$j] = PMA_backquote($fields_meta[$j]->name, $sql_backquotes);
}
}
if (isset($GLOBALS['sql_type']) && $GLOBALS['sql_type'] == 'UPDATE') {
// update
$schema_insert = 'UPDATE ';
if (isset($GLOBALS['sql_ignore'])) {
$schema_insert .= 'IGNORE ';
}
// avoid EOL blank
$schema_insert .= PMA_backquote($table, $sql_backquotes) . ' SET';
} else {
// insert or replace
if (isset($GLOBALS['sql_type']) && $GLOBALS['sql_type'] == 'REPLACE') {
$sql_command = 'REPLACE';
} else {
$sql_command = 'INSERT';
}
// delayed inserts?
if (isset($GLOBALS['sql_delayed'])) {
$insert_delayed = ' DELAYED';
} else {
$insert_delayed = '';
}
// insert ignore?
if (isset($GLOBALS['sql_type']) && $GLOBALS['sql_type'] == 'INSERT' && isset($GLOBALS['sql_ignore'])) {
$insert_delayed .= ' IGNORE';
}
// scheme for inserting fields
if (isset($GLOBALS['sql_columns'])) {
$fields = implode(', ', $field_set);
$schema_insert = $sql_command . $insert_delayed . ' INTO ' . PMA_backquote($table, $sql_backquotes) . ' (' . $fields . ') VALUES';
} else {
$schema_insert = $sql_command . $insert_delayed . ' INTO ' . PMA_backquote($table, $sql_backquotes) . ' VALUES';
}
}
$search = array("", "\n", "\r", "");
//\x08\\x09, not required
$replace = array('\\0', '\\n', '\\r', '\\Z');
$current_row = 0;
$query_size = 0;
if (isset($GLOBALS['sql_extended']) && (!isset($GLOBALS['sql_type']) || $GLOBALS['sql_type'] != 'UPDATE')) {
$separator = ',';
$schema_insert .= $crlf;
} else {
$separator = ';';
}
while ($row = PMA_DBI_fetch_row($result)) {
$current_row++;
for ($j = 0; $j < $fields_cnt; $j++) {
// NULL
if (!isset($row[$j]) || is_null($row[$j])) {
$values[] = 'NULL';
// a number
// timestamp is numeric on some MySQL 4.1, BLOBs are sometimes numeric
} elseif ($fields_meta[$j]->numeric && $fields_meta[$j]->type != 'timestamp' && !$fields_meta[$j]->blob) {
$values[] = $row[$j];
// a true BLOB
// - mysqldump only generates hex data when the --hex-blob
// option is used, for fields having the binary attribute
// no hex is generated
// - a TEXT field returns type blob but a real blob
// returns also the 'binary' flag
} elseif (stristr($field_flags[$j], 'BINARY') && $fields_meta[$j]->blob && isset($GLOBALS['sql_hex_for_blob'])) {
// empty blobs need to be different, but '0' is also empty :-(
if (empty($row[$j]) && $row[$j] != '0') {
$values[] = '\'\'';
} else {
$values[] = '0x' . bin2hex($row[$j]);
}
// detection of 'bit' works only on mysqli extension
} elseif ($fields_meta[$j]->type == 'bit') {
$values[] = "b'" . PMA_sqlAddslashes(PMA_printable_bit_value($row[$j], $fields_meta[$j]->length)) . "'";
// something else -> treat as a string
} else {
$values[] = '\'' . str_replace($search, $replace, PMA_sqlAddslashes($row[$j])) . '\'';
}
// end if
}
// end for
// should we make update?
if (isset($GLOBALS['sql_type']) && $GLOBALS['sql_type'] == 'UPDATE') {
$insert_line = $schema_insert;
for ($i = 0; $i < $fields_cnt; $i++) {
if (0 == $i) {
$insert_line .= ' ';
}
if ($i > 0) {
// avoid EOL blank
$insert_line .= ',';
}
$insert_line .= $field_set[$i] . ' = ' . $values[$i];
}
$insert_line .= ' WHERE ' . PMA_getUniqueCondition($result, $fields_cnt, $fields_meta, $row);
} else {
// Extended inserts case
if (isset($GLOBALS['sql_extended'])) {
if ($current_row == 1) {
$insert_line = $schema_insert . '(' . implode(', ', $values) . ')';
} else {
$insert_line = '(' . implode(', ', $values) . ')';
if (isset($GLOBALS['sql_max_query_size']) && $GLOBALS['sql_max_query_size'] > 0 && $query_size + strlen($insert_line) > $GLOBALS['sql_max_query_size']) {
if (!PMA_exportOutputHandler(';' . $crlf)) {
return FALSE;
}
$query_size = 0;
$current_row = 1;
$insert_line = $schema_insert . $insert_line;
}
}
$query_size += strlen($insert_line);
} else {
$insert_line = $schema_insert . '(' . implode(', ', $values) . ')';
}
}
unset($values);
if (!PMA_exportOutputHandler(($current_row == 1 ? '' : $separator . $crlf) . $insert_line)) {
return FALSE;
}
}
// end while
if ($current_row > 0) {
if (!PMA_exportOutputHandler(';' . $crlf)) {
return FALSE;
}
}
}
// end if ($result != FALSE)
PMA_DBI_free_result($result);
return TRUE;
}
/**
* Outputs the content of a table
*
* @param string the database name
* @param string the table name
* @param string the end of line sequence
* @param string the url to go back in case of error
* @param string SQL query for obtaining data
*
* @return bool Whether it suceeded
*
* @access public
*/
function PMA_exportData($db, $table, $crlf, $error_url, $sql_query)
{
$result = PMA_DBI_query($sql_query, NULL, PMA_DBI_QUERY_UNBUFFERED);
$columns_cnt = PMA_DBI_num_fields($result);
for ($i = 0; $i < $columns_cnt; $i++) {
$columns[$i] = stripslashes(PMA_DBI_field_name($result, $i));
}
unset($i);
$buffer = ' <!-- ' . $GLOBALS['strTable'] . ' ' . $table . ' -->' . $crlf;
if (!PMA_exportOutputHandler($buffer)) {
return FALSE;
}
while ($record = PMA_DBI_fetch_row($result)) {
$buffer = ' <' . $table . '>' . $crlf;
for ($i = 0; $i < $columns_cnt; $i++) {
if (isset($record[$i]) && !is_null($record[$i])) {
$buffer .= ' <' . $columns[$i] . '>' . htmlspecialchars($record[$i]) . '</' . $columns[$i] . '>' . $crlf;
}
}
$buffer .= ' </' . $table . '>' . $crlf;
if (!PMA_exportOutputHandler($buffer)) {
return FALSE;
}
}
PMA_DBI_free_result($result);
return TRUE;
}
/**
* Outputs the content of a table in XML format
*
* @param string $db database name
* @param string $table table name
* @param string $crlf the end of line sequence
* @param string $error_url the url to go back in case of error
* @param string $sql_query SQL query for obtaining data
*
* @return bool Whether it succeeded
*/
public function exportData($db, $table, $crlf, $error_url, $sql_query)
{
if (isset($GLOBALS['xml_export_contents']) && $GLOBALS['xml_export_contents']) {
$result = PMA_DBI_query($sql_query, null, PMA_DBI_QUERY_UNBUFFERED);
$columns_cnt = PMA_DBI_num_fields($result);
$columns = array();
for ($i = 0; $i < $columns_cnt; $i++) {
$columns[$i] = stripslashes(PMA_DBI_field_name($result, $i));
}
unset($i);
$buffer = ' <!-- ' . __('Table') . ' ' . $table . ' -->' . $crlf;
if (!PMA_exportOutputHandler($buffer)) {
return false;
}
while ($record = PMA_DBI_fetch_row($result)) {
$buffer = ' <table name="' . htmlspecialchars($table) . '">' . $crlf;
for ($i = 0; $i < $columns_cnt; $i++) {
// If a cell is NULL, still export it to preserve
// the XML structure
if (!isset($record[$i]) || is_null($record[$i])) {
$record[$i] = 'NULL';
}
$buffer .= ' <column name="' . htmlspecialchars($columns[$i]) . '">' . htmlspecialchars((string) $record[$i]) . '</column>' . $crlf;
}
$buffer .= ' </table>' . $crlf;
if (!PMA_exportOutputHandler($buffer)) {
return false;
}
}
PMA_DBI_free_result($result);
}
return true;
}
/**
* Outputs the content of a table in SQL format
*
* @param string $db database name
* @param string $table table name
* @param string $crlf the end of line sequence
* @param string $error_url the url to go back in case of error
* @param string $sql_query SQL query for obtaining data
*
* @return bool Whether it succeeded
*/
public function exportData($db, $table, $crlf, $error_url, $sql_query)
{
global $current_row, $sql_backquotes;
if (isset($GLOBALS['sql_compatibility'])) {
$compat = $GLOBALS['sql_compatibility'];
} else {
$compat = 'NONE';
}
$formatted_table_name = isset($GLOBALS['sql_backquotes']) ? PMA_Util::backquoteCompat($table, $compat) : '\'' . $table . '\'';
// Do not export data for a VIEW
// (For a VIEW, this is called only when exporting a single VIEW)
if (PMA_Table::isView($db, $table)) {
$head = $this->_possibleCRLF() . $this->_exportComment() . $this->_exportComment('VIEW ' . ' ' . $formatted_table_name) . $this->_exportComment(__('Data') . ': ' . __('None')) . $this->_exportComment() . $this->_possibleCRLF();
if (!PMA_exportOutputHandler($head)) {
return false;
}
return true;
}
// analyze the query to get the true column names, not the aliases
// (this fixes an undefined index, also if Complete inserts
// are used, we did not get the true column name in case of aliases)
$analyzed_sql = PMA_SQP_analyze(PMA_SQP_parse($sql_query));
$result = PMA_DBI_try_query($sql_query, null, PMA_DBI_QUERY_UNBUFFERED);
// a possible error: the table has crashed
$tmp_error = PMA_DBI_getError();
if ($tmp_error) {
return PMA_exportOutputHandler($this->_exportComment(__('Error reading data:') . ' (' . $tmp_error . ')'));
}
if ($result != false) {
$fields_cnt = PMA_DBI_num_fields($result);
// Get field information
$fields_meta = PMA_DBI_get_fields_meta($result);
$field_flags = array();
for ($j = 0; $j < $fields_cnt; $j++) {
$field_flags[$j] = PMA_DBI_field_flags($result, $j);
}
for ($j = 0; $j < $fields_cnt; $j++) {
if (isset($analyzed_sql[0]['select_expr'][$j]['column'])) {
$field_set[$j] = PMA_Util::backquoteCompat($analyzed_sql[0]['select_expr'][$j]['column'], $compat, $sql_backquotes);
} else {
$field_set[$j] = PMA_Util::backquoteCompat($fields_meta[$j]->name, $compat, $sql_backquotes);
}
}
if (isset($GLOBALS['sql_type']) && $GLOBALS['sql_type'] == 'UPDATE') {
// update
$schema_insert = 'UPDATE ';
if (isset($GLOBALS['sql_ignore'])) {
$schema_insert .= 'IGNORE ';
}
// avoid EOL blank
$schema_insert .= PMA_Util::backquoteCompat($table, $compat, $sql_backquotes) . ' SET';
} else {
// insert or replace
if (isset($GLOBALS['sql_type']) && $GLOBALS['sql_type'] == 'REPLACE') {
$sql_command = 'REPLACE';
} else {
$sql_command = 'INSERT';
}
// delayed inserts?
if (isset($GLOBALS['sql_delayed'])) {
$insert_delayed = ' DELAYED';
} else {
$insert_delayed = '';
}
// insert ignore?
if (isset($GLOBALS['sql_type']) && $GLOBALS['sql_type'] == 'INSERT' && isset($GLOBALS['sql_ignore'])) {
$insert_delayed .= ' IGNORE';
}
//truncate table before insert
if (isset($GLOBALS['sql_truncate']) && $GLOBALS['sql_truncate'] && $sql_command == 'INSERT') {
$truncate = 'TRUNCATE TABLE ' . PMA_Util::backquoteCompat($table, $compat, $sql_backquotes) . ";";
$truncatehead = $this->_possibleCRLF() . $this->_exportComment() . $this->_exportComment(__('Truncate table before insert') . ' ' . $formatted_table_name) . $this->_exportComment() . $crlf;
PMA_exportOutputHandler($truncatehead);
PMA_exportOutputHandler($truncate);
} else {
$truncate = '';
}
// scheme for inserting fields
if ($GLOBALS['sql_insert_syntax'] == 'complete' || $GLOBALS['sql_insert_syntax'] == 'both') {
$fields = implode(', ', $field_set);
$schema_insert = $sql_command . $insert_delayed . ' INTO ' . PMA_Util::backquoteCompat($table, $compat, $sql_backquotes) . ' (' . $fields . ') VALUES';
} else {
$schema_insert = $sql_command . $insert_delayed . ' INTO ' . PMA_Util::backquoteCompat($table, $compat, $sql_backquotes) . ' VALUES';
}
}
//\x08\\x09, not required
$search = array("", "\n", "\r", "");
$replace = array('\\0', '\\n', '\\r', '\\Z');
$current_row = 0;
$query_size = 0;
if (($GLOBALS['sql_insert_syntax'] == 'extended' || $GLOBALS['sql_insert_syntax'] == 'both') && (!isset($GLOBALS['sql_type']) || $GLOBALS['sql_type'] != 'UPDATE')) {
$separator = ',';
$schema_insert .= $crlf;
} else {
$separator = ';';
}
while ($row = PMA_DBI_fetch_row($result)) {
if ($current_row == 0) {
$head = $this->_possibleCRLF() . $this->_exportComment() . $this->_exportComment(__('Dumping data for table') . ' ' . $formatted_table_name) . $this->_exportComment() . $crlf;
if (!PMA_exportOutputHandler($head)) {
return false;
}
}
// We need to SET IDENTITY_INSERT ON for MSSQL
if (isset($GLOBALS['sql_compatibility']) && $GLOBALS['sql_compatibility'] == 'MSSQL' && $current_row == 0) {
if (!PMA_exportOutputHandler('SET IDENTITY_INSERT ' . PMA_Util::backquoteCompat($table, $compat) . ' ON ;' . $crlf)) {
return false;
}
}
$current_row++;
for ($j = 0; $j < $fields_cnt; $j++) {
// NULL
if (!isset($row[$j]) || is_null($row[$j])) {
$values[] = 'NULL';
} elseif ($fields_meta[$j]->numeric && $fields_meta[$j]->type != 'timestamp' && !$fields_meta[$j]->blob) {
// a number
// timestamp is numeric on some MySQL 4.1, BLOBs are
// sometimes numeric
$values[] = $row[$j];
} elseif (stristr($field_flags[$j], 'BINARY') && $fields_meta[$j]->blob && isset($GLOBALS['sql_hex_for_blob'])) {
// a true BLOB
// - mysqldump only generates hex data when the --hex-blob
// option is used, for fields having the binary attribute
// no hex is generated
// - a TEXT field returns type blob but a real blob
// returns also the 'binary' flag
// empty blobs need to be different, but '0' is also empty
// :-(
if (empty($row[$j]) && $row[$j] != '0') {
$values[] = '\'\'';
} else {
$values[] = '0x' . bin2hex($row[$j]);
}
} elseif ($fields_meta[$j]->type == 'bit') {
// detection of 'bit' works only on mysqli extension
$values[] = "b'" . PMA_Util::sqlAddSlashes(PMA_Util::printableBitValue($row[$j], $fields_meta[$j]->length)) . "'";
} else {
// something else -> treat as a string
$values[] = '\'' . str_replace($search, $replace, PMA_Util::sqlAddSlashes($row[$j])) . '\'';
}
// end if
}
// end for
// should we make update?
if (isset($GLOBALS['sql_type']) && $GLOBALS['sql_type'] == 'UPDATE') {
$insert_line = $schema_insert;
for ($i = 0; $i < $fields_cnt; $i++) {
if (0 == $i) {
$insert_line .= ' ';
}
if ($i > 0) {
// avoid EOL blank
$insert_line .= ',';
}
$insert_line .= $field_set[$i] . ' = ' . $values[$i];
}
list($tmp_unique_condition, $tmp_clause_is_unique) = PMA_Util::getUniqueCondition($result, $fields_cnt, $fields_meta, $row);
$insert_line .= ' WHERE ' . $tmp_unique_condition;
unset($tmp_unique_condition, $tmp_clause_is_unique);
} else {
// Extended inserts case
if ($GLOBALS['sql_insert_syntax'] == 'extended' || $GLOBALS['sql_insert_syntax'] == 'both') {
if ($current_row == 1) {
$insert_line = $schema_insert . '(' . implode(', ', $values) . ')';
} else {
$insert_line = '(' . implode(', ', $values) . ')';
$sql_max_size = $GLOBALS['sql_max_query_size'];
if (isset($sql_max_size) && $sql_max_size > 0 && $query_size + strlen($insert_line) > $sql_max_size) {
if (!PMA_exportOutputHandler(';' . $crlf)) {
return false;
}
$query_size = 0;
$current_row = 1;
$insert_line = $schema_insert . $insert_line;
}
}
$query_size += strlen($insert_line);
// Other inserts case
} else {
$insert_line = $schema_insert . '(' . implode(', ', $values) . ')';
}
}
unset($values);
if (!PMA_exportOutputHandler(($current_row == 1 ? '' : $separator . $crlf) . $insert_line)) {
return false;
}
}
// end while
if ($current_row > 0) {
if (!PMA_exportOutputHandler(';' . $crlf)) {
return false;
}
}
// We need to SET IDENTITY_INSERT OFF for MSSQL
if (isset($GLOBALS['sql_compatibility']) && $GLOBALS['sql_compatibility'] == 'MSSQL' && $current_row > 0) {
$outputSucceeded = PMA_exportOutputHandler($crlf . 'SET IDENTITY_INSERT ' . PMA_Util::backquoteCompat($table, $compat) . ' OFF;' . $crlf);
if (!$outputSucceeded) {
return false;
}
}
}
// end if ($result != false)
PMA_DBI_free_result($result);
return true;
}
/**
* Outputs the content of a table in CSV format
*
* @param string the database name
* @param string the table name
* @param string the end of line sequence
* @param string the url to go back in case of error
* @param string SQL query for obtaining data
*
* @return bool Whether it suceeded
*
* @access public
*/
function PMA_exportData($db, $table, $crlf, $error_url, $sql_query)
{
global $what;
global $workbook;
$workbook->setTempDir(realpath($GLOBALS['cfg']['TempDir']));
// Gets the data from the database
$result = PMA_DBI_query($sql_query, null, PMA_DBI_QUERY_UNBUFFERED);
$fields_cnt = PMA_DBI_num_fields($result);
$row = PMA_DBI_fetch_row($result);
for ($sheetIndex = 0;; $sheetIndex++) {
// Maximum sheet name length is 31 chars - leave 2 for numeric index
$sheetName = substr($table, 0, 29) . ($sheetIndex > 0 ? $sheetIndex : '');
$worksheet =& $workbook->addWorksheet($sheetName);
$rowIndex = 0;
// If required, get fields name at the first line
if (isset($GLOBALS['xls_columns']) && $GLOBALS['xls_columns']) {
for ($i = 0; $i < $fields_cnt; $i++) {
$worksheet->write(0, $i, stripslashes(PMA_DBI_field_name($result, $i)));
}
// end for
$worksheet->repeatRows($rowIndex);
$worksheet->freezePanes(array($rowIndex + 1, 0, $rowIndex + 1, 0));
$rowIndex++;
}
// end if
// Format the data (max 65536 rows per worksheet)
while ($rowIndex < 65536 && $row) {
set_time_limit(0);
for ($j = 0; $j < $fields_cnt; $j++) {
if (!isset($row[$j]) || is_null($row[$j])) {
$worksheet->write($rowIndex, $j, $GLOBALS['xls_null']);
} elseif ($row[$j] == '0' || $row[$j] != '') {
/**
* @todo we should somehow handle character set here!
*/
$worksheet->write($rowIndex, $j, $row[$j]);
} else {
$worksheet->write($rowIndex, $j, '');
}
}
// end for
$rowIndex++;
$row = PMA_DBI_fetch_row($result);
}
// end while
if (!$row) {
break;
}
}
// end for
PMA_DBI_free_result($result);
return TRUE;
}
/**
* Outputs the content of a table in YAML format
*
* @param string $db database name
* @param string $table table name
* @param string $crlf the end of line sequence
* @param string $error_url the url to go back in case of error
* @param string $sql_query SQL query for obtaining data
* @return bool Whether it succeeded
*
* @access public
*/
function PMA_exportData($db, $table, $crlf, $error_url, $sql_query)
{
$result = PMA_DBI_query($sql_query, null, PMA_DBI_QUERY_UNBUFFERED);
$columns_cnt = PMA_DBI_num_fields($result);
for ($i = 0; $i < $columns_cnt; $i++) {
$columns[$i] = stripslashes(PMA_DBI_field_name($result, $i));
}
unset($i);
$buffer = '';
$record_cnt = 0;
while ($record = PMA_DBI_fetch_row($result)) {
$record_cnt++;
// Output table name as comment if this is the first record of the table
if ($record_cnt == 1) {
$buffer = '# ' . $db . '.' . $table . $crlf;
$buffer .= '-' . $crlf;
} else {
$buffer = '-' . $crlf;
}
for ($i = 0; $i < $columns_cnt; $i++) {
if (!isset($record[$i])) {
continue;
}
$column = $columns[$i];
if (is_null($record[$i])) {
$buffer .= ' ' . $column . ': null' . $crlf;
continue;
}
if (is_numeric($record[$i])) {
$buffer .= ' ' . $column . ': ' . $record[$i] . $crlf;
continue;
}
$record[$i] = str_replace(array('\\', '"', "\n", "\r"), array('\\\\', '\\"', '\\n', '\\r'), $record[$i]);
$buffer .= ' ' . $column . ': "' . $record[$i] . '"' . $crlf;
}
if (!PMA_exportOutputHandler($buffer)) {
return false;
}
}
PMA_DBI_free_result($result);
return true;
}
/**
* Outputs the content of a table
*
* @param string the database name
* @param string the table name
* @param string the end of line sequence
* @param string the url to go back in case of error
* @param string SQL query for obtaining data
*
* @return bool Whether it suceeded
*
* @access public
*/
function PMA_exportData($db, $table, $crlf, $error_url, $sql_query)
{
global $what;
if (isset($GLOBALS[$what . '_export_contents']) && $GLOBALS[$what . '_export_contents']) {
$result = PMA_DBI_query($sql_query, null, PMA_DBI_QUERY_UNBUFFERED);
$columns_cnt = PMA_DBI_num_fields($result);
for ($i = 0; $i < $columns_cnt; $i++) {
$columns[$i] = stripslashes(str_replace(' ', '_', PMA_DBI_field_name($result, $i)));
}
unset($i);
$buffer = ' <!-- ' . $GLOBALS['strTable'] . ' ' . $table . ' -->' . $crlf;
if (!PMA_exportOutputHandler($buffer)) {
return FALSE;
}
while ($record = PMA_DBI_fetch_row($result)) {
$buffer = ' <table name="' . htmlspecialchars($table) . '">' . $crlf;
for ($i = 0; $i < $columns_cnt; $i++) {
// If a cell is NULL, still export it to preserve the XML structure
if (!isset($record[$i]) || is_null($record[$i])) {
$record[$i] = 'NULL';
}
$buffer .= ' <column name="' . $columns[$i] . '">' . htmlspecialchars(utf8_encode((string) $record[$i])) . '</column>' . $crlf;
}
$buffer .= ' </table>' . $crlf;
if (!PMA_exportOutputHandler($buffer)) {
return FALSE;
}
}
PMA_DBI_free_result($result);
}
return TRUE;
}
/**
* Outputs the content of a table in LaTeX table/sideways table environment
*
* @param string the database name
* @param string the table name
* @param string the end of line sequence
* @param string the url to go back in case of error
* @param string SQL query for obtaining data
*
* @return bool Whether it suceeded
*
* @access public
*/
function PMA_exportData($db, $table, $crlf, $error_url, $sql_query)
{
$result = PMA_DBI_try_query($sql_query, null, PMA_DBI_QUERY_UNBUFFERED);
$columns_cnt = PMA_DBI_num_fields($result);
for ($i = 0; $i < $columns_cnt; $i++) {
$columns[$i] = PMA_DBI_field_name($result, $i);
}
unset($i);
$buffer = $crlf . '%' . $crlf . '% ' . $GLOBALS['strData'] . ': ' . $table . $crlf . '%' . $crlf . ' \\begin{longtable}{|';
for ($index = 0; $index < $columns_cnt; $index++) {
$buffer .= 'l|';
}
$buffer .= '} ' . $crlf;
$buffer .= ' \\hline \\endhead \\hline \\endfoot \\hline ' . $crlf;
if (isset($GLOBALS['latex_caption'])) {
$buffer .= ' \\caption{' . str_replace('__TABLE__', PMA_texEscape($table), $GLOBALS['latex_data_caption']) . '} \\label{' . str_replace('__TABLE__', $table, $GLOBALS['latex_data_label']) . '} \\\\';
}
if (!PMA_exportOutputHandler($buffer)) {
return FALSE;
}
// show column names
if (isset($GLOBALS['latex_columns'])) {
$buffer = '\\hline ';
for ($i = 0; $i < $columns_cnt; $i++) {
$buffer .= '\\multicolumn{1}{|c|}{\\textbf{' . PMA_texEscape(stripslashes($columns[$i])) . '}} & ';
}
$buffer = substr($buffer, 0, -2) . '\\\\ \\hline \\hline ';
if (!PMA_exportOutputHandler($buffer . ' \\endfirsthead ' . $crlf)) {
return FALSE;
}
if (isset($GLOBALS['latex_caption'])) {
if (!PMA_exportOutputHandler('\\caption{' . str_replace('__TABLE__', PMA_texEscape($table), $GLOBALS['latex_data_continued_caption']) . '} \\\\ ')) {
return FALSE;
}
}
if (!PMA_exportOutputHandler($buffer . '\\endhead \\endfoot' . $crlf)) {
return FALSE;
}
} else {
if (!PMA_exportOutputHandler('\\\\ \\hline')) {
return FALSE;
}
}
// print the whole table
while ($record = PMA_DBI_fetch_assoc($result)) {
$buffer = '';
// print each row
for ($i = 0; $i < $columns_cnt; $i++) {
if (isset($record[$columns[$i]]) && (!function_exists('is_null') || !is_null($record[$columns[$i]]))) {
$column_value = PMA_texEscape(stripslashes($record[$columns[$i]]));
} else {
$column_value = $GLOBALS['latex_null'];
}
// last column ... no need for & character
if ($i == $columns_cnt - 1) {
$buffer .= $column_value;
} else {
$buffer .= $column_value . " & ";
}
}
$buffer .= ' \\\\ \\hline ' . $crlf;
if (!PMA_exportOutputHandler($buffer)) {
return FALSE;
}
}
$buffer = ' \\end{longtable}' . $crlf;
if (!PMA_exportOutputHandler($buffer)) {
return FALSE;
}
PMA_DBI_free_result($result);
return TRUE;
}
/**
* returns all rows in the resultset in one array
*
* <code>
* $sql = 'SELECT * FROM `user`';
* $users = PMA_DBI_fetch_result( $sql );
* // produces
* // $users[] = array( 'id' => 123, 'name' => 'John Doe' )
*
* $sql = 'SELECT `id`, `name` FROM `user`';
* $users = PMA_DBI_fetch_result( $sql, 'id' );
* // produces
* // $users['123'] = array( 'id' => 123, 'name' => 'John Doe' )
*
* $sql = 'SELECT `id`, `name` FROM `user`';
* $users = PMA_DBI_fetch_result( $sql, 0 );
* // produces
* // $users['123'] = array( 0 => 123, 1 => 'John Doe' )
*
* $sql = 'SELECT `id`, `name` FROM `user`';
* $users = PMA_DBI_fetch_result( $sql, 'id', 'name' );
* // or
* $users = PMA_DBI_fetch_result( $sql, 0, 1 );
* // produces
* // $users['123'] = 'John Doe'
*
* $sql = 'SELECT `name` FROM `user`';
* $users = PMA_DBI_fetch_result( $sql );
* // produces
* // $users[] = 'John Doe'
* </code>
*
* @uses is_string()
* @uses is_int()
* @uses PMA_DBI_try_query()
* @uses PMA_DBI_num_rows()
* @uses PMA_DBI_num_fields()
* @uses PMA_DBI_fetch_row()
* @uses PMA_DBI_fetch_assoc()
* @uses PMA_DBI_free_result()
* @param string|mysql_result $result query or mysql result
* @param string|integer $key field-name or offset
* used as key for array
* @param string|integer $value value-name or offset
* used as value for array
* @param resource $link mysql link
* @param mixed $options
* @return array resultrows or values indexed by $key
*/
function PMA_DBI_fetch_result($result, $key = NULL, $value = NULL, $link = NULL, $options = 0)
{
$resultrows = array();
if (is_string($result)) {
$result = PMA_DBI_try_query($result, $link, $options);
}
// return empty array if result is empty or false
if (!$result) {
return $resultrows;
}
$fetch_function = 'PMA_DBI_fetch_assoc';
// no nested array if only one field is in result
if (NULL === $key && 1 === PMA_DBI_num_fields($result)) {
$value = 0;
$fetch_function = 'PMA_DBI_fetch_row';
}
// if $key is an integer use non associative mysql fetch function
if (is_int($key)) {
$fetch_function = 'PMA_DBI_fetch_row';
}
if (NULL === $key && NULL === $value) {
while ($row = $fetch_function($result)) {
$resultrows[] = $row;
}
} elseif (NULL === $key) {
while ($row = $fetch_function($result)) {
$resultrows[] = $row[$value];
}
} elseif (NULL === $value) {
while ($row = $fetch_function($result)) {
$resultrows[$row[$key]] = $row;
}
} else {
while ($row = $fetch_function($result)) {
$resultrows[$row[$key]] = $row[$value];
}
}
PMA_DBI_free_result($result);
return $resultrows;
}
