2 /* vim: set expandtab sw=4 ts=4 sts=4: */
4 * Handles table zoom search tab
6 * display table zoom search form, create SQL queries from form data
11 * Gets some core libraries
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';
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';
34 * Handle AJAX request for data row on point select
35 * @var post_params Object containing parameters for the POST request
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
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
);
53 $extra_data['row_info'] = $row;
55 PMA_ajaxResponse(null, true, $extra_data);
59 * Handle AJAX request for changing field information(value,collation,operators,field values)
61 * @var post_params Object containing parameters for the POST request
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);
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']);
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];
85 $html = '<select name="zoomFunc[]">';
86 if (strncasecmp($fields_type[$key], 'enum', 4) == 0) {
87 foreach ($GLOBALS['cfg']['EnumOperators'] as $fc) {
89 . '<option value="' . htmlspecialchars($fc) . '">'
90 . htmlspecialchars($fc) . '</option>';
92 } elseif (preg_match('@char|blob|text|set@i', $fields_type[$key])) {
93 foreach ($GLOBALS['cfg']['TextOperators'] as $fc) {
95 . '<option value="' . htmlspecialchars($fc) . '">'
96 . htmlspecialchars($fc) . '</option>';
99 foreach ($GLOBALS['cfg']['NumOperators'] as $fc) {
101 . '<option value="' . htmlspecialchars($fc) . '">'
102 . htmlspecialchars($fc) . '</option>';
104 } // end if... else...
106 if ($fields_null[$key]) {
107 foreach ($GLOBALS['cfg']['NullOperators'] as $fc) {
109 . '<option value="' . htmlspecialchars($fc) . '">'
110 . htmlspecialchars($fc) . '</option>';
113 $html .= '</select>';
114 $extra_data['field_operators'] = $html;
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, '', '');
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);
127 $titles['Browse'] = PMA_getIcon('b_browse.png', __('Browse foreign values'));
129 * Not selection yet required -> displays the selection form
132 // Gets some core libraries
133 require_once './libraries/tbl_common.php';
134 $url_query .= '&goto=tbl_select.php&back=tbl_select.php';
137 * Gets tables informations
139 require_once './libraries/tbl_info.inc.php';
142 * Displays top menu links
144 require_once './libraries/tbl_links.inc.php';
146 if (! isset($goto)) {
147 $goto = $GLOBALS['cfg']['DefaultTabTable'];
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);
152 // Gets the list and number of fields
154 list($fields_list, $fields_type, $fields_collation, $fields_null) = PMA_tbl_getFields($db, $table);
155 $fields_cnt = count($fields_list);
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);
162 $tbl_fields_type = $tbl_fields_collation = $tbl_fields_null = array();
163 if (! isset($zoom_submit) && ! isset($inputs)) {
164 $dataLabel = PMA_getDisplayField($db, $table);
167 <div id
="sqlqueryresults"></div
>
168 <fieldset id
="fieldset_subtab">
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');
176 * Set the field name,type,collation and whether null on select of a coulmn
178 if (isset($inputs) && ($inputs[0] != 'pma_null' ||
$inputs[1] != 'pma_null')) {
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];
191 * Form for input criteria
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; ?>" />
202 <fieldset id
="inputSection">
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();?
>
211 for ($i = 0; $i < 4; $i++
) {
214 echo __("Additional search criteria");
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
>
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
>
228 <option value
="<?php echo htmlspecialchars($fields_list[$j]);?>">
229 <?php
echo htmlspecialchars($fields_list[$j]);?
></option
>
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; ?>" />
251 * Other inputs like data label and mode go after selection of column criteria
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);
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
>
266 for ($j = 0; $j < $fields_cnt; $j++
) {
267 if (isset($dataLabel) && $dataLabel == htmlspecialchars($fields_list[$j])) {
269 <option value
="<?php echo htmlspecialchars($fields_list[$j]);?>" selected
="selected">
270 <?php
echo htmlspecialchars($fields_list[$j]);?
></option
>
274 <option value
="<?php echo htmlspecialchars($fields_list[$j]);?>" >
275 <?php
echo htmlspecialchars($fields_list[$j]);?
></option
>
282 <tr
><td
><label
for="maxRowPlotLimit"><?php
echo __("Maximum rows to plot"); ?
></label
></td
>
285 echo '<input type="text" name="maxPlotLimit" id="maxRowPlotLimit" value="';
286 if (! empty($maxPlotLimit)) {
287 echo htmlspecialchars($maxPlotLimit);
289 echo $GLOBALS['cfg']['maxRowPlotLimit'];
291 echo '" /></td></tr>';
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'); ?>" />
307 * Handle the input criteria and generate the query result
308 * Form for displaying query results
310 if (isset($zoom_submit) && $inputs[0] != 'pma_null' && $inputs[1] != 'pma_null' && $inputs[0] != $inputs[1]) {
313 * Query generation part
315 $w = $data = array();
316 $sql_query = 'SELECT *';
319 $sql_query .= ' FROM ' . PMA_backquote($table);
320 for ($i = 0; $i < 4; $i++
) {
321 if ($inputs[$i] == 'pma_null') {
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)
334 $whereClause = PMA_tbl_search_getWhereClause(
335 $fields[$i], $inputs[$i], $types[$i],
336 $collations[$i], $func_type, $unaryFlag
343 $sql_query .= ' WHERE ' . implode(' AND ', $w);
345 $sql_query .= ' LIMIT ' . $maxPlotLimit;
348 * Query execution part
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
355 foreach ($row as $val) {
358 //Get unique conditon on each row (will be needed for row update)
359 $uniqueCondition = PMA_getUniqueCondition($result, $fields_cnt, $fields_meta, $tmpRow, true);
361 //Append it to row array as where_clause
362 $row['where_clause'] = $uniqueCondition[0];
363 if ($dataLabel == $inputs[0] ||
$dataLabel == $inputs[1]) {
365 $inputs[0] => $row[$inputs[0]],
366 $inputs[1] => $row[$inputs[1]],
367 'where_clause' => $uniqueCondition[0]
369 } elseif ($dataLabel) {
371 $inputs[0] => $row[$inputs[0]],
372 $inputs[1] => $row[$inputs[1]],
373 $dataLabel => $row[$dataLabel],
374 'where_clause' => $uniqueCondition[0]
378 $inputs[0] => $row[$inputs[0]],
379 $inputs[1] => $row[$inputs[1]],
381 'where_clause' => $uniqueCondition[0]
386 * Form for displaying point data and also the scatter plot
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" />
395 <fieldset id
="displaySection">
396 <legend
><?php
echo __('Browse/Edit the points') ?
></legend
>
399 //JSON encode the data(query result)
400 if (isset($zoom_submit) && ! empty($data)) {
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">
406 echo json_encode($data);
409 <div id
="querychart" style
="float:right"></div
>
415 <div id
='dataDisplay' style
="display:none">
419 <th
> <?php
echo __('Column'); ?
> </th
>
420 <th
> <?php
echo __('Null'); ?
> </th
>
421 <th
> <?php
echo __('Value'); ?
> </th
>
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, '', '');
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 . '" />'
440 echo PMA_getForeignFields_Values(
441 $foreigners, $foreignData, $fieldpopup, $tbl_fields_type,
442 $i, $db, $table, $titles,
443 $GLOBALS['cfg']['ForeignKeyMaxLimit'], '', false, true
453 <input type
="hidden" id
="queryID" name
="sql_query" />
457 require './libraries/footer.inc.php';