tbl_zoom_select.php

   1 <?php
   2 /* vim: set expandtab sw=4 ts=4 sts=4: */
   3 /**
   4  * Handles table zoom search tab
   5  *
   6  * display table zoom search form, create SQL queries from form data
   7  *
   8  */
   9
  10 /**
  11  * Gets some core libraries
  12  */
  13 require_once './libraries/common.inc.php';
  14 require_once './libraries/mysql_charsets.lib.php';
  15 require_once './libraries/tbl_select.lib.php';
  16 require_once './libraries/relation.lib.php';
  17 require_once './libraries/tbl_info.inc.php';
  18
  19 $GLOBALS['js_include'][] = 'makegrid.js';
  20 $GLOBALS['js_include'][] = 'sql.js';
  21 $GLOBALS['js_include'][] = 'functions.js';
  22 $GLOBALS['js_include'][] = 'tbl_zoom_plot.js';
  23 $GLOBALS['js_include'][] = 'date.js';
  24 $GLOBALS['js_include'][] = 'jquery/jquery.mousewheel.js';
  25 $GLOBALS['js_include'][] = 'highcharts/highcharts.js';
  26 /* Files required for chart exporting */
  27 $GLOBALS['js_include'][] = 'highcharts/exporting.js';
  28 $GLOBALS['js_include'][] = 'canvg/canvg.js';
  29 $GLOBALS['js_include'][] = 'jquery/jquery-ui-1.8.16.custom.js';
  30 $GLOBALS['js_include'][] = 'jquery/timepicker.js';
  31
  32
  33 /**
  34  * Handle AJAX request for data row on point select
  35  * @var post_params Object containing parameters for the POST request
  36  */
  37
  38 if (isset($_REQUEST['get_data_row']) && $_REQUEST['get_data_row'] == true) {
  39     $extra_data = array();
  40     $row_info_query = 'SELECT * FROM `' . $_REQUEST['db'] . '`.`'
  41         . $_REQUEST['table'] . '` WHERE ' .  $_REQUEST['where_clause'];
  42     $result = PMA_DBI_query($row_info_query . ";", null, PMA_DBI_QUERY_STORE);
  43     $fields_meta = PMA_DBI_get_fields_meta($result);
  44     while ($row = PMA_DBI_fetch_assoc($result)) {
  45         // for bit fields we need to convert them to printable form
  46         $i = 0;
  47         foreach ($row as $col => $val) {
  48             if ($fields_meta[$i]->type == 'bit') {
  49                 $row[$col] = PMA_printable_bit_value($val, $fields_meta[$i]->length);
  50             }
  51             $i++;
  52         }
  53         $extra_data['row_info'] = $row;
  54     }
  55     PMA_ajaxResponse(null, true, $extra_data);
  56 }
  57
  58 /**
  59  * Handle AJAX request for changing field information(value,collation,operators,field values)
  60  * in input form
  61  * @var post_params Object containing parameters for the POST request
  62  */
  63
  64 if (isset($_REQUEST['change_tbl_info']) && $_REQUEST['change_tbl_info'] == true) {
  65     $extra_data = array();
  66     $field = $_REQUEST['field'];
  67     if($field == 'pma_null') {
  68         $extra_data['field_type'] = '';
  69         $extra_data['field_collation'] = '';
  70         $extra_data['field_operators'] = '';
  71         PMA_ajaxResponse(NULL, true, $extra_data);
  72     }
  73
  74
  75     // Gets the list and number of fields
  76     list($fields_list, $fields_type, $fields_collation, $fields_null) = PMA_tbl_getFields($_REQUEST['db'],$_REQUEST['table']);
  77
  78     $foreigners = PMA_getForeigners($db, $table);
  79     $titles['Browse'] = PMA_getIcon('b_browse.png', __('Browse foreign values'));
  80     $key = array_search($field,$fields_list);
  81     $extra_data['field_type'] = $fields_type[$key];
  82     $extra_data['field_collation'] = $fields_collation[$key];
  83
  84     // HTML for operators
  85     $html = '<select name="zoomFunc[]">';
  86     if (strncasecmp($fields_type[$key], 'enum', 4) == 0) {
  87         foreach ($GLOBALS['cfg']['EnumOperators'] as $fc) {
  88             $html .= "\n" . '                        '
  89             . '<option value="' . htmlspecialchars($fc) . '">'
  90             . htmlspecialchars($fc) . '</option>';
  91         }
  92     } elseif (preg_match('@char|blob|text|set@i', $fields_type[$key])) {
  93     foreach ($GLOBALS['cfg']['TextOperators'] as $fc) {
  94         $html .= "\n" . '                        '
  95             . '<option value="' . htmlspecialchars($fc) . '">'
  96             . htmlspecialchars($fc) . '</option>';
  97         }
  98     } else {
  99     foreach ($GLOBALS['cfg']['NumOperators'] as $fc) {
 100         $html .= "\n" . '                        '
 101             . '<option value="' .  htmlspecialchars($fc) . '">'
 102             . htmlspecialchars($fc) . '</option>';
 103     }
 104     } // end if... else...
 105
 106     if ($fields_null[$key]) {
 107         foreach ($GLOBALS['cfg']['NullOperators'] as $fc) {
 108         $html .= "\n" . '                        '
 109                 . '<option value="' .  htmlspecialchars($fc) . '">'
 110         . htmlspecialchars($fc) . '</option>';
 111     }
 112     }
 113     $html .= '</select>';
 114     $extra_data['field_operators'] = $html;
 115
 116     // retrieve keys into foreign fields, if any
 117     // check also foreigners even if relwork is FALSE (to get
 118     // foreign keys from innodb)
 119     $foreignData = PMA_getForeignData($foreigners, $field, false, '', '');
 120
 121     // HTML for field values
 122     $html = PMA_getForeignFields_Values($foreigners, $foreignData, $field, array($_REQUEST['it'] => $fields_type[$key]), $_REQUEST['it'] ,$_REQUEST['db'], $_REQUEST['table'], $titles, $GLOBALS['cfg']['ForeignKeyMaxLimit'], '');
 123     $extra_data['field_value'] = $html;
 124     PMA_ajaxResponse(NULL, true, $extra_data);
 125 }
 126
 127 $titles['Browse'] = PMA_getIcon('b_browse.png', __('Browse foreign values'));
 128 /**
 129  * Not selection yet required -> displays the selection form
 130  */
 131
 132 // Gets some core libraries
 133 require_once './libraries/tbl_common.php';
 134 $url_query .= '&amp;goto=tbl_select.php&amp;back=tbl_select.php';
 135
 136 /**
 137  * Gets tables informations
 138  */
 139 require_once './libraries/tbl_info.inc.php';
 140
 141 /**
 142  * Displays top menu links
 143  */
 144 require_once './libraries/tbl_links.inc.php';
 145
 146 if (! isset($goto)) {
 147     $goto = $GLOBALS['cfg']['DefaultTabTable'];
 148 }
 149 // Defines the url to return to in case of error in the next sql statement
 150 $err_url   = $goto . '?' . PMA_generate_common_url($db, $table);
 151
 152 // Gets the list and number of fields
 153
 154 list($fields_list, $fields_type, $fields_collation, $fields_null) = PMA_tbl_getFields($db, $table);
 155 $fields_cnt = count($fields_list);
 156
 157 // retrieve keys into foreign fields, if any
 158 // check also foreigners even if relwork is FALSE (to get
 159 // foreign keys from innodb)
 160 $foreigners = PMA_getForeigners($db, $table);
 161 $flag = 1;
 162 $tbl_fields_type = $tbl_fields_collation = $tbl_fields_null = array();
 163 if (! isset($zoom_submit) && ! isset($inputs)) {
 164     $dataLabel = PMA_getDisplayField($db, $table);
 165 }
 166 ?>
 167 <div id="sqlqueryresults"></div>
 168 <fieldset id="fieldset_subtab">
 169 <?php
 170 $url_params = array();
 171 $url_params['db']    = $db;
 172 $url_params['table'] = $table;
 173 echo PMA_generate_html_tabs(PMA_tbl_getSubTabs(), $url_params, '', 'topmenu2');
 174
 175 /**
 176  *  Set the field name,type,collation and whether null on select of a coulmn
 177  */
 178 if (isset($inputs) && ($inputs[0] != 'pma_null' || $inputs[1] != 'pma_null')) {
 179     $flag = 2;
 180     for ($i = 0 ; $i < 4 ; $i++) {
 181         if ($inputs[$i] != 'pma_null') {
 182             $key = array_search($inputs[$i], $fields_list);
 183             $tbl_fields_type[$i] = $fields_type[$key];
 184             $tbl_fields_collation[$i] = $fields_collation[$key];
 185             $tbl_fields_null[$i] = $fields_null[$key];
 186         }
 187     }
 188 }
 189
 190 /*
 191  * Form for input criteria
 192  */
 193
 194 ?>
 195 <form method="post" action="tbl_zoom_select.php" name="insertForm" id="zoom_search_form"
 196     <?php echo ($GLOBALS['cfg']['AjaxEnable'] ? ' class="ajax"' : ''); ?>>
 197 <?php echo PMA_generate_common_hidden_inputs($db, $table); ?>
 198 <input type="hidden" name="goto" value="<?php echo $goto; ?>" />
 199 <input type="hidden" name="back" value="tbl_zoom_select.php" />
 200 <input type="hidden" name="flag" id="id_flag" value="<?php echo $flag; ?>" />
 201
 202 <fieldset id="inputSection">
 203
 204 <legend><?php echo __('Do a "query by example" (wildcard: "%") for two different columns') ?></legend>
 205 <table class="data" id="tableFieldsId">
 206 <?php echo PMA_tbl_setTableHeader();?>
 207 <tbody>
 208 <?php
 209     $odd_row = true;
 210
 211 for ($i = 0; $i < 4; $i++) {
 212     if ($i == 2) {
 213         echo "<tr><td>";
 214         echo __("Additional search criteria");
 215         echo "</td></tr>";
 216     }
 217     ?>
 218     <tr class="noclick <?php echo $odd_row ? 'odd' : 'even'; $odd_row = ! $odd_row; ?>">
 219         <th><select name="inputs[]" id="<?php echo 'tableid_' . $i; ?>" >
 220         <option value="<?php echo 'pma_null'; ?>"><?php echo __('None');  ?></option>
 221     <?php
 222     for ($j = 0 ; $j < $fields_cnt ; $j++) {
 223         if (isset($inputs[$i]) && $inputs[$i] == htmlspecialchars($fields_list[$j])) {?>
 224             <option value="<?php echo htmlspecialchars($fields_list[$j]);?>" selected="selected">
 225                 <?php echo htmlspecialchars($fields_list[$j]);?></option>
 226         <?php
 227         } else { ?>
 228             <option value="<?php echo htmlspecialchars($fields_list[$j]);?>">
 229                 <?php echo htmlspecialchars($fields_list[$j]);?></option>
 230         <?php
 231         }
 232     } ?>
 233         </select></th>
 234         <td></td>
 235         <td></td>
 236         <td></td>
 237         <td></td>
 238     </tr>
 239     <tr><td>
 240       <input type="hidden" name="types[<?php echo $i; ?>]" id="types_<?php echo $i; ?>" />
 241       <input type="hidden" name="collations[<?php echo $i;?>]" id="collations_<?php echo $i; ?>" />
 242     </td></tr>
 243     <?php
 244 }//end for
 245 ?>
 246     </tbody>
 247     </table>
 248
 249 <?php
 250 /*
 251  * Other inputs like data label and mode go after selection of column criteria
 252  */
 253
 254 //Set default datalabel if not selected
 255 if (isset($zoom_submit) && $inputs[0] != 'pma_null' && $inputs[1] != 'pma_null') {
 256     if ($dataLabel == '') {
 257         $dataLabel = PMA_getDisplayField($db, $table);
 258     }
 259 }
 260 ?>
 261     <table class="data">
 262     <tr><td><label for="dataLabel"><?php echo __("Use this column to label each point"); ?></label></td>
 263     <td><select name="dataLabel" id='dataLabel' >
 264         <option value = ''> <?php echo __('None');  ?> </option>
 265 <?php
 266 for ($j = 0; $j < $fields_cnt; $j++) {
 267     if (isset($dataLabel) && $dataLabel == htmlspecialchars($fields_list[$j])) {
 268         ?>
 269         <option value="<?php echo htmlspecialchars($fields_list[$j]);?>" selected="selected">
 270             <?php echo htmlspecialchars($fields_list[$j]);?></option>
 271         <?php
 272     } else {
 273         ?>
 274         <option value="<?php echo htmlspecialchars($fields_list[$j]);?>" >
 275             <?php echo htmlspecialchars($fields_list[$j]);?></option>
 276         <?php
 277     }
 278 }
 279 ?>
 280     </select>
 281     </td></tr>
 282     <tr><td><label for="maxRowPlotLimit"><?php echo __("Maximum rows to plot"); ?></label></td>
 283     <td>
 284 <?php
 285 echo '<input type="text" name="maxPlotLimit" id="maxRowPlotLimit" value="';
 286 if (! empty($maxPlotLimit)) {
 287     echo htmlspecialchars($maxPlotLimit);
 288 } else {
 289     echo $GLOBALS['cfg']['maxRowPlotLimit'];
 290 }
 291 echo '" /></td></tr>';
 292 ?>
 293     </table>
 294
 295 </fieldset>
 296 <fieldset class="tblFooters">
 297     <input type="hidden" name="max_number_of_fields"
 298         value="<?php echo $fields_cnt; ?>" />
 299     <input type="submit" name="zoom_submit" id="inputFormSubmitId" value="<?php echo __('Go'); ?>" />
 300 </fieldset>
 301 </form>
 302 </fieldset>
 303
 304 <?php
 305
 306 /*
 307  * Handle the input criteria and generate the query result
 308  * Form for displaying query results
 309  */
 310 if (isset($zoom_submit) && $inputs[0] != 'pma_null' && $inputs[1] != 'pma_null' && $inputs[0] != $inputs[1]) {
 311
 312     /*
 313      * Query generation part
 314      */
 315     $w = $data = array();
 316     $sql_query = 'SELECT *';
 317
 318     //Add the table
 319     $sql_query .= ' FROM ' . PMA_backquote($table);
 320     for ($i = 0; $i < 4; $i++) {
 321         if ($inputs[$i] == 'pma_null') {
 322             continue;
 323         }
 324         $tmp = array();
 325         // The where clause
 326         $charsets = array();
 327         $cnt_func = count($zoomFunc[$i]);
 328         $func_type = $zoomFunc[$i];
 329         list($charsets[$i]) = explode('_', $collations[$i]);
 330         $unaryFlag = (isset($GLOBALS['cfg']['UnaryOperators'][$func_type])
 331                       && $GLOBALS['cfg']['UnaryOperators'][$func_type] == 1)
 332                       ? true
 333                       : false;
 334         $whereClause = PMA_tbl_search_getWhereClause(
 335             $fields[$i], $inputs[$i], $types[$i],
 336             $collations[$i], $func_type, $unaryFlag
 337         );
 338         if ($whereClause) {
 339             $w[] = $whereClause;
 340         }
 341     } // end for
 342     if ($w) {
 343         $sql_query .= ' WHERE ' . implode(' AND ', $w);
 344     }
 345     $sql_query .= ' LIMIT ' . $maxPlotLimit;
 346
 347     /*
 348      * Query execution part
 349      */
 350     $result = PMA_DBI_query($sql_query . ";", null, PMA_DBI_QUERY_STORE);
 351     $fields_meta = PMA_DBI_get_fields_meta($result);
 352     while ($row = PMA_DBI_fetch_assoc($result)) {
 353         //Need a row with indexes as 0,1,2 for the PMA_getUniqueCondition hence using a temporary array
 354         $tmpRow = array();
 355         foreach ($row as $val) {
 356             $tmpRow[] = $val;
 357         }
 358         //Get unique conditon on each row (will be needed for row update)
 359         $uniqueCondition = PMA_getUniqueCondition($result, $fields_cnt, $fields_meta, $tmpRow, true);
 360
 361         //Append it to row array as where_clause
 362         $row['where_clause'] = $uniqueCondition[0];
 363         if ($dataLabel == $inputs[0] || $dataLabel == $inputs[1]) {
 364             $data[] = array(
 365                 $inputs[0]     => $row[$inputs[0]],
 366                 $inputs[1]     => $row[$inputs[1]],
 367                 'where_clause' => $uniqueCondition[0]
 368             );
 369         } elseif ($dataLabel) {
 370             $data[] = array(
 371                 $inputs[0]     => $row[$inputs[0]],
 372                 $inputs[1]     => $row[$inputs[1]],
 373                 $dataLabel     => $row[$dataLabel],
 374                 'where_clause' => $uniqueCondition[0]
 375             );
 376         } else {
 377             $data[] = array(
 378                 $inputs[0]     => $row[$inputs[0]],
 379                 $inputs[1]     => $row[$inputs[1]],
 380                 $dataLabel     => '',
 381                 'where_clause' => $uniqueCondition[0]
 382             );
 383         }
 384     }
 385     /*
 386      * Form for displaying point data and also the scatter plot
 387      */
 388     ?>
 389     <form method="post" action="tbl_zoom_select.php" name="displayResultForm" id="zoom_display_form"
 390         <?php echo ($GLOBALS['cfg']['AjaxEnable'] ? ' class="ajax"' : ''); ?>>
 391     <?php echo PMA_generate_common_hidden_inputs($db, $table); ?>
 392     <input type="hidden" name="goto" value="<?php echo $goto; ?>" />
 393     <input type="hidden" name="back" value="tbl_zoom_select.php" />
 394
 395     <fieldset id="displaySection">
 396       <legend><?php echo __('Browse/Edit the points') ?></legend>
 397       <center>
 398     <?php
 399     //JSON encode the data(query result)
 400     if (isset($zoom_submit) && ! empty($data)) {
 401         ?>
 402         <div id='resizer' style="width:600px;height:400px">
 403           <center><a href="#" onclick="displayHelp();"><?php echo __('How to use'); ?></a></center>
 404           <div id="querydata" style="display:none">
 405         <?php
 406         echo json_encode($data);
 407         ?>
 408           </div>
 409           <div id="querychart" style="float:right"></div>
 410         </div>
 411         <?php
 412     }
 413     ?>
 414       </center>
 415       <div id='dataDisplay' style="display:none">
 416         <table>
 417           <thead>
 418             <tr>
 419               <th> <?php echo __('Column'); ?> </th>
 420               <th> <?php echo __('Null'); ?> </th>
 421               <th> <?php echo __('Value'); ?> </th>
 422             </tr>
 423           </thead>
 424           <tbody>
 425     <?php
 426     $odd_row = true;
 427     for ($i = 4; $i < $fields_cnt + 4; $i++) {
 428         $tbl_fields_type[$i] = $fields_type[$i - 4];
 429         $fieldpopup = $fields_list[$i - 4];
 430         $foreignData = PMA_getForeignData($foreigners, $fieldpopup, false, '', '');
 431         ?>
 432             <tr class="noclick <?php echo $odd_row ? 'odd' : 'even'; $odd_row = ! $odd_row; ?>">
 433               <th><?php echo htmlspecialchars($fields_list[$i - 4]); ?></th>
 434               <th><?php echo ($fields_null[$i - 4] == 'YES')
 435                   ? '<input type="checkbox" class="checkbox_null" name="fields_null[ '
 436                       . $i . ' ]" id="fields_null_id_' . $i . '" />'
 437                   : ''; ?>
 438               </th>
 439               <th> <?php
 440               echo PMA_getForeignFields_Values(
 441                   $foreigners, $foreignData, $fieldpopup, $tbl_fields_type,
 442                   $i, $db, $table, $titles,
 443                   $GLOBALS['cfg']['ForeignKeyMaxLimit'], '', false, true
 444               ); ?>
 445               </th>
 446             </tr>
 447         <?php
 448     }
 449     ?>
 450           </tbody>
 451         </table>
 452     </div>
 453     <input type="hidden" id="queryID" name="sql_query" />
 454     </form>
 455     <?php
 456 }
 457 require './libraries/footer.inc.php';
 458 ?>