libraries/tbl_select.lib.php

   1 <?php
   2 /* vim: set expandtab sw=4 ts=4 sts=4: */
   3 /**
   4  * Functions for the table-search page and zoom-search page
   5  *
   6  * @package PhpMyAdmin
   7  */
   8
   9 require_once 'url_generating.lib.php';
  10
  11 /**
  12  * Gets all the fields of a table along with their types, collations
  13  * and whether null or not.
  14  *
  15  * @param string $db    Selected database
  16  * @param string $table Selected table
  17  *
  18  * @return array Array containing the field list, field types, collations
  19  * and null constraint
  20  */
  21 function PMA_tbl_getFields($db, $table)
  22 {
  23     // Gets the list and number of fields
  24     $fields = PMA_DBI_get_columns($db, $table, null, true);
  25     $fields_list = $fields_null = $fields_type = $fields_collation = array();
  26     $geom_column_present = false;
  27     $geom_types = PMA_getGISDatatypes();
  28
  29     foreach ($fields as $key => $row) {
  30         $fields_list[] = $row['Field'];
  31         $type          = $row['Type'];
  32
  33         // check whether table contains geometric columns
  34         if (in_array($type, $geom_types)) {
  35             $geom_column_present = true;
  36         }
  37
  38         // reformat mysql query output
  39         if (strncasecmp($type, 'set', 3) == 0
  40             || strncasecmp($type, 'enum', 4) == 0
  41         ) {
  42             $type = str_replace(',', ', ', $type);
  43         } else {
  44             // strip the "BINARY" attribute, except if we find "BINARY(" because
  45             // this would be a BINARY or VARBINARY field type
  46             if (!preg_match('@BINARY[\(]@i', $type)) {
  47                 $type = preg_replace('@BINARY@i', '', $type);
  48             }
  49             $type = preg_replace('@ZEROFILL@i', '', $type);
  50             $type = preg_replace('@UNSIGNED@i', '', $type);
  51
  52             $type = strtolower($type);
  53         }
  54         if (empty($type)) {
  55             $type = '&nbsp;';
  56         }
  57         $fields_null[] = $row['Null'];
  58         $fields_type[] = $type;
  59         $fields_collation[] = ! empty($row['Collation']) && $row['Collation'] != 'NULL'
  60             ? $row['Collation']
  61             : '';
  62     } // end while
  63
  64     return array($fields_list, $fields_type, $fields_collation, $fields_null, $geom_column_present);
  65 }
  66
  67 /**
  68  * Sets the table header for displaying a table in query-by-example format.
  69  *
  70  * @param bool $geom_column_present whether a geometry column is present
  71  *
  72  * @return HTML content, the tags and content for table header
  73  */
  74 function PMA_tbl_setTableHeader($geom_column_present = false)
  75 {
  76     // Display the Function column only if there is alteast one geomety colum
  77     $func = '';
  78     if ($geom_column_present) {
  79         $func = '<th>' . __('Function') . '</th>';
  80     }
  81
  82     return '<thead>
  83         <tr>' . $func . '<th>' .  __('Column') . '</th>
  84         <th>' .  __('Type') . '</th>
  85         <th>' .  __('Collation') . '</th>
  86         <th>' .  __('Operator') . '</th>
  87         <th>' .  __('Value') . '</th>
  88         </tr>
  89         </thead>';
  90 }
  91
  92 /**
  93  * Returns an array with necessary configrations to create
  94  * sub-tabs(Table Search and Zoom Search) in the table_select page.
  95  *
  96  * @return array Array containing configuration (icon, text, link, id, args)
  97  * of sub-tabs for Table Search and Zoom search
  98  */
  99 function PMA_tbl_getSubTabs()
 100 {
 101     $subtabs = array();
 102     $subtabs['search']['icon'] = 'b_search.png';
 103     $subtabs['search']['text'] = __('Table Search');
 104     $subtabs['search']['link'] = 'tbl_select.php';
 105     $subtabs['search']['id'] = 'tbl_search_id';
 106     $subtabs['search']['args']['pos'] = 0;
 107
 108     $subtabs['zoom']['icon'] = 'b_props.png';
 109     $subtabs['zoom']['link'] = 'tbl_zoom_select.php';
 110     $subtabs['zoom']['text'] = __('Zoom Search');
 111     $subtabs['zoom']['id'] = 'zoom_search_id';
 112
 113     return $subtabs;
 114 }
 115
 116 /**
 117  * Creates the HTML content for:
 118  * 1) Browsing foreign data for a field.
 119  * 2) Creating elements for search criteria input on fields.
 120  *
 121  * @param array  $foreigners      Array of foreign keys
 122  * @param array  $foreignData     Foreign keys data
 123  * @param string $field           Column name
 124  * @param string $tbl_fields_type Column type
 125  * @param int    $i               Column index
 126  * @param string $db              Selected database
 127  * @param string $table           Selected table
 128  * @param array  $titles          Selected title
 129  * @param int    $foreignMaxLimit Max limit of displaying foreign elements
 130  * @param array  $fields          Array of search criteria inputs
 131  * @param bool   $in_fbs          Whether we are in 'function based search'
 132  * @param bool   $in_edit         Whether in search mode
 133  *                                    or edit mode (used for zoom search)
 134  *
 135  * @return string HTML content for viewing foreing data and elements
 136  * for search criteria input.
 137  */
 138 function PMA_getForeignFields_Values($foreigners, $foreignData, $field, $tbl_fields_type, $i, $db, $table, $titles, $foreignMaxLimit, $fields, $in_fbs = false, $in_edit = false)
 139 {
 140     $str = '';
 141     if ($foreigners && isset($foreigners[$field]) && is_array($foreignData['disp_row'])) {
 142         // f o r e i g n    k e y s
 143         $str .=  '<select name="fields[' . $i . ']" id="fieldID_' . $i .'">' . "\n";
 144         // go back to first row
 145         // here, the 4th parameter is empty because there is no current
 146         // value of data for the dropdown (the search page initial values
 147         // are displayed empty)
 148         $str .= PMA_foreignDropdown(
 149             $foreignData['disp_row'], $foreignData['foreign_field'],
 150             $foreignData['foreign_display'], '', $foreignMaxLimit
 151         );
 152         $str .= '</select>' . "\n";
 153
 154     } elseif ($foreignData['foreign_link'] == true) {
 155         if(isset($fields[$i]) && is_string($fields[$i])){
 156          $str .= '<input type="text" id="fieldID_' . $i .'"name="fields[' . $i . ']" value="' . $fields[$i] . '"';
 157              'id="field_' . md5($field) . '[' . $i .']"
 158              class="textfield"/>' ;
 159         }
 160         else{
 161          $str .= '<input type="text" id="fieldID_' . $i .'"name="fields[' . $i . ']"';
 162              'id="field_' . md5($field) . '[' . $i .']"
 163              class="textfield" />' ;
 164         }
 165  ?>
 166     <?php $str .= '<script type="text/javascript">';
 167         // <![CDATA[
 168     $str .=  <<<EOT
 169 <a target="_blank" onclick="window.open(this.href, 'foreigners', 'width=640,height=240,scrollbars=yes'); return false" href="browse_foreigners.php?
 170 EOT;
 171         $str .= '' . PMA_generate_common_url($db, $table) .  '&amp;field=' . urlencode($field) . '&amp;fieldkey=' . $i . '">' . str_replace("'", "\'", $titles['Browse']) . '</a>';
 172         // ]]
 173         $str .= '</script>';
 174
 175     } elseif (in_array($tbl_fields_type[$i], PMA_getGISDatatypes())) {
 176         // g e o m e t r y
 177         $str .= '<input type="text" name="fields[' . $i . ']"'
 178         .' size="40" class="textfield" id="field_' . $i . '" />' .  "\n";
 179
 180         if ($in_fbs) {
 181             $edit_url = 'gis_data_editor.php?' . PMA_generate_common_url();
 182             $edit_str = PMA_getIcon('b_edit.png', __('Edit/Insert'));
 183             $str .= '<span class="open_search_gis_editor">';
 184             $str .= PMA_linkOrButton($edit_url, $edit_str, array(), false, false, '_blank');
 185             $str .= '</span>';
 186         }
 187
 188     } elseif (strncasecmp($tbl_fields_type[$i], 'enum', 4) == 0
 189         || (strncasecmp($tbl_fields_type[$i], 'set', 3) == 0 && $in_edit)
 190     ) {
 191         // e n u m s   a n d   s e t s
 192
 193         // Enum in edit mode   --> dropdown
 194         // Enum in search mode --> multiselect
 195         // Set in edit mode    --> multiselect
 196         // Set in search mode  --> input (skipped here, so the 'else'
 197         //                                 section would handle it)
 198
 199         $value = explode(', ', str_replace("'", '', substr($tbl_fields_type[$i], 5, -1)));
 200         $cnt_value = count($value);
 201
 202         if ((strncasecmp($tbl_fields_type[$i], 'enum', 4) && ! $in_edit)
 203             || (strncasecmp($tbl_fields_type[$i], 'set', 3) && $in_edit)
 204         ) {
 205             $str .= '<select name="fields[' . ($i) . '][]" id="fieldID_' . $i .'">' . "\n";
 206         } else {
 207             $str .= '<select name="fields[' . ($i) . '][]" id="fieldID_' . $i .'"'
 208                 . ' multiple="multiple" size="' . min(3, $cnt_value) . '">' . "\n";
 209         }
 210
 211         for ($j = 0; $j < $cnt_value; $j++) {
 212             if (isset($fields[$i])
 213                 && is_array($fields[$i])
 214                 && in_array($value[$j], $fields[$i])
 215             ) {
 216                 $str .= '<option value="' . $value[$j] . '" Selected>'
 217                     . $value[$j] . '</option>';
 218             } else {
 219                 $str .= '<option value="' . $value[$j] . '">'
 220                     . $value[$j] . '</option>';
 221             }
 222         } // end for
 223         $str .= '</select>' . "\n";
 224
 225     } else {
 226         // o t h e r   c a s e s
 227         $the_class = 'textfield';
 228         $type = $tbl_fields_type[$i];
 229
 230         if ($type == 'date') {
 231             $the_class .= ' datefield';
 232         } elseif ($type == 'datetime' || substr($type, 0, 9) == 'timestamp') {
 233             $the_class .= ' datetimefield';
 234         } elseif (substr($type, 0, 3) == 'bit') {
 235             $the_class .= ' bit';
 236         }
 237
 238         if (isset($fields[$i]) && is_string($fields[$i])) {
 239             $str .= '<input type="text" name="fields[' . $i . ']"'
 240                 .' size="40" class="' . $the_class . '" id="fieldID_'
 241                 . $i .'" value = "' . $fields[$i] . '"/>' .  "\n";
 242         } else {
 243             $str .= '<input type="text" name="fields[' . $i . ']"'
 244                 .' size="40" class="' . $the_class . '" id="fieldID_'
 245                 . $i .'" />' .  "\n";
 246         }
 247     }
 248     return $str;
 249 }
 250
 251 /**
 252  * Return the where clause for query generation based on the inputs provided.
 253  *
 254  * @param mixed  $fields     Search criteria input
 255  * @param string $names      Name of the column on which search is submitted
 256  * @param string $types      Type of the field
 257  * @param string $collations Field collation
 258  * @param string $func_type  Search fucntion/operator
 259  * @param bool   $unaryFlag  Whether operator unary or not
 260  * @param bool   $geom_func  Whether geometry functions should be applied
 261  *
 262  * @return string HTML content for viewing foreing data and elements
 263  * for search criteria input.
 264  */
 265 function PMA_tbl_search_getWhereClause($fields, $names, $types, $collations, $func_type, $unaryFlag, $geom_func = null)
 266 {
 267     /**
 268      * @todo move this to a more apropriate place
 269      */
 270     $geom_unary_functions = array(
 271         'IsEmpty' => 1,
 272         'IsSimple' => 1,
 273         'IsRing' => 1,
 274         'IsClosed' => 1,
 275     );
 276
 277     $w = '';
 278     // If geometry function is set apply it to the field name
 279     if ($geom_func != null && trim($geom_func) != '') {
 280         // Get details about the geometry fucntions
 281         $geom_funcs = PMA_getGISFunctions($types, true, false);
 282
 283         // If the function takes a single parameter
 284         if ($geom_funcs[$geom_func]['params'] == 1) {
 285             $backquoted_name = $geom_func . '(' . PMA_backquote($names) . ')';
 286         } else {
 287             // If the function takes two parameters
 288             // create gis data from the string
 289             $gis_data = PMA_createGISData($fields);
 290
 291             $w = $geom_func . '(' . PMA_backquote($names) . ',' . $gis_data . ')';
 292             return $w;
 293         }
 294
 295         // New output type is the output type of the function being applied
 296         $types = $geom_funcs[$geom_func]['type'];
 297
 298         // If the where clause is something like 'IsEmpty(`spatial_col_name`)'
 299         if (isset($geom_unary_functions[$geom_func]) && trim($fields) == '') {
 300             $w = $backquoted_name;
 301             return $w;
 302         }
 303     } else {
 304         $backquoted_name = PMA_backquote($names);
 305     }
 306
 307     if ($unaryFlag) {
 308         $fields = '';
 309         $w = $backquoted_name . ' ' . $func_type;
 310
 311     } elseif (in_array($types, PMA_getGISDatatypes()) && ! empty($fields)) {
 312         // create gis data from the string
 313         $gis_data = PMA_createGISData($fields);
 314         $w = $backquoted_name . ' ' . $func_type . ' ' . $gis_data;
 315
 316     } elseif (strncasecmp($types, 'enum', 4) == 0) {
 317         if (!empty($fields)) {
 318             if (! is_array($fields)) {
 319                 $fields = explode(',', $fields);
 320             }
 321             $enum_selected_count = count($fields);
 322             if ($func_type == '=' && $enum_selected_count > 1) {
 323                 $func_type    = 'IN';
 324                 $parens_open  = '(';
 325                 $parens_close = ')';
 326
 327             } elseif ($func_type == '!=' && $enum_selected_count > 1) {
 328                 $func_type    = 'NOT IN';
 329                 $parens_open  = '(';
 330                 $parens_close = ')';
 331
 332             } else {
 333                 $parens_open  = '';
 334                 $parens_close = '';
 335             }
 336             $enum_where = '\'' . PMA_sqlAddslashes($fields[0]) . '\'';
 337             for ($e = 1; $e < $enum_selected_count; $e++) {
 338                 $enum_where .= ', \'' . PMA_sqlAddslashes($fields[$e]) . '\'';
 339             }
 340
 341             $w = $backquoted_name . ' ' . $func_type . ' ' . $parens_open . $enum_where . $parens_close;
 342         }
 343
 344     } elseif ($fields != '') {
 345         // For these types we quote the value. Even if it's another type (like INT),
 346         // for a LIKE we always quote the value. MySQL converts strings to numbers
 347         // and numbers to strings as necessary during the comparison
 348         if (preg_match('@char|binary|blob|text|set|date|time|year@i', $types)
 349             || strpos(' ' . $func_type, 'LIKE')
 350         ) {
 351             $quot = '\'';
 352         } else {
 353             $quot = '';
 354         }
 355
 356         // LIKE %...%
 357         if ($func_type == 'LIKE %...%') {
 358             $func_type = 'LIKE';
 359             $fields = '%' . $fields . '%';
 360         }
 361         if ($func_type == 'REGEXP ^...$') {
 362             $func_type = 'REGEXP';
 363             $fields = '^' . $fields . '$';
 364         }
 365
 366         if ($func_type == 'IN (...)'
 367             || $func_type == 'NOT IN (...)'
 368             || $func_type == 'BETWEEN'
 369             || $func_type == 'NOT BETWEEN'
 370         ) {
 371             $func_type = str_replace(' (...)', '', $func_type);
 372
 373             // quote values one by one
 374             $values = explode(',', $fields);
 375             foreach ($values as &$value) {
 376                 $value = $quot . PMA_sqlAddslashes(trim($value)) . $quot;
 377             }
 378
 379             if ($func_type == 'BETWEEN' || $func_type == 'NOT BETWEEN') {
 380                 $w = $backquoted_name . ' ' . $func_type . ' ' . (isset($values[0]) ? $values[0] : '')
 381                     . ' AND ' . (isset($values[1]) ? $values[1] : '');
 382             } else {
 383                 $w = $backquoted_name . ' ' . $func_type . ' (' . implode(',', $values) . ')';
 384             }
 385         } else {
 386             $w = $backquoted_name . ' ' . $func_type . ' ' . $quot . PMA_sqlAddslashes($fields) . $quot;;
 387         }
 388     } // end if
 389
 390     return $w;
 391 }
 392
 393 /**
 394  * Formats a SVG plot for the query results.
 395  *
 396  * @param array $data      Data for the status chart
 397  * @param array &$settings Settings used to generate the chart
 398  *
 399  * @return string HTML and JS code for the SVG plot
 400  */
 401 function PMA_SVG_scatter_plot($data, &$settings)
 402 {
 403     include_once './libraries/svg_plot/pma_scatter_plot.php';
 404
 405     if (empty($data)) {
 406         // empty data
 407         return '';
 408     } else {
 409         $scatter_plot = new PMA_Scatter_Plot($data, $settings);
 410
 411         if ($settings != null) {
 412             foreach ($scatter_plot->getSettings() as $setting => $val) {
 413                 if (! isset($settings[$setting])) {
 414                     $settings[$setting] = $val;
 415                 }
 416             }
 417         }
 418         return $scatter_plot->asSVG();
 419     }
 420 }
 421 ?>