phpmyadmin/tbl_chart.php

   1 <?php
   2 /* vim: set expandtab sw=4 ts=4 sts=4: */
   3 /**
   4  * handles creation of the chart
   5  *
   6  * @package PhpMyAdmin
   7  */
   8
   9 require_once 'libraries/common.inc.php';
  10
  11 /*
  12  * Execute the query and return the result
  13  */
  14 if (isset($_REQUEST['ajax_request'])
  15     && isset($_REQUEST['pos'])
  16     && isset($_REQUEST['session_max_rows'])
  17 ) {
  18     $response = PMA_Response::getInstance();
  19
  20     if (strlen($GLOBALS['table']) && strlen($GLOBALS['db'])) {
  21         include './libraries/tbl_common.inc.php';
  22     } else {
  23         $response->isSuccess(false);
  24         $response->addJSON('message', __('Error'));
  25         exit;
  26     }
  27
  28     $sql_with_limit = 'SELECT * FROM( ' . $sql_query . ' ) AS `temp_res` LIMIT '
  29         . $_REQUEST['pos'] . ', ' . $_REQUEST['session_max_rows'];
  30     $data = array();
  31     $result = PMA_DBI_try_query($sql_with_limit);
  32     while ($row = PMA_DBI_fetch_assoc($result)) {
  33         $data[] = $row;
  34     }
  35
  36     if (empty($data)) {
  37         $response->isSuccess(false);
  38         $response->addJSON('message', __('No data to display'));
  39         exit;
  40     }
  41     $sanitized_data = array();
  42
  43     foreach ($data as $data_row_number => $data_row) {
  44         $tmp_row = array();
  45         foreach ($data_row as $data_column => $data_value) {
  46             $tmp_row[htmlspecialchars($data_column)] = htmlspecialchars($data_value);
  47         }
  48         $sanitized_data[] = $tmp_row;
  49     }
  50     $response->isSuccess(true);
  51     $response->addJSON('message', null);
  52     $response->addJSON('chartData', json_encode($sanitized_data));
  53     unset($sanitized_data);
  54     exit;
  55 }
  56
  57 $response = PMA_Response::getInstance();
  58 // Throw error if no sql query is set
  59 if (! isset($sql_query) || $sql_query == '') {
  60     $response->isSuccess(false);
  61     $response->addHTML(
  62         PMA_Message::error(__('No SQL query was set to fetch data.'))
  63     );
  64     exit;
  65 }
  66 $header   = $response->getHeader();
  67 $scripts  = $header->getScripts();
  68 $scripts->addFile('chart.js');
  69 $scripts->addFile('tbl_chart.js');
  70 $scripts->addFile('jqplot/jquery.jqplot.js');
  71 $scripts->addFile('jqplot/plugins/jqplot.barRenderer.js');
  72 $scripts->addFile('jqplot/plugins/jqplot.canvasAxisLabelRenderer.js');
  73 $scripts->addFile('jqplot/plugins/jqplot.canvasTextRenderer.js');
  74 $scripts->addFile('jqplot/plugins/jqplot.categoryAxisRenderer.js');
  75 $scripts->addFile('jqplot/plugins/jqplot.dateAxisRenderer.js');
  76 $scripts->addFile('jqplot/plugins/jqplot.pointLabels.js');
  77 $scripts->addFile('jqplot/plugins/jqplot.pieRenderer.js');
  78 $scripts->addFile('jqplot/plugins/jqplot.highlighter.js');
  79 /* < IE 9 doesn't support canvas natively */
  80 if (PMA_USR_BROWSER_AGENT == 'IE' && PMA_USR_BROWSER_VER < 9) {
  81     $scripts->addFile('canvg/flashcanvas.js');
  82 }
  83
  84 /**
  85  * Runs common work
  86  */
  87 if (strlen($GLOBALS['table'])) {
  88     $url_params['goto'] = $cfg['DefaultTabTable'];
  89     $url_params['back'] = 'tbl_sql.php';
  90     include 'libraries/tbl_common.inc.php';
  91     include 'libraries/tbl_info.inc.php';
  92 } elseif (strlen($GLOBALS['db'])) {
  93     $url_params['goto'] = $cfg['DefaultTabDatabase'];
  94     $url_params['back'] = 'sql.php';
  95     include 'libraries/db_common.inc.php';
  96     include 'libraries/db_info.inc.php';
  97 } else {
  98     $url_params['goto'] = $cfg['DefaultTabServer'];
  99     $url_params['back'] = 'sql.php';
 100     include 'libraries/server_common.inc.php';
 101 }
 102
 103 $data = array();
 104
 105 $result = PMA_DBI_try_query($sql_query);
 106 $fields_meta = PMA_DBI_get_fields_meta($result);
 107 while ($row = PMA_DBI_fetch_assoc($result)) {
 108     $data[] = $row;
 109 }
 110
 111 $keys = array_keys($data[0]);
 112
 113 $numeric_types = array('int', 'real');
 114 $numeric_column_count = 0;
 115 foreach ($keys as $idx => $key) {
 116     if (in_array($fields_meta[$idx]->type, $numeric_types)) {
 117         $numeric_column_count++;
 118     }
 119 }
 120 if ($numeric_column_count == 0) {
 121     $response->isSuccess(false);
 122     $response->addJSON(
 123         'message',
 124         __('No numeric columns present in the table to plot.')
 125     );
 126     exit;
 127 }
 128
 129 // get settings if any posted
 130 $chartSettings = array();
 131 if (PMA_isValid($_REQUEST['chartSettings'], 'array')) {
 132     $chartSettings = $_REQUEST['chartSettings'];
 133 }
 134
 135 $url_params['db'] = $GLOBALS['db'];
 136 $url_params['reload'] = 1;
 137
 138 /**
 139  * Displays the page
 140  */
 141 // pma_token/url_query needed for chart export
 142 $htmlString = '<script type="text/javascript">'
 143     . "pma_token = '" . $_SESSION[' PMA_token '] . "';"
 144     . "url_query = '" . $url_query . "';"
 145     . '</script>'
 146     . '<!-- Display Chart options -->'
 147     . '<div id="div_view_options">'
 148     . '<form method="post" id="tblchartform" action="tbl_chart.php" class="ajax">'
 149     . PMA_generate_common_hidden_inputs($url_params)
 150     . '<fieldset>'
 151     . '<legend>' . __('Display chart') . '</legend>'
 152     . '<div style="float:left; width:420px;">'
 153     . '<input type="radio" name="chartType" value="bar" id="radio_bar" />'
 154     . '<label for ="radio_bar">' . _pgettext('Chart type', 'Bar') . '</label>'
 155     . '<input type="radio" name="chartType" value="column" id="radio_column" />'
 156     . '<label for ="radio_column">' . _pgettext('Chart type', 'Column') . '</label>'
 157     . '<input type="radio" name="chartType" value="line" id="radio_line"'
 158     . ' checked="checked" />'
 159     . '<label for ="radio_line">' . _pgettext('Chart type', 'Line') . '</label>'
 160     . '<input type="radio" name="chartType" value="spline" id="radio_spline" />'
 161     . '<label for ="radio_spline">' . _pgettext('Chart type', 'Spline') . '</label>'
 162     . '<input type="radio" name="chartType" value="area" id="radio_area" />'
 163     . '<label for ="radio_area">' . _pgettext('Chart type', 'Area') . '</label>'
 164     . '<span class="span_pie" style="display:none;">'
 165     . '<input type="radio" name="chartType" value="pie" id="radio_pie" />'
 166     . '<label for ="radio_pie">' . _pgettext('Chart type', 'Pie') . '</label>'
 167     . '</span>'
 168     . '<span class="span_timeline" style="display:none;">'
 169     . '<input type="radio" name="chartType" value="timeline" id="radio_timeline" />'
 170     . '<label for ="radio_timeline">' . _pgettext('Chart type', 'Timeline')
 171     . '</label>'
 172     . '</span>'
 173     . '<br /><br />'
 174     . '<span class="barStacked">'
 175     . '<input type="checkbox" name="barStacked" value="1"'
 176     . ' id="checkbox_barStacked" />'
 177     . '<label for ="checkbox_barStacked">' . __('Stacked') . '</label>'
 178     . '</span>'
 179     . '<br /><br />'
 180     . '<input type="text" name="chartTitle" value="' . __('Chart title') . '">'
 181     . '</div>';
 182
 183 $htmlString .= '<div style="float:left; padding-left:40px;">'
 184     . '<label for="select_chartXAxis">' .  __('X-Axis:') . '</label>'
 185     . '<select name="chartXAxis" id="select_chartXAxis">';
 186
 187 $yaxis = null;
 188 foreach ($keys as $idx => $key) {
 189     if ($yaxis === null) {
 190         $htmlString .= '<option value="' . htmlspecialchars($idx)
 191             . '" selected="selected">' . htmlspecialchars($key) . '</option>';
 192         $yaxis = $idx;
 193     } else {
 194         $htmlString .= '<option value="' . htmlspecialchars($idx) . '">'
 195             . htmlspecialchars($key) . '</option>';
 196     }
 197 }
 198
 199 $htmlString .= '</select><br />'
 200     . '<label for="select_chartSeries">' . __('Series:') . '</label>'
 201     . '<select name="chartSeries" id="select_chartSeries" multiple="multiple">';
 202
 203 foreach ($keys as $idx => $key) {
 204     if (in_array($fields_meta[$idx]->type, $numeric_types)) {
 205         if ($idx == $yaxis && $numeric_column_count > 1) {
 206             $htmlString .= '<option value="' . htmlspecialchars($idx) . '">'
 207                 . htmlspecialchars($key) . '</option>';
 208         } else {
 209             $htmlString .= '<option value="' . htmlspecialchars($idx)
 210                 . '" selected="selected">' . htmlspecialchars($key)
 211                 . '</option>';
 212         }
 213     }
 214 }
 215
 216 $htmlString .= '</select>'
 217     . '<input type="hidden" name="dateTimeCols" value="';
 218
 219 $date_time_types = array('date', 'datetime', 'timestamp');
 220 foreach ($keys as $idx => $key) {
 221     if (in_array($fields_meta[$idx]->type, $date_time_types)) {
 222         $htmlString .= $idx . " ";
 223     }
 224 }
 225 $htmlString .= '" />'
 226     . '</div>';
 227
 228 $htmlString .= '<div style="float:left; padding-left:40px;">'
 229     . '<label for="xaxis_label">' . __('X-Axis label:') . '</label>'
 230     . '<input style="margin-top:0;" type="text" name="xaxis_label" id="xaxis_label"'
 231     . ' value="'
 232     . (($yaxis == -1) ? __('X Values') : htmlspecialchars($keys[$yaxis]))
 233     . '" /><br />'
 234     . '<label for="yaxis_label">' . __('Y-Axis label:') . '</label>'
 235     . '<input type="text" name="yaxis_label" id="yaxis_label" value="'
 236     . __('Y Values') . '" /><br />'
 237     . '</div>'
 238     . '<p style="clear:both;">&nbsp;</p>'
 239     . '<fieldset>'
 240     . '<div>'
 241     . '<label for="pos">' . __('Start row') . ': ' . "\n" . '</label>'
 242     . '<input type="text" name="pos" size="3" value="'
 243     . $_SESSION['tmp_user_values']['pos'] . '" />'
 244     . '<label for="session_max_rows">'
 245     . __('Number of rows') . ': ' . "\n" . '</label>'
 246     . '<input type="text" name="session_max_rows" size="3" value="'
 247     . (($_SESSION['tmp_user_values']['max_rows'] != 'all')
 248         ? $_SESSION['tmp_user_values']['max_rows']
 249         : $GLOBALS['cfg']['MaxRows'])
 250     . '" />'
 251     . '<input type="submit" name="submit" class="Go" value="' . __('Go') . '" />'
 252     . '<input type="hidden" name="sql_query" value="'
 253     . htmlspecialchars($sql_query) . '" />'
 254     . '</div>'
 255     . '</fieldset>'
 256     . '<p style="clear:both;">&nbsp;</p>'
 257     . '<div id="resizer" style="width:600px; height:400px;">'
 258     . '<div id="querychart">'
 259     . '</div>'
 260     . '</div>'
 261     . '</fieldset>'
 262     . '</form>'
 263     . '</div>';
 264
 265 $response->addHTML($htmlString);
 266 ?>