diff options
Diffstat (limited to 'hugo/libraries/TableSearch.class.php')
| -rw-r--r-- | hugo/libraries/TableSearch.class.php | 1229 |
1 files changed, 1229 insertions, 0 deletions
diff --git a/hugo/libraries/TableSearch.class.php b/hugo/libraries/TableSearch.class.php new file mode 100644 index 0000000..65f45ad --- /dev/null +++ b/hugo/libraries/TableSearch.class.php @@ -0,0 +1,1229 @@ +<?php +/* vim: set expandtab sw=4 ts=4 sts=4: */ +/** + * Handles Table search and Zoom search + * + * @package PhpMyAdmin + */ +if (! defined('PHPMYADMIN')) { + exit; +} + +/** + * Class to handle normal-search + * and zoom-search in a table + * + * @package PhpMyAdmin + */ +class PMA_TableSearch +{ + /** + * Database name + * + * @access private + * @var string + */ + private $_db; + /** + * Table name + * + * @access private + * @var string + */ + private $_table; + /** + * Normal search or Zoom search + * + * @access private + * @var string + */ + private $_searchType; + /** + * Names of columns + * + * @access private + * @var array + */ + private $_columnNames; + /** + * Types of columns + * + * @access private + * @var array + */ + private $_columnTypes; + /** + * Collations of columns + * + * @access private + * @var array + */ + private $_columnCollations; + /** + * Null Flags of columns + * + * @access private + * @var array + */ + private $_columnNullFlags; + /** + * Whether a geometry column is present + * + * @access private + * @var boolean + */ + private $_geomColumnFlag; + /** + * Foreign Keys + * + * @access private + * @var array + */ + private $_foreigners; + + + /** + * Public Constructor + * + * @param string $db Database name + * @param string $table Table name + * @param string $searchType Whether normal or zoom search + */ + public function __construct($db, $table, $searchType) + { + $this->_db = $db; + $this->_table = $table; + $this->_searchType = $searchType; + $this->_columnNames = array(); + $this->_columnNullFlags = array(); + $this->_columnTypes = array(); + $this->_columnCollations = array(); + $this->_geomColumnFlag = false; + $this->_foreigners = array(); + // Loads table's information + $this->_loadTableInfo(); + } + + /** + * Returns Column names array + * + * @return array column names + */ + public function getColumnNames() + { + return $this->_columnNames; + } + + /** + * Gets all the columns of a table along with their types, collations + * and whether null or not. + * + * @return void + */ + private function _loadTableInfo() + { + // Gets the list and number of columns + $columns = PMA_DBI_get_columns($this->_db, $this->_table, null, true); + // Get details about the geometry fucntions + $geom_types = PMA_Util::getGISDatatypes(); + + foreach ($columns as $row) { + // set column name + $this->_columnNames[] = $row['Field']; + + $type = $row['Type']; + // check whether table contains geometric columns + if (in_array($type, $geom_types)) { + $this->_geomColumnFlag = true; + } + // reformat mysql query output + if (strncasecmp($type, 'set', 3) == 0 + || strncasecmp($type, 'enum', 4) == 0 + ) { + $type = str_replace(',', ', ', $type); + } else { + // strip the "BINARY" attribute, except if we find "BINARY(" because + // this would be a BINARY or VARBINARY column type + if (! preg_match('@BINARY[\(]@i', $type)) { + $type = preg_replace('@BINARY@i', '', $type); + } + $type = preg_replace('@ZEROFILL@i', '', $type); + $type = preg_replace('@UNSIGNED@i', '', $type); + $type = strtolower($type); + } + if (empty($type)) { + $type = ' '; + } + $this->_columnTypes[] = $type; + $this->_columnNullFlags[] = $row['Null']; + $this->_columnCollations[] + = ! empty($row['Collation']) && $row['Collation'] != 'NULL' + ? $row['Collation'] + : ''; + } // end for + + // Retrieve foreign keys + $this->_foreigners = PMA_getForeigners($this->_db, $this->_table); + } + + /** + * Sets the table header for displaying a table in query-by-example format. + * + * @return HTML content, the tags and content for table header + */ + private function _getTableHeader() + { + // Display the Function column only if there is at least one geometry column + $func = ''; + if ($this->_geomColumnFlag) { + $func = '<th>' . __('Function') . '</th>'; + } + + return '<thead> + <tr>' . $func . '<th>' . __('Column') . '</th> + <th>' . __('Type') . '</th> + <th>' . __('Collation') . '</th> + <th>' . __('Operator') . '</th> + <th>' . __('Value') . '</th> + </tr> + </thead>'; + } + + /** + * Returns an array with necessary configrations to create + * sub-tabs(Table Search and Zoom Search) in the table_select page. + * + * @return array Array containing configuration (icon, text, link, id, args) + * of sub-tabs for Table Search and Zoom search + */ + private function _getSubTabs() + { + $subtabs = array(); + $subtabs['search']['icon'] = 'b_search.png'; + $subtabs['search']['text'] = __('Table Search'); + $subtabs['search']['link'] = 'tbl_select.php'; + $subtabs['search']['id'] = 'tbl_search_id'; + $subtabs['search']['args']['pos'] = 0; + + $subtabs['zoom']['icon'] = 'b_props.png'; + $subtabs['zoom']['link'] = 'tbl_zoom_select.php'; + $subtabs['zoom']['text'] = __('Zoom Search'); + $subtabs['zoom']['id'] = 'zoom_search_id'; + + return $subtabs; + } + + /** + * Provides html elements for search criteria inputbox + * in case the column's type is geometrical + * + * @param int $column_index Column's index + * @param bool $in_fbs Whether we are in 'function based search' + * + * @return HTML elements. + */ + private function _getGeometricalInputBox($column_index, $in_fbs) + { + $html_output = '<input type="text" name="criteriaValues[' . $column_index . ']"' + . ' size="40" class="textfield" id="field_' . $column_index . '" />'; + + if ($in_fbs) { + $edit_url = 'gis_data_editor.php?' . PMA_generate_common_url(); + $edit_str = PMA_Util::getIcon('b_edit.png', __('Edit/Insert')); + $html_output .= '<span class="open_search_gis_editor">'; + $html_output .= PMA_Util::linkOrButton( + $edit_url, $edit_str, array(), false, false, '_blank' + ); + $html_output .= '</span>'; + } + return $html_output; + } + + /** + * Provides html elements for search criteria inputbox + * in case the column is a Foreign Key + * + * @param array $foreignData Foreign keys data + * @param string $column_name Column name + * @param int $column_index Column index + * @param array $titles Selected title + * @param int $foreignMaxLimit Max limit of displaying foreign elements + * @param array $criteriaValues Array of search criteria inputs + * @param string $column_id Column's inputbox's id + * @param bool $in_zoom_search_edit Whether we are in zoom search edit + * + * @return HTML elements. + */ + private function _getForeignKeyInputBox($foreignData, $column_name, + $column_index, $titles, $foreignMaxLimit, $criteriaValues, $column_id, + $in_zoom_search_edit = false + ) { + $html_output = ''; + if (is_array($foreignData['disp_row'])) { + $html_output .= '<select name="criteriaValues[' . $column_index . ']"' + . ' id="' . $column_id . $column_index .'">'; + $html_output .= PMA_foreignDropdown( + $foreignData['disp_row'], $foreignData['foreign_field'], + $foreignData['foreign_display'], '', $foreignMaxLimit + ); + $html_output .= '</select>'; + + } elseif ($foreignData['foreign_link'] == true) { + $html_output .= '<input type="text" id="' . $column_id . $column_index . '"' + . ' name="criteriaValues[' . $column_index . ']" id="field_' + . md5($column_name) . '[' . $column_index .']" class="textfield"' + . (isset($criteriaValues[$column_index]) + && is_string($criteriaValues[$column_index]) + ? (' value="' . $criteriaValues[$column_index] . '"') + : '') + . ' />'; + + $html_output .= <<<EOT +<a target="_blank" onclick="window.open(this.href, 'foreigners', 'width=640,height=240,scrollbars=yes'); return false" href="browse_foreigners.php? +EOT; + $html_output .= '' . PMA_generate_common_url($this->_db, $this->_table) + . '&field=' . urlencode($column_name) . '&fieldkey=' + . $column_index . '&fromsearch=1"'; + if ($in_zoom_search_edit) { + $html_output .= ' class="browse_foreign"'; + } + $html_output .= '>' . str_replace("'", "\'", $titles['Browse']) . '</a>'; + } + return $html_output; + } + + /** + * Provides html elements for search criteria inputbox + * in case the column is of ENUM or SET type + * + * @param int $column_index Column index + * @param array $criteriaValues Array of search criteria inputs + * @param string $column_type Column type + * @param string $column_id Column's inputbox's id + * @param bool $in_zoom_search_edit Whether we are in zoom search edit + * + * @return HTML elements. + */ + private function _getEnumSetInputBox($column_index, $criteriaValues, + $column_type, $column_id, $in_zoom_search_edit = false + ) { + $html_output = ''; + $value = explode( + ', ', + str_replace("'", '', substr($column_type, 5, -1)) + ); + $cnt_value = count($value); + + /* + * Enum in edit mode --> dropdown + * Enum in search mode --> multiselect + * Set in edit mode --> multiselect + * Set in search mode --> input (skipped here, so the 'else' + * section would handle it) + */ + if ((strncasecmp($column_type, 'enum', 4) && ! $in_zoom_search_edit) + || (strncasecmp($column_type, 'set', 3) && $in_zoom_search_edit) + ) { + $html_output .= '<select name="criteriaValues[' . ($column_index) + . ']" id="' . $column_id . $column_index .'">'; + } else { + $html_output .= '<select name="criteriaValues[' . $column_index . ']"' + . ' id="' . $column_id . $column_index . '" multiple="multiple"' + . ' size="' . min(3, $cnt_value) . '">'; + } + + //Add select options + for ($j = 0; $j < $cnt_value; $j++) { + if (isset($criteriaValues[$column_index]) + && is_array($criteriaValues[$column_index]) + && in_array($value[$j], $criteriaValues[$column_index]) + ) { + $html_output .= '<option value="' . $value[$j] . '" Selected>' + . $value[$j] . '</option>'; + } else { + $html_output .= '<option value="' . $value[$j] . '">' + . $value[$j] . '</option>'; + } + } // end for + $html_output .= '</select>'; + return $html_output; + } + + /** + * Creates the HTML content for: + * 1) Browsing foreign data for a column. + * 2) Creating elements for search criteria input on columns. + * + * @param array $foreignData Foreign keys data + * @param string $column_name Column name + * @param string $column_type Column type + * @param int $column_index Column index + * @param array $titles Selected title + * @param int $foreignMaxLimit Max limit of displaying foreign elements + * @param array $criteriaValues Array of search criteria inputs + * @param bool $in_fbs Whether we are in 'function based search' + * @param bool $in_zoom_search_edit Whether we are in zoom search edit + * + * @return string HTML content for viewing foreign data and elements + * for search criteria input. + */ + private function _getInputbox($foreignData, $column_name, $column_type, + $column_index, $titles, $foreignMaxLimit, $criteriaValues, $in_fbs = false, + $in_zoom_search_edit = false + ) { + $str = ''; + $column_type = (string)$column_type; + $column_id = ($in_zoom_search_edit) ? 'edit_fieldID_' : 'fieldID_'; + + // Get inputbox based on different column types + // (Foreign key, geometrical, enum) + if ($this->_foreigners && isset($this->_foreigners[$column_name])) { + $str .= $this->_getForeignKeyInputBox( + $foreignData, $column_name, $column_index, $titles, + $foreignMaxLimit, $criteriaValues, $column_id + ); + + } elseif (in_array($column_type, PMA_Util::getGISDatatypes())) { + $str .= $this->_getGeometricalInputBox($column_index, $in_fbs); + + } elseif (strncasecmp($column_type, 'enum', 4) == 0 + || (strncasecmp($column_type, 'set', 3) == 0 && $in_zoom_search_edit) + ) { + $str .= $this->_getEnumSetInputBox( + $column_index, $criteriaValues, $column_type, $column_id, + $in_zoom_search_edit = false + ); + + } else { + // other cases + $the_class = 'textfield'; + + if ($column_type == 'date') { + $the_class .= ' datefield'; + } elseif ($column_type == 'datetime' + || substr($column_type, 0, 9) == 'timestamp' + ) { + $the_class .= ' datetimefield'; + } elseif (substr($column_type, 0, 3) == 'bit') { + $the_class .= ' bit'; + } + + $str .= '<input type="text" name="criteriaValues[' . $column_index . ']"' + .' size="40" class="' . $the_class . '" id="' + . $column_id . $column_index . '"' + . (isset($criteriaValues[$column_index]) + && is_string($criteriaValues[$column_index]) + ? (' value="' . $criteriaValues[$column_index] . '"') + : '') + . ' />'; + } + return $str; + } + + /** + * Return the where clause in case column's type is ENUM. + * + * @param mixed $criteriaValues Search criteria input + * @param string $func_type Search function/operator + * + * @return string part of where clause. + */ + private function _getEnumWhereClause($criteriaValues, $func_type) + { + if (! is_array($criteriaValues)) { + $criteriaValues = explode(',', $criteriaValues); + } + $enum_selected_count = count($criteriaValues); + if ($func_type == '=' && $enum_selected_count > 1) { + $func_type = 'IN'; + $parens_open = '('; + $parens_close = ')'; + + } elseif ($func_type == '!=' && $enum_selected_count > 1) { + $func_type = 'NOT IN'; + $parens_open = '('; + $parens_close = ')'; + + } else { + $parens_open = ''; + $parens_close = ''; + } + $enum_where = '\'' + . PMA_Util::sqlAddSlashes($criteriaValues[0]) . '\''; + for ($e = 1; $e < $enum_selected_count; $e++) { + $enum_where .= ', \'' + . PMA_Util::sqlAddSlashes($criteriaValues[$e]) . '\''; + } + + return ' ' . $func_type . ' ' . $parens_open + . $enum_where . $parens_close; + } + + /** + * Return the where clause for a geometrical column. + * + * @param mixed $criteriaValues Search criteria input + * @param string $names Name of the column on which search is submitted + * @param string $func_type Search function/operator + * @param string $types Type of the field + * @param bool $geom_func Whether geometry functions should be applied + * + * @return string part of where clause. + */ + private function _getGeomWhereClause($criteriaValues, $names, + $func_type, $types, $geom_func = null + ) { + $geom_unary_functions = array( + 'IsEmpty' => 1, + 'IsSimple' => 1, + 'IsRing' => 1, + 'IsClosed' => 1, + ); + $where = ''; + + // Get details about the geometry functions + $geom_funcs = PMA_Util::getGISFunctions($types, true, false); + // New output type is the output type of the function being applied + $types = $geom_funcs[$geom_func]['type']; + + // If the function takes a single parameter + if ($geom_funcs[$geom_func]['params'] == 1) { + $backquoted_name = $geom_func . '(' . PMA_Util::backquote($names) . ')'; + } else { + // If the function takes two parameters + // create gis data from the criteria input + $gis_data = PMA_Util::createGISData($criteriaValues); + $where = $geom_func . '(' . PMA_Util::backquote($names) . ',' . $gis_data . ')'; + return $where; + } + + // If the where clause is something like 'IsEmpty(`spatial_col_name`)' + if (isset($geom_unary_functions[$geom_func]) + && trim($criteriaValues) == '' + ) { + $where = $backquoted_name; + + } elseif (in_array($types, PMA_Util::getGISDatatypes()) + && ! empty($criteriaValues) + ) { + // create gis data from the criteria input + $gis_data = PMA_Util::createGISData($criteriaValues); + $where = $backquoted_name . ' ' . $func_type . ' ' . $gis_data; + } + return $where; + } + + /** + * Return the where clause for query generation based on the inputs provided. + * + * @param mixed $criteriaValues Search criteria input + * @param string $names Name of the column on which search is submitted + * @param string $types Type of the field + * @param string $collations Field collation + * @param string $func_type Search function/operator + * @param bool $unaryFlag Whether operator unary or not + * @param bool $geom_func Whether geometry functions should be applied + * + * @return string generated where clause. + */ + private function _getWhereClause($criteriaValues, $names, $types, $collations, + $func_type, $unaryFlag, $geom_func = null + ) { + // If geometry function is set + if ($geom_func != null && trim($geom_func) != '') { + return $this->_getGeomWhereClause( + $criteriaValues, $names, $func_type, $types, $geom_func + ); + } + + $backquoted_name = PMA_Util::backquote($names); + $where = ''; + if ($unaryFlag) { + $criteriaValues = ''; + $where = $backquoted_name . ' ' . $func_type; + + } elseif (strncasecmp($types, 'enum', 4) == 0 && ! empty($criteriaValues)) { + $where = $backquoted_name; + $where .= $this->_getEnumWhereClause($criteriaValues, $func_type); + + } elseif ($criteriaValues != '') { + // For these types we quote the value. Even if it's another type + // (like INT), for a LIKE we always quote the value. MySQL converts + // strings to numbers and numbers to strings as necessary + // during the comparison + if (preg_match('@char|binary|blob|text|set|date|time|year@i', $types) + || strpos(' ' . $func_type, 'LIKE') + ) { + $quot = '\''; + } else { + $quot = ''; + } + + // LIKE %...% + if ($func_type == 'LIKE %...%') { + $func_type = 'LIKE'; + $criteriaValues = '%' . $criteriaValues . '%'; + } + if ($func_type == 'REGEXP ^...$') { + $func_type = 'REGEXP'; + $criteriaValues = '^' . $criteriaValues . '$'; + } + + if ($func_type == 'IN (...)' + || $func_type == 'NOT IN (...)' + || $func_type == 'BETWEEN' + || $func_type == 'NOT BETWEEN' + ) { + $func_type = str_replace(' (...)', '', $func_type); + + // quote values one by one + $values = explode(',', $criteriaValues); + foreach ($values as &$value) { + $value = $quot . PMA_Util::sqlAddSlashes(trim($value)) + . $quot; + } + + if ($func_type == 'BETWEEN' || $func_type == 'NOT BETWEEN') { + $where = $backquoted_name . ' ' . $func_type . ' ' + . (isset($values[0]) ? $values[0] : '') + . ' AND ' . (isset($values[1]) ? $values[1] : ''); + } else { + $where = $backquoted_name . ' ' . $func_type + . ' (' . implode(',', $values) . ')'; + } + } else { + if ($func_type == 'LIKE %...%' || $func_type == 'LIKE') { + $where = $backquoted_name . ' ' . $func_type . ' ' . $quot + . PMA_Util::sqlAddSlashes($criteriaValues, true) . $quot; + } else { + $where = $backquoted_name . ' ' . $func_type . ' ' . $quot + . PMA_Util::sqlAddSlashes($criteriaValues) . $quot; + } + } + } // end if + + return $where; + } + + /** + * Builds the sql search query from the post parameters + * + * @return string the generated SQL query + */ + public function buildSqlQuery() + { + $sql_query = 'SELECT '; + + // If only distinct values are needed + $is_distinct = (isset($_POST['distinct'])) ? 'true' : 'false'; + if ($is_distinct == 'true') { + $sql_query .= 'DISTINCT '; + } + + // if all column names were selected to display, we do a 'SELECT *' + // (more efficient and this helps prevent a problem in IE + // if one of the rows is edited and we come back to the Select results) + if (isset($_POST['zoom_submit']) || ! empty($_POST['displayAllColumns'])) { + $sql_query .= '* '; + } else { + $sql_query .= implode( + ', ', + PMA_Util::backquote($_POST['columnsToDisplay']) + ); + } // end if + + $sql_query .= ' FROM ' + . PMA_Util::backquote($_POST['table']); + $whereClause = $this->_generateWhereClause(); + $sql_query .= $whereClause; + + // if the search results are to be ordered + if (isset($_POST['orderByColumn']) && $_POST['orderByColumn'] != '--nil--') { + $sql_query .= ' ORDER BY ' + . PMA_Util::backquote($_POST['orderByColumn']) + . ' ' . $_POST['order']; + } // end if + return $sql_query; + } + + /** + * Generates the where clause for the SQL search query to be executed + * + * @return string the generated where clause + */ + private function _generateWhereClause() + { + if (isset($_POST['customWhereClause']) + && trim($_POST['customWhereClause']) != '' + ) { + return ' WHERE ' . $_POST['customWhereClause']; + } + + // If there are no search criteria set or no unary criteria operators, return + if (! isset($_POST['criteriaValues']) + && ! isset($_POST['criteriaColumnOperators']) + ) { + return ''; + } + + // else continue to form the where clause from column criteria values + $fullWhereClause = $charsets = array(); + reset($_POST['criteriaColumnOperators']); + while (list($column_index, $operator) = each($_POST['criteriaColumnOperators'])) { + list($charsets[$column_index]) = explode( + '_', $_POST['criteriaColumnCollations'][$column_index] + ); + $unaryFlag = $GLOBALS['PMA_Types']->isUnaryOperator($operator); + $tmp_geom_func = isset($geom_func[$column_index]) + ? $geom_func[$column_index] : null; + + $whereClause = $this->_getWhereClause( + $_POST['criteriaValues'][$column_index], + $_POST['criteriaColumnNames'][$column_index], + $_POST['criteriaColumnTypes'][$column_index], + $_POST['criteriaColumnCollations'][$column_index], + $operator, + $unaryFlag, + $tmp_geom_func + ); + + if ($whereClause) { + $fullWhereClause[] = $whereClause; + } + } // end while + + if ($fullWhereClause) { + return ' WHERE ' . implode(' AND ', $fullWhereClause); + } + return ''; + } + + /** + * Generates HTML for a geometrical function column to be displayed in table + * search selection form + * + * @param integer $column_index index of current column in $columnTypes array + * + * @return string the generated HTML + */ + private function _getGeomFuncHtml($column_index) + { + $html_output = ''; + // return if geometrical column is not present + if (! $this->_geomColumnFlag) { + return $html_output; + } + + /** + * Displays 'Function' column if it is present + */ + $html_output .= '<td>'; + $geom_types = PMA_Util::getGISDatatypes(); + // if a geometry column is present + if (in_array($this->_columnTypes[$column_index], $geom_types)) { + $html_output .= '<select class="geom_func" name="geom_func[' + . $column_index . ']">'; + // get the relevant list of GIS functions + $funcs = PMA_Util::getGISFunctions($this->_columnTypes[$column_index], true, true); + /** + * For each function in the list of functions, + * add an option to select list + */ + foreach ($funcs as $func_name => $func) { + $name = isset($func['display']) ? $func['display'] : $func_name; + $html_output .= '<option value="' . htmlspecialchars($name) . '">' + . htmlspecialchars($name) . '</option>'; + } + $html_output .= '</select>'; + } else { + $html_output .= ' '; + } + $html_output .= '</td>'; + return $html_output; + } + + /** + * Generates formatted HTML for extra search options in table search form + * + * @return string the generated HTML + */ + private function _getOptions() + { + $html_output = ''; + $html_output .= PMA_Util::getDivForSliderEffect( + 'searchoptions', __('Options') + ); + + /** + * Displays columns select list for selecting distinct columns in the search + */ + $html_output .= '<fieldset id="fieldset_select_fields">' + . '<legend>' . __('Select columns (at least one):') . '</legend>' + . '<select name="columnsToDisplay[]"' + . ' size="' . min(count($this->_columnNames), 10) . '"' + . ' multiple="multiple">'; + // Displays the list of the fields + foreach ($this->_columnNames as $each_field) { + $html_output .= ' ' + . '<option value="' . htmlspecialchars($each_field) . '"' + . ' selected="selected">' . htmlspecialchars($each_field) + . '</option>' . "\n"; + } // end for + $html_output .= '</select>' + . '<input type="checkbox" name="distinct" value="DISTINCT" id="oDistinct" />' + . '<label for="oDistinct">DISTINCT</label></fieldset>'; + + /** + * Displays input box for custom 'Where' clause to be used in the search + */ + $html_output .= '<fieldset id="fieldset_search_conditions">' + . '<legend>' . '<em>' . __('Or') . '</em> ' + . __('Add search conditions (body of the "where" clause):') . '</legend>'; + $html_output .= PMA_Util::showMySQLDocu( + 'SQL-Syntax', 'Functions' + ); + $html_output .= '<input type="text" name="customWhereClause"' + . ' class="textfield" size="64" />'; + $html_output .= '</fieldset>'; + + /** + * Displays option of changing default number of rows displayed per page + */ + $html_output .= '<fieldset id="fieldset_limit_rows">' + . '<legend>' . __('Number of rows per page') . '</legend>' + . '<input type="text" size="4" name="session_max_rows" ' + . 'value="' . $GLOBALS['cfg']['MaxRows'] . '" class="textfield" />' + . '</fieldset>'; + + /** + * Displays option for ordering search results + * by a column value (Asc or Desc) + */ + $html_output .= '<fieldset id="fieldset_display_order">' + . '<legend>' . __('Display order:') . '</legend>' + . '<select name="orderByColumn"><option value="--nil--"></option>'; + foreach ($this->_columnNames as $each_field) { + $html_output .= ' ' + . '<option value="' . htmlspecialchars($each_field) . '">' + . htmlspecialchars($each_field) . '</option>' . "\n"; + } // end for + $html_output .= '</select>'; + $choices = array( + 'ASC' => __('Ascending'), + 'DESC' => __('Descending') + ); + $html_output .= PMA_Util::getRadioFields( + 'order', $choices, 'ASC', false, true, "formelement" + ); + unset($choices); + + $html_output .= '</fieldset><br style="clear: both;"/></div>'; + return $html_output; + } + + /** + * Other search criteria like data label + * (for tbl_zoom_select.php) + * + * @param array $dataLabel Label for points in zoom plot + * + * @return string the generated html + */ + private function _getOptionsZoom($dataLabel) + { + $html_output = ''; + $html_output .= '<table class="data">'; + //Select options for datalabel + $html_output .= '<tr>'; + $html_output .= '<td><label for="dataLabel">' + . __("Use this column to label each point") . '</label></td>'; + $html_output .= '<td><select name="dataLabel" id="dataLabel" >' + . '<option value = "">' . __('None') . '</option>'; + for ($j = 0; $j < count($this->_columnNames); $j++) { + if (isset($dataLabel) + && $dataLabel == htmlspecialchars($this->_columnNames[$j]) + ) { + $html_output .= '<option value="' + . htmlspecialchars($this->_columnNames[$j]) . '" selected="selected">' + . htmlspecialchars($this->_columnNames[$j]) . '</option>'; + } else { + $html_output .= '<option value="' + . htmlspecialchars($this->_columnNames[$j]) . '" >' + . htmlspecialchars($this->_columnNames[$j]) . '</option>'; + } + } + $html_output .= '</select></td>'; + $html_output .= '</tr>'; + //Inputbox for changing default maximum rows to plot + $html_output .= '<tr>'; + $html_output .= '<td><label for="maxRowPlotLimit">' + . __("Maximum rows to plot") . '</label></td>'; + $html_output .= '<td>'; + $html_output .= '<input type="text" name="maxPlotLimit"' + . ' id="maxRowPlotLimit"' + . ' value="' . ((! empty($_POST['maxPlotLimit'])) + ? htmlspecialchars($_POST['maxPlotLimit']) + : $GLOBALS['cfg']['maxRowPlotLimit']) + . '" />'; + $html_output .= '</td></tr>'; + $html_output .= '</table>'; + return $html_output; + } + + /** + * Provides a column's type, collation, operators list, and crietria value + * to display in table search form + * + * @param integer $search_index Row number in table search form + * @param integer $column_index Column index in ColumnNames array + * + * @return array Array contaning column's properties + */ + public function getColumnProperties($search_index, $column_index) + { + $selected_operator = (isset($_POST['criteriaColumnOperators']) + ? $_POST['criteriaColumnOperators'][$search_index] : ''); + $entered_value = (isset($_POST['criteriaValues']) + ? $_POST['criteriaValues'] : ''); + $titles['Browse'] = PMA_Util::getIcon('b_browse.png', __('Browse foreign values')); + //Gets column's type and collation + $type = $this->_columnTypes[$column_index]; + $collation = $this->_columnCollations[$column_index]; + //Gets column's comparison operators depending on column type + $func = '<select name="criteriaColumnOperators[' . $search_index . ']">'; + $func .= $GLOBALS['PMA_Types']->getTypeOperatorsHtml( + preg_replace('@\(.*@s', '', $this->_columnTypes[$column_index]), + $this->_columnNullFlags[$column_index], $selected_operator + ); + $func .= '</select>'; + //Gets link to browse foreign data(if any) and criteria inputbox + $foreignData = PMA_getForeignData( + $this->_foreigners, $this->_columnNames[$column_index], false, '', '' + ); + $value = $this->_getInputbox( + $foreignData, $this->_columnNames[$column_index], $type, $search_index, + $titles, $GLOBALS['cfg']['ForeignKeyMaxLimit'], $entered_value + ); + return array( + 'type' => $type, + 'collation' => $collation, + 'func' => $func, + 'value' => $value + ); + } + + /** + * Provides the search form's table row in case of Normal Search + * (for tbl_select.php) + * + * @return string the generated table row + */ + private function _getRowsNormal() + { + $odd_row = true; + $html_output = ''; + // for every column present in table + for ($column_index = 0; $column_index < count($this->_columnNames); $column_index++) { + $html_output .= '<tr class="noclick ' . ($odd_row ? 'odd' : 'even') . '">'; + $odd_row = !$odd_row; + //If 'Function' column is present + $html_output .= $this->_getGeomFuncHtml($column_index); + //Displays column's name, type, collation and value + $html_output .= '<th>' + . htmlspecialchars($this->_columnNames[$column_index]) . '</th>'; + $properties = $this->getColumnProperties($column_index, $column_index); + $html_output .= '<td>' . $properties['type'] . '</td>'; + $html_output .= '<td>' . $properties['collation'] . '</td>'; + $html_output .= '<td>' . $properties['func'] . '</td>'; + $html_output .= '<td>' . $properties['value'] . '</td>'; + $html_output .= '</tr>'; + //Displays hidden fields + $html_output .= '<tr><td>'; + $html_output .= '<input type="hidden"' + . ' name="criteriaColumnNames[' . $column_index . ']"' + . ' value="' . htmlspecialchars($this->_columnNames[$column_index]) + . '" />'; + $html_output .= '<input type="hidden"' + . ' name="criteriaColumnTypes[' . $column_index . ']"' + . ' value="' . $this->_columnTypes[$column_index] . '" />'; + $html_output .= '<input type="hidden"' + . ' name="criteriaColumnCollations[' . $column_index . ']"' + . ' value="' . $this->_columnCollations[$column_index] . '" />'; + $html_output .= '</td></tr>'; + } // end for + + return $html_output; + } + + /** + * Provides the search form's table row in case of Zoom Search + * (for tbl_zoom_select.php) + * + * @return string the generated table row + */ + private function _getRowsZoom() + { + $odd_row = true; + $html_output = ''; + /** + * Get already set search criteria (if any) + */ + + //Displays column rows for search criteria input + for ($i = 0; $i < 4; $i++) { + //After X-Axis and Y-Axis column rows, display additional criteria option + if ($i == 2) { + $html_output .= '<tr><td>'; + $html_output .= __("Additional search criteria"); + $html_output .= '</td></tr>'; + } + $html_output .= '<tr class="noclick ' . ($odd_row ? 'odd' : 'even') . '">'; + $odd_row = ! $odd_row; + //Select options for column names + $html_output .= '<th><select name="criteriaColumnNames[]" id="' + . 'tableid_' . $i . '" >'; + $html_output .= '<option value="' . 'pma_null' . '">' . __('None') + . '</option>'; + for ($j = 0 ; $j < count($this->_columnNames); $j++) { + if (isset($_POST['criteriaColumnNames'][$i]) + && $_POST['criteriaColumnNames'][$i] == htmlspecialchars($this->_columnNames[$j]) + ) { + $html_output .= '<option value="' + . htmlspecialchars($this->_columnNames[$j]) . '" selected="selected">' + . htmlspecialchars($this->_columnNames[$j]) . '</option>'; + } else { + $html_output .= '<option value="' + . htmlspecialchars($this->_columnNames[$j]) . '">' + . htmlspecialchars($this->_columnNames[$j]) . '</option>'; + } + } + $html_output .= '</select></th>'; + if (isset($_POST['criteriaColumnNames']) + && $_POST['criteriaColumnNames'][$i] != 'pma_null' + ) { + $key = array_search( + $_POST['criteriaColumnNames'][$i], + $this->_columnNames + ); + $properties = $this->getColumnProperties($i, $key); + $type[$i] = $properties['type']; + $collation[$i] = $properties['collation']; + $func[$i] = $properties['func']; + $value[$i] = $properties['value']; + } + //Column type + $html_output .= '<td>' . (isset($type[$i]) ? $type[$i] : '') . '</td>'; + //Column Collation + $html_output .= '<td>' . (isset($collation[$i]) ? $collation[$i] : '') + . '</td>'; + //Select options for column operators + $html_output .= '<td>' . (isset($func[$i]) ? $func[$i] : '') . '</td>'; + //Inputbox for search criteria value + $html_output .= '<td>' . (isset($value[$i]) ? $value[$i] : '') . '</td>'; + $html_output .= '</tr>'; + //Displays hidden fields + $html_output .= '<tr><td>'; + $html_output .= '<input type="hidden" name="criteriaColumnTypes[' . $i . ']"' + . ' id="types_' . $i . '" '; + if (isset($_POST['criteriaColumnTypes'][$i])) { + $html_output .= 'value="' . $_POST['criteriaColumnTypes'][$i] . '" '; + } + $html_output .= '/>'; + $html_output .= '<input type="hidden" name="criteriaColumnCollations[' + . $i . ']" id="collations_' . $i . '" />'; + $html_output .= '</td></tr>'; + }//end for + return $html_output; + } + + /** + * Generates HTML for displaying fields table in search form + * + * @return string the generated HTML + */ + private function _getFieldsTableHtml() + { + $html_output = ''; + $html_output .= '<table class="data"' + . ($this->_searchType == 'zoom' ? ' id="tableFieldsId"' : '') . '>'; + $html_output .= $this->_getTableHeader(); + $html_output .= '<tbody>'; + + if ($this->_searchType == 'zoom') { + $html_output .= $this->_getRowsZoom(); + } else { + $html_output .= $this->_getRowsNormal(); + } + + $html_output .= '</tbody></table>'; + return $html_output; + } + + /** + * Provides the form tag for table search form + * (normal search or zoom search) + * + * @param string $goto Goto URL + * + * @return string the HTML for form tag + */ + private function _getFormTag($goto) + { + $html_output = ''; + $scriptName = ($this->_searchType == 'zoom' ? 'tbl_zoom_select.php' : 'tbl_select.php'); + $formId = ($this->_searchType == 'zoom' ? 'zoom_search_form' : 'tbl_search_form'); + + $html_output .= '<form method="post" action="' . $scriptName . '" ' + . 'name="insertForm" id="' . $formId . '" ' + . 'class="ajax"' . '>'; + + $html_output .= PMA_generate_common_hidden_inputs($this->_db, $this->_table); + $html_output .= '<input type="hidden" name="goto" value="' . $goto . '" />'; + $html_output .= '<input type="hidden" name="back" value="' . $scriptName + . '" />'; + + return $html_output; + } + + /** + * Generates the table search form under table search tab + * + * @param string $goto Goto URL + * @param string $dataLabel Label for points in zoom plot + * + * @return string the generated HTML for table search form + */ + public function getSelectionForm($goto, $dataLabel = null) + { + $url_params = array(); + $url_params['db'] = $this->_db; + $url_params['table'] = $this->_table; + + $html_output = '<ul id="topmenu2">'; + foreach ($this->_getSubTabs() as $tab) { + $html_output .= PMA_Util::getHtmlTab($tab, $url_params); + } + $html_output .= '</ul>'; + $html_output .= '<div class="clearfloat"></div>'; + + $html_output .= $this->_getFormTag($goto); + + if ($this->_searchType == 'zoom') { + $html_output .= '<fieldset id="fieldset_zoom_search">'; + $html_output .= '<fieldset id="inputSection">'; + $html_output .= '<legend>' + . __('Do a "query by example" (wildcard: "%") for two different columns') + . '</legend>'; + $html_output .= $this->_getFieldsTableHtml(); + $html_output .= $this->_getOptionsZoom($dataLabel); + $html_output .= '</fieldset>'; + $html_output .= '</fieldset>'; + } else { + $html_output .= '<fieldset id="fieldset_table_search">'; + $html_output .= '<fieldset id="fieldset_table_qbe">'; + $html_output .= '<legend>' + . __('Do a "query by example" (wildcard: "%")') + . '</legend>'; + $html_output .= $this->_getFieldsTableHtml(); + $html_output .= '<div id="gis_editor"></div>'; + $html_output .= '<div id="popup_background"></div>'; + $html_output .= '</fieldset>'; + $html_output .= $this->_getOptions(); + $html_output .= '</fieldset>'; + } + + /** + * Displays selection form's footer elements + */ + $html_output .= '<fieldset class="tblFooters">'; + $html_output .= '<input type="submit" name="' + . ($this->_searchType == 'zoom' ? 'zoom_submit' : 'submit') + . ($this->_searchType == 'zoom' ? '" id="inputFormSubmitId"' : '" ') + . 'value="' . __('Go') . '" />'; + $html_output .= '</fieldset></form>'; + $html_output .= '<div id="sqlqueryresults"></div>'; + return $html_output; + } + + /** + * Provides form for displaying point data and also the scatter plot + * (for tbl_zoom_select.php) + * + * @param string $goto Goto URL + * @param array $data Array containing SQL query data + * + * @return string form's html + */ + public function getZoomResultsForm($goto, $data) + { + $html_output = ''; + $titles['Browse'] = PMA_Util::getIcon('b_browse.png', __('Browse foreign values')); + $html_output .= '<form method="post" action="tbl_zoom_select.php"' + . ' name="displayResultForm" id="zoom_display_form"' + . ' class="ajax"' . '>'; + $html_output .= PMA_generate_common_hidden_inputs($this->_db, $this->_table); + $html_output .= '<input type="hidden" name="goto" value="' . $goto . '" />'; + $html_output .= '<input type="hidden" name="back" value="tbl_zoom_select.php" />'; + + $html_output .= '<fieldset id="displaySection">'; + $html_output .= '<legend>' . __('Browse/Edit the points') . '</legend>'; + + //JSON encode the data(query result) + $html_output .= '<center>'; + if (isset($_POST['zoom_submit']) && ! empty($data)) { + $html_output .= '<div id="resizer">'; + $html_output .= '<center><a href="#" onclick="displayHelp();">' + . __('How to use') . '</a></center>'; + $html_output .= '<div id="querydata" style="display:none">' + . json_encode($data) . '</div>'; + $html_output .= '<div id="querychart"></div>'; + $html_output .= '<button class="button-reset">' + . __('Reset zoom') . '</button>'; + $html_output .= '</div>'; + } + $html_output .= '</center>'; + + //Displays rows in point edit form + $html_output .= '<div id="dataDisplay" style="display:none">'; + $html_output .= '<table><thead>'; + $html_output .= '<tr>'; + $html_output .= '<th>' . __('Column') . '</th>' + . '<th>' . __('Null') . '</th>' + . '<th>' . __('Value') . '</th>'; + $html_output .= '</tr>'; + $html_output .= '</thead>'; + + $html_output .= '<tbody>'; + $odd_row = true; + for ($column_index = 0; $column_index < count($this->_columnNames); $column_index++) { + $fieldpopup = $this->_columnNames[$column_index]; + $foreignData = PMA_getForeignData($this->_foreigners, $fieldpopup, false, '', ''); + $html_output .= '<tr class="noclick ' . ($odd_row ? 'odd' : 'even') . '">'; + $odd_row = ! $odd_row; + //Display column Names + $html_output .= '<th>' . htmlspecialchars($this->_columnNames[$column_index]) + . '</th>'; + //Null checkbox if column can be null + $html_output .= '<th>' . (($this->_columnNullFlags[$column_index] == 'YES') + ? '<input type="checkbox" class="checkbox_null"' + . ' name="criteriaColumnNullFlags[' . $column_index . ']"' + . ' id="edit_fields_null_id_' . $column_index . '" />' + : ''); + $html_output .= '</th>'; + //Column's Input box + $html_output .= '<th>'; + $html_output .= $this->_getInputbox( + $foreignData, $fieldpopup, $this->_columnTypes[$column_index], + $column_index, $titles, $GLOBALS['cfg']['ForeignKeyMaxLimit'], + '', false, true + ); + $html_output .= '</th></tr>'; + } + $html_output .= '</tbody></table>'; + $html_output .= '</div>'; + $html_output .= '<input type="hidden" id="queryID" name="sql_query" />'; + $html_output .= '</form>'; + return $html_output; + } +} +?> |