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