/* * Handle the input criteria and generate the query result * Form for displaying query results */ if (isset($_POST['zoom_submit']) && $_POST['criteriaColumnNames'][0] != 'pma_null' && $_POST['criteriaColumnNames'][1] != 'pma_null' && $_POST['criteriaColumnNames'][0] != $_POST['criteriaColumnNames'][1]) { //Query generation part $sql_query = $table_search->buildSqlQuery(); $sql_query .= ' LIMIT ' . $_POST['maxPlotLimit']; //Query execution part $result = $GLOBALS['dbi']->query($sql_query . ";", null, PMA_DatabaseInterface::QUERY_STORE); $fields_meta = $GLOBALS['dbi']->getFieldsMeta($result); $data = array(); while ($row = $GLOBALS['dbi']->fetchAssoc($result)) { //Need a row with indexes as 0,1,2 for the getUniqueCondition // hence using a temporary array $tmpRow = array(); foreach ($row as $val) { $tmpRow[] = $val; } //Get unique condition on each row (will be needed for row update) $uniqueCondition = PMA_Util::getUniqueCondition($result, count($table_search->getColumnNames()), $fields_meta, $tmpRow, true); //Append it to row array as where_clause $row['where_clause'] = $uniqueCondition[0]; $tmpData = array($_POST['criteriaColumnNames'][0] => $row[$_POST['criteriaColumnNames'][0]], $_POST['criteriaColumnNames'][1] => $row[$_POST['criteriaColumnNames'][1]], 'where_clause' => $uniqueCondition[0]); $tmpData[$dataLabel] = $dataLabel ? $row[$dataLabel] : ''; $data[] = $tmpData; } unset($tmpData); //Displays form for point data and scatter plot $response->addHTML($table_search->getZoomResultsForm($goto, $data)); }
/** * 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; }
/** * Prepare multi field edit/delete links * * @param integer &$dt_result the link id associated to the query * which results have to be displayed * @param array $analyzed_sql the analyzed query * @param string $del_link the display element - 'del_link' * * @return string $links_html html content * * @access private * * @see getTable() */ private function _getMultiRowOperationLinks(&$dt_result, $analyzed_sql, $del_link) { $links_html = ''; $url_query = $this->__get('url_query'); $delete_text = $del_link == self::DELETE_ROW ? __('Delete') : __('Kill'); $links_html .= '<img class="selectallarrow" width="38" height="22"' . ' src="' . $this->__get('pma_theme_image') . 'arrow_' . $this->__get('text_dir') . '.png' . '"' . ' alt="' . __('With selected:') . '" />'; $links_html .= '<input type="checkbox" ' . 'id="resultsForm_' . $this->__get('unique_id') . '_checkall" ' . 'class="checkall_box" title="' . __('Check All') . '" /> ' . '<label for="resultsForm_' . $this->__get('unique_id') . '_checkall">' . __('Check All') . '</label> ' . '<i style="margin-left: 2em">' . __('With selected:') . '</i>' . "\n"; $links_html .= PMA_Util::getButtonOrImage('submit_mult', 'mult_submit', 'submit_mult_change', __('Edit'), 'b_edit.png', 'edit'); $links_html .= PMA_Util::getButtonOrImage('submit_mult', 'mult_submit', 'submit_mult_delete', $delete_text, 'b_drop.png', 'delete'); if (isset($analyzed_sql[0]) && $analyzed_sql[0]['querytype'] == self::QUERY_TYPE_SELECT) { $links_html .= PMA_Util::getButtonOrImage('submit_mult', 'mult_submit', 'submit_mult_export', __('Export'), 'b_tblexport.png', 'export'); } $links_html .= "\n"; $links_html .= '<input type="hidden" name="sql_query"' . ' value="' . htmlspecialchars($this->__get('sql_query')) . '" />' . "\n"; if (!empty($url_query)) { $links_html .= '<input type="hidden" name="url_query"' . ' value="' . $url_query . '" />' . "\n"; } // fetch last row of the result set $GLOBALS['dbi']->dataSeek($dt_result, $this->__get('num_rows') - 1); $row = $GLOBALS['dbi']->fetchRow($dt_result); // $clause_is_unique is needed by getTable() to generate the proper param // in the multi-edit and multi-delete form list($where_clause, $clause_is_unique, $condition_array) = PMA_Util::getUniqueCondition($dt_result, $this->__get('fields_cnt'), $this->__get('fields_meta'), $row); unset($where_clause, $condition_array); // reset to first row for the loop in _getTableBody() $GLOBALS['dbi']->dataSeek($dt_result, 0); $links_html .= '<input type="hidden" name="clause_is_unique"' . ' value="' . $clause_is_unique . '" />' . "\n"; $links_html .= '</form>' . "\n"; return $links_html; }
/** * set $_SESSION for edit_next * * @param string $one_where_clause one where clause from where clauses array * * @return void */ function PMA_setSessionForEditNext($one_where_clause) { $local_query = 'SELECT * FROM ' . PMA_Util::backquote($GLOBALS['db']) . '.' . PMA_Util::backquote($GLOBALS['table']) . ' WHERE ' . str_replace('` =', '` >', $one_where_clause) . ' LIMIT 1;'; $res = $GLOBALS['dbi']->query($local_query); $row = $GLOBALS['dbi']->fetchRow($res); $meta = $GLOBALS['dbi']->getFieldsMeta($res); // must find a unique condition based on unique key, // not a combination of all fields list($unique_condition, $clause_is_unique) = PMA_Util::getUniqueCondition($res, count($meta), $meta, $row, true); if (!empty($unique_condition)) { $_SESSION['edit_next'] = $unique_condition; } unset($unique_condition, $clause_is_unique); }