· 7 years ago · Feb 15, 2019, 08:54 AM
1<?php
2
3 $result = mysqli_query($cxn,"SELECT * FROM counter");
4
5 while($row = mysqli_fetch_array($result)) {
6 $value = $row['value'];
7 $timestamp = strtotime($row['datetime']);
8 $data[] = "[$value, $timestamp]";
9 }
10
11 json_encode($data);
12
13?>
14
15["[500, 1384122794]","[600, 1384153203]"]
16
17<html>
18 <body>
19 <div id="container" style="height: 500px; min-width: 500px"></div>
20
21 <script src="http://ajax.googleapis.com/ajax/libs/jquery/1.8.2/jquery.min.js"></script>
22 <script src="http://code.highcharts.com/stock/highstock.js"></script>
23 <script>
24 $(function() {
25 $.getJSON('http://www.website.com/graph.php', function(data) { // I put website.com on purpose
26 // Create the chart
27 $('#container').highcharts('StockChart', {
28 rangeSelector : {
29 selected : 1
30 },
31
32 title : {
33 text : 'Title'
34 },
35
36 series : [{
37 name : 'AAPL',
38 data : data,
39 tooltip: {
40 valueDecimals: 2
41 }
42 }]
43 });
44 });
45
46 });
47 </script>
48 </body>
49</html>
50
51while($row = mysqli_fetch_array($result)) {
52 $value = $row['value'];
53 $timestamp = strtotime($row['datetime']) * 1000;
54 $data[] = [$timestamp, (int)$value];
55}
56
57$(function () {
58 $('#container').highcharts({
59 title: {
60 text: 'Monthly Average Temperature',
61 x: -20 //center
62 },
63 subtitle: {
64 text: 'Source: WorldClimate.com',
65 x: -20
66 },
67 xAxis: {
68 categories: ['Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun',
69 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec']
70 },
71 yAxis: {
72 title: {
73 text: 'Temperature (°C)'
74 },
75 plotLines: [{
76 value: 0,
77 width: 1,
78 color: '#808080'
79 }]
80 },
81 tooltip: {
82 valueSuffix: '°C'
83 },
84 legend: {
85 layout: 'vertical',
86 align: 'right',
87 verticalAlign: 'middle',
88 borderWidth: 0
89 },
90 series: [{
91 name: 'Tokyo',
92 data: [7.0, 6.9, 9.5, 14.5, 18.2, 21.5, 25.2, 26.5, 23.3, 18.3, 13.9, 9.6]
93 }, {
94 name: 'New York',
95 data: [-0.2, 0.8, 5.7, 11.3, 17.0, 22.0, 24.8, 24.1, 20.1, 14.1, 8.6, 2.5]
96 }, {
97 name: 'Berlin',
98 data: [-0.9, 0.6, 3.5, 8.4, 13.5, 17.0, 18.6, 17.9, 14.3, 9.0, 3.9, 1.0]
99 }, {
100 name: 'London',
101 data: [3.9, 4.2, 5.7, 8.5, 11.9, 15.2, 17.0, 16.6, 14.2, 10.3, 6.6, 4.8]
102 }]
103 });
104 });
105
106Step-1 Create Database [highchartdemo]
107Step-2 Create Two tables [demo_viewer, demo_click]
108
109CREATE TABLE IF NOT EXISTS `demo_viewer` (
110
111 `id` int(11) NOT NULL AUTO_INCREMENT,
112
113 `numberofview` int(11) NOT NULL,
114
115 `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
116
117 PRIMARY KEY (`id`)
118
119) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=10 ;
120
121
122CREATE TABLE IF NOT EXISTS `demo_click` (
123
124 `id` int(11) NOT NULL AUTO_INCREMENT,
125
126 `numberofclick` int(12) NOT NULL,
127
128 `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
129
130 PRIMARY KEY (`id`)
131
132) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=10 ;
133
134
135demo_viewer table:
136[enter image description here][1]
137
138
139demo_click table:
140[enter image description here][2]
141
142
143Step3- Create Database Configuration File
144
145config.php
146<?php
147
148 $dbHost = "localhost";
149
150 $dbDatabase = "highchartdemo";
151
152 $dbPasswrod = "root";
153
154 $dbUser = "root";
155
156
157 $mysqli = mysqli_connect($dbHost, $dbUser, $dbPasswrod, $dbDatabase);
158
159?>
160
161
162Step-4 Create Index.php file
163
164index.php
165
166<?php
167
168
169 require('config.php');
170
171
172 /* Getting demo_viewer table data */
173
174 $sql = "SELECT SUM(numberofview) as count FROM demo_viewer
175
176 GROUP BY YEAR(created_at) ORDER BY created_at";
177
178 $viewer = mysqli_query($mysqli,$sql);
179
180 $viewer = mysqli_fetch_all($viewer,MYSQLI_ASSOC);
181
182 $viewer = json_encode(array_column($viewer, 'count'),JSON_NUMERIC_CHECK);
183
184
185 /* Getting demo_click table data */
186
187 $sql = "SELECT SUM(numberofclick) as count FROM demo_click
188
189 GROUP BY YEAR(created_at) ORDER BY created_at";
190
191 $click = mysqli_query($mysqli,$sql);
192
193 $click = mysqli_fetch_all($click,MYSQLI_ASSOC);
194
195 $click = json_encode(array_column($click, 'count'),JSON_NUMERIC_CHECK);
196
197
198?>
199
200
201<!DOCTYPE html>
202
203<html>
204
205<head>
206
207 <title>Highcharts get data from database</title>
208
209 <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css">
210
211 <script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.1.1/jquery.js"></script>
212
213 <script src="https://code.highcharts.com/highcharts.js"></script>
214
215</head>
216
217<body>
218
219
220<script type="text/javascript">
221
222
223$(function () {
224
225
226 var data_click = <?php echo $click; ?>;
227
228 var data_viewer = <?php echo $viewer; ?>;
229
230
231 $('#container').highcharts({
232
233 chart: {
234
235 type: 'column'
236
237 },
238
239 title: {
240
241 text: 'Yearly Website Ratio'
242
243 },
244
245 xAxis: {
246
247 categories: ['2013','2014','2015', '2016']
248
249 },
250
251 yAxis: {
252
253 title: {
254
255 text: 'Rate'
256
257 }
258
259 },
260
261 series: [{
262
263 name: 'Click',
264
265 data: data_click
266
267 }, {
268
269 name: 'View',
270
271 data: data_viewer
272
273 }]
274
275 });
276
277});
278
279
280</script>
281
282
283<div class="container">
284
285 <br/>
286
287 <h2 class="text-center">Highcharts get data from database</h2>
288
289 <div class="row">
290
291 <div class="col-md-10 col-md-offset-1">
292
293 <div class="panel panel-default">
294
295 <div class="panel-heading">Graphical View</div>
296
297 <div class="panel-body">
298
299 <div id="container"></div>
300
301 </div>
302
303 </div>
304 </div>
305 </div>
306</div>
307</body>
308</html>
309
310
311 [1]: https://i.stack.imgur.com/5wiDd.png
312 [2]: https://i.stack.imgur.com/QAdT7.png