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