2 /* vim: set expandtab sw=4 ts=4 sts=4: */
4 * Functions for the table-search page and zoom-search page
6 * Funtion PMA_tbl_getFields : Returns the fields of a table
7 * Funtion PMA_tbl_search_getWhereClause : Returns the where clause for query generation
12 require_once 'url_generating.lib.php';
15 * Sets the title for foreign keys display link.
17 * @param mixed $propertiesIconic Type of icon property
18 * @param string $pmaThemeImage Icon Image
20 * @return string $str Value of the Title
22 function PMA_tbl_setTitle($propertiesIconic, $pmaThemeImage)
24 if ($propertiesIconic == true) {
25 $str = '<img class="icon" width="16" height="16" src="' . $pmaThemeImage
26 .'b_browse.png" alt="' . __('Browse foreign values') . '" title="'
27 . __('Browse foreign values') . '" />';
29 if ($propertiesIconic === 'both') {
30 $str .= __('Browse foreign values');
35 return __('Browse foreign values');
40 * Gets all the fields of a table along with their types, collations
41 * and whether null or not.
43 * @param string $table Selected table
44 * @param string $db Selected database
46 * @return array Array containing the field list, field types, collations
49 function PMA_tbl_getFields($table,$db)
51 // Gets the list and number of fields
52 $fields = PMA_DBI_get_columns($db, $table, true);
53 $fields_list = $fields_null = $fields_type = $fields_collation = array();
54 $geom_column_present = false;
55 $geom_types = PMA_getGISDatatypes();
57 foreach ($fields as $row) {
58 $fields_list[] = $row['Field'];
61 // check whether table contains geometric columns
62 if (in_array($type, $geom_types)) {
63 $geom_column_present = true;
66 // reformat mysql query output
67 if (strncasecmp($type, 'set', 3) == 0
68 ||
strncasecmp($type, 'enum', 4) == 0
70 $type = str_replace(',', ', ', $type);
72 // strip the "BINARY" attribute, except if we find "BINARY(" because
73 // this would be a BINARY or VARBINARY field type
74 if (!preg_match('@BINARY[\(]@i', $type)) {
75 $type = preg_replace('@BINARY@i', '', $type);
77 $type = preg_replace('@ZEROFILL@i', '', $type);
78 $type = preg_replace('@UNSIGNED@i', '', $type);
80 $type = strtolower($type);
85 $fields_null[] = $row['Null'];
86 $fields_type[] = $type;
87 $fields_collation[] = ! empty($row['Collation']) && $row['Collation'] != 'NULL'
92 return array($fields_list, $fields_type, $fields_collation, $fields_null, $geom_column_present);
96 * Sets the table header for displaying a table in query-by-example format.
98 * @param bool $geom_column_present whether a geometry column is present
100 * @return HTML content, the tags and content for table header
102 function PMA_tbl_setTableHeader($geom_column_present = false)
104 // Display the Function column only if there is alteast one geomety colum
106 if ($geom_column_present) {
107 $func = '<th>' . __('Function') . '</th>';
111 <tr>' . $func . '<th>' . __('Column') . '</th>
112 <th>' . __('Type') . '</th>
113 <th>' . __('Collation') . '</th>
114 <th>' . __('Operator') . '</th>
115 <th>' . __('Value') . '</th>
121 * Returns an array with necessary configrations to create
122 * sub-tabs(Table Search and Zoom Search) in the table_select page.
124 * @return array Array containing configuration (icon, text, link, id, args)
125 * of sub-tabs for Table Search and Zoom search
127 function PMA_tbl_getSubTabs()
130 $subtabs['search']['icon'] = 'b_search.png';
131 $subtabs['search']['text'] = __('Table Search');
132 $subtabs['search']['link'] = 'tbl_select.php';
133 $subtabs['search']['id'] = 'tbl_search_id';
134 $subtabs['search']['args']['pos'] = 0;
136 $subtabs['zoom']['icon'] = 'b_props.png';
137 $subtabs['zoom']['link'] = 'tbl_zoom_select.php';
138 $subtabs['zoom']['text'] = __('Zoom Search');
139 $subtabs['zoom']['id'] = 'zoom_search_id';
145 * Creates the HTML content for:
146 * 1) Browsing foreign data for a field.
147 * 2) Creating elements for search criteria input on fields.
149 * @param array $foreigners Array of foreign keys
150 * @param array $foreignData Foreign keys data
151 * @param string $field Column name
152 * @param string $tbl_fields_type Column type
153 * @param int $i Column index
154 * @param string $db Selected database
155 * @param string $table Selected table
156 * @param array $titles Selected title
157 * @param int $foreignMaxLimit Max limit of displaying foreign elements
158 * @param array $fields Array of search criteria inputs
159 * @param bool $in_fbs Whether we are in 'function based search'
161 * @return string HTML content for viewing foreing data and elements
162 * for search criteria input.
164 function PMA_getForeignFields_Values($foreigners, $foreignData, $field, $tbl_fields_type, $i, $db, $table, $titles, $foreignMaxLimit, $fields, $in_fbs = false)
167 if ($foreigners && isset($foreigners[$field]) && is_array($foreignData['disp_row'])) {
168 // f o r e i g n k e y s
169 $str .= '<select name="fields[' . $i . ']" id="fieldID_' . $i .'">' . "\n";
170 // go back to first row
171 // here, the 4th parameter is empty because there is no current
172 // value of data for the dropdown (the search page initial values
173 // are displayed empty)
174 $str .= PMA_foreignDropdown(
175 $foreignData['disp_row'], $foreignData['foreign_field'],
176 $foreignData['foreign_display'], '', $foreignMaxLimit
178 $str .= '</select>' . "\n";
180 } elseif ($foreignData['foreign_link'] == true) {
181 if(isset($fields[$i]) && is_string($fields[$i])){
182 $str .= '<input type="text" id="fieldID_' . $i .'"name="fields[' . $i . '] " value="' . $fields[$i] . '"';
183 'id="field_' . md5($field) . '[' . $i .']"
184 class="textfield"/>' ;
187 $str .= '<input type="text" id="fieldID_' . $i .'"name="fields[' . $i . '] "';
188 'id="field_' . md5($field) . '[' . $i .']"
189 class="textfield" />' ;
192 <?php
$str .= '<script type="text/javascript">';
195 <a target="_blank" onclick="window.open(this.href, 'foreigners', 'width=640,height=240,scrollbars=yes'); return false" href="browse_foreigners.php?
197 $str .= '' . PMA_generate_common_url($db, $table) . '&field=' . urlencode($field) . '&fieldkey=' . $i . '">' . str_replace("'", "\'", $titles['Browse']) . '</a>';
201 } elseif (in_array($tbl_fields_type[$i], PMA_getGISDatatypes())) {
203 $str .= '<input type="text" name="fields[' . $i . ']"'
204 .' size="40" class="textfield" id="field_' . $i . '" />' . "\n";
207 $edit_url = 'gis_data_editor.php?' . PMA_generate_common_url();
208 $edit_str = PMA_getIcon('b_edit.png', __('Edit/Insert'), true);
209 $str .= '<span class="open_search_gis_editor">';
210 $str .= PMA_linkOrButton($edit_url, $edit_str, array(), false, false, '_blank');
214 } elseif (strncasecmp($tbl_fields_type[$i], 'enum', 4) == 0) {
216 $enum_value=explode(', ', str_replace("'", '', substr($tbl_fields_type[$i], 5, -1)));
217 $cnt_enum_value = count($enum_value);
218 $str .= '<select name="fields[' . ($i) . '][]" id="fieldID_' . $i .'"'
219 .' multiple="multiple" size="' . min(3, $cnt_enum_value) . '">' . "\n";
221 for ($j = 0; $j < $cnt_enum_value; $j++
) {
222 if (isset($fields[$i])
223 && is_array($fields[$i])
224 && in_array($enum_value[$j], $fields[$i])
226 $str .= '<option value="' . $enum_value[$j] . '" Selected>'
227 . $enum_value[$j] . '</option>';
229 $str .= '<option value="' . $enum_value[$j] . '">'
230 . $enum_value[$j] . '</option>';
233 $str .= '</select>' . "\n";
236 // o t h e r c a s e s
237 $the_class = 'textfield';
238 $type = $tbl_fields_type[$i];
240 if ($type == 'date') {
241 $the_class .= ' datefield';
242 } elseif ($type == 'datetime' ||
substr($type, 0, 9) == 'timestamp') {
243 $the_class .= ' datetimefield';
246 if (isset($fields[$i]) && is_string($fields[$i])) {
247 $str .= '<input type="text" name="fields[' . $i . ']"'
248 .' size="40" class="' . $the_class . '" id="fieldID_'
249 . $i .'" value = "' . $fields[$i] . '"/>' . "\n";
251 $str .= '<input type="text" name="fields[' . $i . ']"'
252 .' size="40" class="' . $the_class . '" id="fieldID_'
260 * Return the where clause for query generation based on the inputs provided.
262 * @param mixed $fields Search criteria input
263 * @param string $names Name of the column on which search is submitted
264 * @param string $types Type of the field
265 * @param string $collations Field collation
266 * @param string $func_type Search fucntion/operator
267 * @param bool $unaryFlag Whether operator unary or not
268 * @param bool $geom_func Whether geometry functions should be applied
270 * @return string HTML content for viewing foreing data and elements
271 * for search criteria input.
273 function PMA_tbl_search_getWhereClause($fields, $names, $types, $collations, $func_type, $unaryFlag, $geom_func = null)
276 * @todo move this to a more apropriate place
278 $geom_unary_functions = array(
286 // If geometry function is set apply it to the field name
287 if ($geom_func != null && trim($geom_func) != '') {
288 // Get details about the geometry fucntions
289 $geom_funcs = PMA_getGISFunctions($types, true, false);
291 // If the function takes a single parameter
292 if ($geom_funcs[$geom_func]['params'] == 1) {
293 $backquoted_name = $geom_func . '(' . PMA_backquote($names) . ')';
295 // If the function takes two parameters
296 // create gis data from the string
297 $gis_data = PMA_createGISData($fields);
299 $w = $geom_func . '(' . PMA_backquote($names) . ',' . $gis_data . ')';
303 // New output type is the output type of the function being applied
304 $types = $geom_funcs[$geom_func]['type'];
306 // If the where clause is something like 'IsEmpty(`spatial_col_name`)'
307 if (isset($geom_unary_functions[$geom_func]) && trim($fields) == '') {
308 $w = $backquoted_name;
312 $backquoted_name = PMA_backquote($names);
317 $w = $backquoted_name . ' ' . $func_type;
319 } elseif (in_array($types, PMA_getGISDatatypes()) && ! empty($fields)) {
320 // create gis data from the string
321 $gis_data = PMA_createGISData($fields);
322 $w = $backquoted_name . ' ' . $func_type . ' ' . $gis_data;
324 } elseif (strncasecmp($types, 'enum', 4) == 0) {
325 if (!empty($fields)) {
326 if (! is_array($fields)) {
327 $fields = explode(',', $fields);
329 $enum_selected_count = count($fields);
330 if ($func_type == '=' && $enum_selected_count > 1) {
335 } elseif ($func_type == '!=' && $enum_selected_count > 1) {
336 $func_type = 'NOT IN';
344 $enum_where = '\'' . PMA_sqlAddslashes($fields[0]) . '\'';
345 for ($e = 1; $e < $enum_selected_count; $e++
) {
346 $enum_where .= ', \'' . PMA_sqlAddslashes($fields[$e]) . '\'';
349 $w = $backquoted_name . ' ' . $func_type . ' ' . $parens_open . $enum_where . $parens_close;
352 } elseif ($fields != '') {
353 // For these types we quote the value. Even if it's another type (like INT),
354 // for a LIKE we always quote the value. MySQL converts strings to numbers
355 // and numbers to strings as necessary during the comparison
356 if (preg_match('@char|binary|blob|text|set|date|time|year@i', $types)
357 ||
strpos(' ' . $func_type, 'LIKE')
365 if ($func_type == 'LIKE %...%') {
367 $fields = '%' . $fields . '%';
369 if ($func_type == 'REGEXP ^...$') {
370 $func_type = 'REGEXP';
371 $fields = '^' . $fields . '$';
374 if ($func_type == 'IN (...)'
375 ||
$func_type == 'NOT IN (...)'
376 ||
$func_type == 'BETWEEN'
377 ||
$func_type == 'NOT BETWEEN'
379 $func_type = str_replace(' (...)', '', $func_type);
381 // quote values one by one
382 $values = explode(',', $fields);
383 foreach ($values as &$value) {
384 $value = $quot . PMA_sqlAddslashes(trim($value)) . $quot;
387 if ($func_type == 'BETWEEN' ||
$func_type == 'NOT BETWEEN') {
388 $w = $backquoted_name . ' ' . $func_type . ' ' . (isset($values[0]) ?
$values[0] : '')
389 . ' AND ' . (isset($values[1]) ?
$values[1] : '');
391 $w = $backquoted_name . ' ' . $func_type . ' (' . implode(',', $values) . ')';
394 $w = $backquoted_name . ' ' . $func_type . ' ' . $quot . PMA_sqlAddslashes($fields) . $quot;;
402 * Formats a SVG plot for the query results.
404 * @param array $data Data for the status chart
405 * @param array &$settings Settings used to generate the chart
407 * @return string HTML and JS code for the SVG plot
409 function PMA_SVG_scatter_plot($data, &$settings)
411 include_once './libraries/svg_plot/pma_scatter_plot.php';
417 $scatter_plot = new PMA_Scatter_Plot($data, $settings);
419 if ($settings != null) {
420 foreach ($scatter_plot->getSettings() as $setting => $val) {
421 if (! isset($settings[$setting])) {
422 $settings[$setting] = $val;
426 return $scatter_plot->asSVG();