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