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