teratail
回答率
85.47%
質問するログイン新規登録
トップに関する質問phpとmysqlで1週間分のデータの集計方法をお教えください。

編集履歴

質問編集履歴

6

コード

2019/07/04 03:27

投稿

mizuoniku
mizuoniku

スコア10

test CHANGED
File without changes
test CHANGED
@@ -4,7 +4,7 @@
4
4
 
5
5
 
6
6
 
7
- 1週間分のデータを取るところで間違っているのか、エラーというか動作しません。
7
+ 1週間分のデータを取るところで間違っていると思われます。
8
8
 
9
9
  どこに問題があるかご教授頂けないでしょうか。
10
10
 
@@ -16,87 +16,223 @@
16
16
 
17
17
 
18
18
 
19
- 試したこと（mysqlのSELECT文）
19
+
20
+
21
+ 試したこと追記
22
+
23
+ WHERE createdate >= DATE(DATE_ADD('2019-07-10', INTERVAL -7 DAY))
24
+
25
+ where以降の日付の指定が無ければ集計が取れます。where以降を付けるとエラーになります。
26
+
27
+ #1054 - Unknown column 'createdate' in 'where clause'
28
+
29
+ mysqliに書き直すとはこれで良いのでしょうか？
30
+
31
+ ```
32
+
33
+ php
34
+
35
+
36
+
37
+ $result = $mysqli->query("SELECT SUM(calorie), SUM(protein), SUM(lipid),SUM(carbohydrate), SUM(calcium), SUM(iron),SUM(zinc), SUM(magnesium), SUM(folic_acid),SUM(vitamin_a), SUM(vitamin_d), SUM(vitamin_e),SUM(vitamin_k), SUM(vitamin_b1), SUM(vitamin_b2),SUM(vitamin_c), SUM(dietary_fiber) FROM gohanmemor2 WHERE createdate >= DATE(DATE_ADD($day, INTERVAL -7 DAY))";
38
+
39
+
40
+
41
+ $row = $result->fetch_assoc($sum,$conn);
42
+
43
+
44
+
45
+ $calorie = $row['SUM(calorie)'];
46
+
47
+
48
+
49
+ $protein = $row['SUM(protein)'];
50
+
51
+
52
+
53
+ $lipid = $row['SUM(lipid)'];
54
+
55
+
56
+
57
+ $carbohydrate = $row['SUM(carbohydrate)'];
58
+
59
+
60
+
61
+ $calcium = $row['SUM(calcium)'];
62
+
63
+
64
+
65
+ $iron = $row['SUM(iron)'];
66
+
67
+
68
+
69
+ $zinc = $row['SUM(zinc)'];
70
+
71
+
72
+
73
+ $magnesium = $row['SUM(magnesium)'];
74
+
75
+
76
+
77
+ $folic_acid = $row['SUM(folic_acid)'];
78
+
79
+
80
+
81
+ $vitamin_a = $row['SUM(vitamin_a)'];
82
+
83
+
84
+
85
+ $vitamin_d = $row['SUM(vitamin_d)'];
86
+
87
+
88
+
89
+ $vitamin_e = $row['SUM(vitamin_e)'];
90
+
91
+
92
+
93
+ $vitamin_k = $row['SUM(vitamin_k)'];
94
+
95
+
96
+
97
+ $vitamin_b1 = $row['SUM(vitamin_b1)'];
98
+
99
+
100
+
101
+ $vitamin_b2 = $row['SUM(vitamin_b2)'];
102
+
103
+
104
+
105
+ $vitamin_c = $row['SUM(vitamin_c)'];
106
+
107
+
108
+
109
+ $dietary_fiber = $row['SUM(dietary_fiber)'];
110
+
111
+
112
+
113
+
114
+
115
+
116
+
117
+ $sto=$db->prepare($sql);
118
+
119
+ $sto->execute();
120
+
121
+
122
+
123
+
124
+
125
+
126
+
127
+ $db=null;
128
+
129
+
130
+
131
+ } catch (PDOException $e) {
132
+
133
+ die("<h2>データベース接続に失敗しました</h2>");
134
+
135
+ }
136
+
137
+
138
+
139
+ ```
140
+
141
+
142
+
143
+ ```mysql
144
+
145
+ コード
146
+
147
+
148
+
149
+ testテーブル
150
+
151
+ create table test(sno int auto_increment,day date,calorie int,protein DOUBLE,lipid DOUBLE,carbohydrate DOUBLE,calcium DOUBLE,iron DOUBLE,zinc DOUBLE,magnesium DOUBLE,folic_acid DOUBLE,vitamin_a DOUBLE,vitamin_d DOUBLE,vitamin_e DOUBLE,vitamin_k DOUBLE,vitamin_b1 DOUBLE,vitamin_b2 DOUBLE,vitamin_c DOUBLE,dietary_fiber DOUBLE,primary key(sno));
152
+
153
+
154
+
155
+
156
+
157
+ INSERT INTO test(day,calorie,protein,lipid,carbohydrate,calcium,iron,zinc,magnesium,folic_acid,vitamin_a,vitamin_d,vitamin_e,vitamin_k,vitamin_b1,vitamin_b2,vitamin_c,dietary_fiber)
158
+
159
+ values('2019-07-04',741,38.67,38.42,70.62,99.8,3.11,3.21,58.24,92,246.2,2.47,2.18,18.04,0.79,0.58,79,4.8),
160
+
161
+ ('2019-07-05',1103,45.2,58.4,92.9,694,1.9,6.4,66.7,42,270,0.5,1.2,13,0.29,0.48,10,0.5),
162
+
163
+ ('2019-07-06',1376,40.97,83.82,105.22,251.8,3.41,5.11,81.24,161,679.2,2.77,2.78,84.04,0.22,0.89,66,1.9),
164
+
165
+ ('2019-07-07',1088,58.57,62.52,82.72,833.8,2.21,6.91,117.24,163,373.2,0.97,2.18,20.04,0.89,0.72,59,7.8),
166
+
167
+ ('2019-07-08',835,47.8,32.3,80.2,27,2.5,4.3,76.72,66,30,11.1,1.4,11,0.88,0.5,9,1.8),
168
+
169
+ ('2019-07-09',988,53.78,45,96.34,552,5.59,8.6,104.7,158.5,209.44,2.6,1.99,86.32,1.03,0.81,68.72,5.18),
170
+
171
+ ('2019-07-10',1436,146.38,46.36,94.3,305,9.06,14.66,392.6,245.4,487.4,46.2,13.66,53.2,0.67,1.47,68,4.12),
172
+
173
+ ('2019-07-11',1049,62.6,36,142.9,1007,8.1,6.4,457,600,691,0.1,2.3,1276,1.28,1.6,57,14.11),
174
+
175
+ ('2019-07-12',828,31.6,39.7,80.4,63,3.4,4.1,45.7,56,150,2.3,1.4,14,0.32,0.58,10,0.5);
176
+
177
+
178
+
179
+ 
180
+
181
+ ```
182
+
183
+
184
+
185
+ ここから問題のページを簡易化したものです。
20
186
 
21
187
  ```php
22
188
 
23
- $sql='SELECT SUM(calorie), SUM(protein), SUM(lipid),SUM(carbohydrate), SUM(calcium), SUM(iron),SUM(zinc), SUM(magnesium), SUM(folic_acid),SUM(vitamin_a), SUM(vitamin_d), SUM(vitamin_e),SUM(vitamin_k), SUM(vitamin_b1), SUM(vitamin_b2),SUM(vitamin_c), SUM(dietary_fiber) FROM gohanmemor2 WHERE createdate >= DATE(DATE_ADD($day, INTERVAL -7 DAY))';
24
-
25
-
26
-
27
- $query =mysql_query($sum,$conn);
28
-
29
-
30
-
31
- $result=mysql_fetch_array($query_sum);
32
-
33
-
34
-
35
- $calorie = $result_sum['SUM(calorie)'];
36
-
37
-
38
-
39
- $protein = $result_sum['SUM(protein)'];
40
-
41
-
42
-
43
- $lipid = $result_sum['SUM(lipid)'];
44
-
45
-
46
-
47
- $carbohydrate = $result_sum['SUM(carbohydrate)'];
48
-
49
-
50
-
51
- $calcium = $result_sum['SUM(calcium)'];
52
-
53
-
54
-
55
- $iron = $result_sum['SUM(iron)'];
56
-
57
-
58
-
59
- $zinc = $result_sum['SUM(zinc)'];
60
-
61
-
62
-
63
- $magnesium = $result_sum['SUM(magnesium)'];
64
-
65
-
66
-
67
- $folic_acid = $result_sum['SUM(folic_acid)'];
68
-
69
-
70
-
71
- $vitamin_a = $result_sum['SUM(vitamin_a)'];
72
-
73
-
74
-
75
- $vitamin_d = $result_sum['SUM(vitamin_d)'];
76
-
77
-
78
-
79
- $vitamin_e = $result_sum['SUM(vitamin_e)'];
80
-
81
-
82
-
83
- $vitamin_k = $result_sum['SUM(vitamin_k)'];
84
-
85
-
86
-
87
- $vitamin_b1 = $result_sum['SUM(vitamin_b1)'];
88
-
89
-
90
-
91
- $vitamin_b2 = $result_sum['SUM(vitamin_b2)'];
92
-
93
-
94
-
95
- $vitamin_c = $result_sum['SUM(vitamin_c)'];
96
-
97
-
98
-
99
- $dietary_fiber = $result_sum['SUM(dietary_fiber)'];
189
+ 
190
+
191
+ <?php
192
+
193
+ //グラフ作成のPHP
194
+
195
+ //formから起算日（デフォルトは当日）を指定して送信されます。
196
+
197
+ $day=$GET_['day'];
198
+
199
+ ?>
200
+
201
+ <!DOCTYPE html>
202
+
203
+ <html>
204
+
205
+ <head>
206
+
207
+ <title>testグラフ</title>
208
+
209
+ <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
210
+
211
+ <script src="https://ajax.googleapis.com/ajax/libs/jquery/1.11.2/jquery.min.js" type="text/javascript"></script>
212
+
213
+ <script src="https://cdnjs.cloudflare.com/ajax/libs/Chart.js/2.1.4/Chart.min.js"></script>
214
+
215
+ </head>
216
+
217
+ <body>
218
+
219
+ <?php
220
+
221
+ try{
222
+
223
+ $db=new PDO('mysql:host=localhost;dbname=webdb','root','root');
224
+
225
+
226
+
227
+
228
+
229
+ print("<p>データベース[webdb]に接続しました</p>");
230
+
231
+
232
+
233
+ //起算日から1週間分のデータを取り出します。
234
+
235
+ $sql="SELECT * FROM test WHERE createdate >= DATE(DATE_ADD($day, INTERVAL -7 DAY))";
100
236
 
101
237
 
102
238
 
@@ -106,9 +242,77 @@
106
242
 
107
243
  $sto->execute();
108
244
 
245
+
246
+
109
-
247
+ while($row=$sto->fetch() ){
248
+
110
-
249
+ $sno =$row['sno'];
250
+
111
-
251
+ $day =$row['day'];
252
+
253
+ $calorie = ($row['calorie']);
254
+
255
+ $protein = ($row['protein']);
256
+
257
+ $lipid = ($row['lipid']);
258
+
259
+ $carbohydrate = ($row['carbohydrate']);
260
+
261
+ $calcium = ($row['calcium']);
262
+
263
+ $iron = ($row['iron']);
264
+
265
+ $zinc = ($row['zinc']);
266
+
267
+ $magnesium = ($row['magnesium']);
268
+
269
+ $folic_acid = ($row['folic_acid']);
270
+
271
+ $vitamin_a = ($row['vitamin_a']);
272
+
273
+ $vitamin_d = ($row['vitamin_d']);
274
+
275
+ $vitamin_e = ($row['vitamin_e']);
276
+
277
+ $vitamin_k = ($row['vitamin_k']);
278
+
279
+ $vitamin_b1 = ($row['vitamin_b1']);
280
+
281
+ $vitamin_b2 = ($row['vitamin_b2']);
282
+
283
+ $vitamin_c = ($row['vitamin_c']);
284
+
285
+ $dietary_fiber = ($row['dietary_fiber']);
286
+
287
+ }
288
+
289
+
290
+
291
+
292
+
293
+
294
+
295
+
296
+
297
+
298
+
299
+ //ビタミンとミネラルの合計を計算
300
+
301
+ $mineral=$carbohydrate +$calcium +$iron +$zinc +$magnesium;
302
+
303
+ $vitamin=$folic_acid +$vitamin_a +$vitamin_d +$vitamin_e +$vitamin_k +$vitamin_b1 +$vitamin_b2 +$vitamin_c;
304
+
305
+ $sto=$db->prepare($sql);
306
+
307
+ if($sto->execute()){
308
+
309
+ print("<p>データ受け取りしました</p>");
310
+
311
+ }else{
312
+
313
+ print("<p>SQL文実行時にエラーが発生しました</p>");
314
+
315
+ }
112
316
 
113
317
 
114
318
 
@@ -122,570 +326,292 @@
122
326
 
123
327
  }
124
328
 
125
-
329
+ }
330
+
331
+ //1週間分の摂取目標
332
+
333
+ $calorie_total=10850;
334
+
335
+ $protein_total=245;
336
+
337
+ $lipid_total=210;
338
+
339
+ $carbohydrate_total=455;
340
+
341
+ $calcium_total=4200;
342
+
343
+ $iron_total=45.5;
344
+
345
+ $zinc_total=35;
346
+
347
+ $magnesium_total=910;
348
+
349
+ $folic_acid_total=910;
350
+
351
+ $vitamin_a_total=3150;
352
+
353
+ $vitamin_d_total=280;
354
+
355
+ $vitamin_e_total=35.0;
356
+
357
+ $vitamin_k_total=595;
358
+
359
+ $vitamin_b1_total=5.6;
360
+
361
+ $vitamin_b2_total=6.3;
362
+
363
+ $vitamin_c_total=385;
364
+
365
+ $dietary_fiber_total=77;
366
+
367
+
368
+
369
+ //ビタミンとミネラルの合計を計算
370
+
371
+ $mineral_total=$carbohydrate_total +$calcium_total +$iron_total +$zinc_total +$magnesium_total;
372
+
373
+ $vitamin_total=$folic_acid_total +$vitamin_a_total +$vitamin_d_total +$vitamin_e_total +$vitamin_k_total +$vitamin_b1_total +$vitamin_b2_total +$vitamin_c_total;
374
+
375
+
376
+
377
+
378
+
379
+ //パーセントの計算を定義
380
+
381
+ function num2per($val, $val_total, $precision = 0) {
382
+
383
+ if (($val < 0)or($val_total < 0)) { return false; }
384
+
385
+ $val_percent = ($val / $val_total) * 100;
386
+
387
+ return round($val_percent, $precision);
388
+
389
+ }
390
+
391
+ $calorie_percent=num2per($calorie, $calorie_total);
392
+
393
+ $protein_percent=num2per($protein, $protein_total);
394
+
395
+ $lipid_percent=num2per($lipid, $lipid_total);
396
+
397
+ $dietary_fiber_percent=num2per($dietary_fiber, $dietary_fiber_total);
398
+
399
+ $mineral_percent=num2per($mineral, $mineral_total);
400
+
401
+ $vitamin_percent=num2per($vitamin, $vitamin_total);
402
+
403
+ ?>
404
+
405
+
406
+
407
+ <tr><td colspan="9" style="text-align:center; font-size:30px;background-color: #33FF99;">栄養素の合計値をグラフで表示</td></tr>
408
+
409
+
410
+
411
+
412
+
413
+ <!-- チャート描画キャンバス -->
414
+
415
+ <tr><td colspan="9" style="width:720px;height:500px;backgroud-color:#FFF;"><div><canvas id="myChart" style="background-color:#fff;"></canvas></div></td></tr>
416
+
417
+ <script>
418
+
419
+
420
+
421
+ // 色の設定
422
+
423
+ var colorSet = {
424
+
425
+ red: 'rgb(255, 99, 132)',
426
+
427
+ orange: 'rgb(255, 159, 64)',
428
+
429
+ yellow: 'rgb(255, 205, 86)',
430
+
431
+ green: 'rgb(75, 192, 192)',
432
+
433
+ blue: 'rgb(54, 162, 235)',
434
+
435
+ purple: 'rgb(153, 102, 255)',
436
+
437
+ grey: 'rgb(201, 203, 207)'
438
+
439
+ };
440
+
441
+
442
+
443
+ // 乱数生成(0～100)
444
+
445
+ var rnd100 = function(){
446
+
447
+ return Math.round(Math.random() * 100);
448
+
449
+ };
450
+
451
+
452
+
453
+ // 色のRGB変換
454
+
455
+ var color = Chart.helpers.color;
456
+
457
+
458
+
459
+ /*
460
+
461
+ * チャートの初期設定
462
+
463
+ */
464
+
465
+ var config = {
466
+
467
+ type: 'radar',
468
+
469
+ data: {
470
+
471
+ labels: ["エネルギー", "たんぱく質", "脂質", "炭水化物", "ミネラル", "ビタミン"],
472
+
473
+ datasets: [{
474
+
475
+ label: "摂取量",
476
+
477
+ backgroundColor: color(colorSet.red).alpha(0.5).rgbString(),
478
+
479
+ borderColor: colorSet.red,
480
+
481
+ pointBackgroundColor: colorSet.red,
482
+
483
+
484
+
485
+ data: [<?php echo $calorie_percent ?>, <?php echo $protein_percent ?>, <?php echo $lipid_percent ?>, <?php echo $carbohydrate_percent ?>, <?php echo $mineral_percent ?>, <?php echo $vitamin_percent ?> ]
486
+
487
+
488
+
489
+
490
+
491
+
492
+
493
+
494
+
495
+ },]
496
+
497
+ },
498
+
499
+ options: {
500
+
501
+ animation:false,
502
+
503
+ showTooltips: false,
504
+
505
+ legend: { position: 'bottom' },
506
+
507
+ title: {
508
+
509
+ display: true,
510
+
511
+ fontSize:20,
512
+
513
+ fontColor:'#666',
514
+
515
+ text: '栄養素チャート'
516
+
517
+ },
518
+
519
+ scale: {
520
+
521
+ display: true,
522
+
523
+ pointLabels: {
524
+
525
+ fontSize: 15,
526
+
527
+ fontColor: colorSet.blue
528
+
529
+ },
530
+
531
+ ticks: {
532
+
533
+ display: true,
534
+
535
+ fontSize: 12,
536
+
537
+ fontColor: colorSet.green,
538
+
539
+ min: 0,
540
+
541
+ max: 100,
542
+
543
+ beginAtZero: true
544
+
545
+ },
546
+
547
+ gridLines: {
548
+
549
+ display: true,
550
+
551
+ color: colorSet.yellow
552
+
553
+ }
554
+
555
+ }
556
+
557
+ }
558
+
559
+ };
560
+
561
+
562
+
563
+ /*
564
+
565
+ * チャートの作成
566
+
567
+ */
568
+
569
+ var myRadar = new Chart($("#myChart"), config);
570
+
571
+
572
+
573
+ </script>
574
+
575
+ </table>
576
+
577
+ </body>
578
+
579
+ </html>
126
580
 
127
581
  ```
128
582
 
129
- 試したこと追記
583
+
130
-
131
- WHERE createdate >= DATE(DATE_ADD('2019-07-10', INTERVAL -7 DAY))
584
+
132
-
133
- where以降の日付の指定が無ければ集計が取れます。where以降を付けるとエラーになります。
585
+ エラー表示は以下のエラーを書いています。
134
-
135
- #1054 - Unknown column 'createdate' in 'where clause'
586
+
136
-
137
- mysqliに書き直すとはこれで良いのでしょうか？
587
+ DBに接続しました。までは表示されますが
588
+
589
+ 以降は一切表示されておりません。
138
590
 
139
591
  ```php
140
592
 
141
-
142
-
143
- $result = $mysqli->query("SELECT SUM(calorie), SUM(protein), SUM(lipid),SUM(carbohydrate), SUM(calcium), SUM(iron),SUM(zinc), SUM(magnesium), SUM(folic_acid),SUM(vitamin_a), SUM(vitamin_d), SUM(vitamin_e),SUM(vitamin_k), SUM(vitamin_b1), SUM(vitamin_b2),SUM(vitamin_c), SUM(dietary_fiber) FROM gohanmemor2 WHERE createdate >= DATE(DATE_ADD($day, INTERVAL -7 DAY))";
144
-
145
-
146
-
147
- $row = $result->fetch_assoc($sum,$conn);
148
-
149
-
150
-
151
- $calorie = $row['SUM(calorie)'];
152
-
153
-
154
-
155
- $protein = $row['SUM(protein)'];
156
-
157
-
158
-
159
- $lipid = $row['SUM(lipid)'];
160
-
161
-
162
-
163
- $carbohydrate = $row['SUM(carbohydrate)'];
164
-
165
-
166
-
167
- $calcium = $row['SUM(calcium)'];
168
-
169
-
170
-
171
- $iron = $row['SUM(iron)'];
172
-
173
-
174
-
175
- $zinc = $row['SUM(zinc)'];
176
-
177
-
178
-
179
- $magnesium = $row['SUM(magnesium)'];
180
-
181
-
182
-
183
- $folic_acid = $row['SUM(folic_acid)'];
184
-
185
-
186
-
187
- $vitamin_a = $row['SUM(vitamin_a)'];
188
-
189
-
190
-
191
- $vitamin_d = $row['SUM(vitamin_d)'];
192
-
193
-
194
-
195
- $vitamin_e = $row['SUM(vitamin_e)'];
196
-
197
-
198
-
199
- $vitamin_k = $row['SUM(vitamin_k)'];
200
-
201
-
202
-
203
- $vitamin_b1 = $row['SUM(vitamin_b1)'];
204
-
205
-
206
-
207
- $vitamin_b2 = $row['SUM(vitamin_b2)'];
208
-
209
-
210
-
211
- $vitamin_c = $row['SUM(vitamin_c)'];
212
-
213
-
214
-
215
- $dietary_fiber = $row['SUM(dietary_fiber)'];
216
-
217
-
218
-
219
-
220
-
221
- ```table
222
-
223
-
224
-
225
- testテーブル
226
-
227
- create table test(sno int auto_increment,day date,calorie int,protein DOUBLE,lipid DOUBLE,carbohydrate DOUBLE,calcium DOUBLE,iron DOUBLE,zinc DOUBLE,magnesium DOUBLE,folic_acid DOUBLE,vitamin_a DOUBLE,vitamin_d DOUBLE,vitamin_e DOUBLE,vitamin_k DOUBLE,vitamin_b1 DOUBLE,vitamin_b2 DOUBLE,vitamin_c DOUBLE,dietary_fiber DOUBLE,primary key(sno));
228
-
229
-
230
-
231
-
232
-
233
- INSERT INTO test(day,calorie,protein,lipid,carbohydrate,calcium,iron,zinc,magnesium,folic_acid,vitamin_a,vitamin_d,vitamin_e,vitamin_k,vitamin_b1,vitamin_b2,vitamin_c,dietary_fiber)
234
-
235
- values('2019-07-04',741,38.67,38.42,70.62,99.8,3.11,3.21,58.24,92,246.2,2.47,2.18,18.04,0.79,0.58,79,4.8),
236
-
237
- ('2019-07-05',1103,45.2,58.4,92.9,694,1.9,6.4,66.7,42,270,0.5,1.2,13,0.29,0.48,10,0.5),
238
-
239
- ('2019-07-06',1376,40.97,83.82,105.22,251.8,3.41,5.11,81.24,161,679.2,2.77,2.78,84.04,0.22,0.89,66,1.9),
240
-
241
- ('2019-07-07',1088,58.57,62.52,82.72,833.8,2.21,6.91,117.24,163,373.2,0.97,2.18,20.04,0.89,0.72,59,7.8),
242
-
243
- ('2019-07-08',835,47.8,32.3,80.2,27,2.5,4.3,76.72,66,30,11.1,1.4,11,0.88,0.5,9,1.8),
244
-
245
- ('2019-07-09',988,53.78,45,96.34,552,5.59,8.6,104.7,158.5,209.44,2.6,1.99,86.32,1.03,0.81,68.72,5.18),
246
-
247
- ('2019-07-10',1436,146.38,46.36,94.3,305,9.06,14.66,392.6,245.4,487.4,46.2,13.66,53.2,0.67,1.47,68,4.12),
248
-
249
- ('2019-07-11',1049,62.6,36,142.9,1007,8.1,6.4,457,600,691,0.1,2.3,1276,1.28,1.6,57,14.11),
250
-
251
- ('2019-07-12',828,31.6,39.7,80.4,63,3.4,4.1,45.7,56,150,2.3,1.4,14,0.32,0.58,10,0.5);
252
-
253
- 
593
+ $sto=$db->prepare($sql);
594
+
595
+ if($sto->execute()){
596
+
597
+ print("<p>データ受け取りしました</p>");
598
+
599
+ }else{
600
+
601
+ print("<p>SQL文実行時にエラーが発生しました</p>");
602
+
603
+ }
604
+
605
+
606
+
607
+ $db=null;
608
+
609
+
610
+
611
+ } catch (PDOException $e) {
612
+
613
+ die("<h2>データベース接続に失敗しました</h2>");
614
+
615
+ }
254
616
 
255
617
  ```
256
-
257
-
258
-
259
- ここから問題のページを簡易化したものです。
260
-
261
- ```php
262
-
263
- 
264
-
265
- <?php
266
-
267
- //グラフ作成のPHP
268
-
269
- //formから起算日（デフォルトは当日）を指定して送信されます。
270
-
271
- $day=$GET_['day'];
272
-
273
- ?>
274
-
275
- <!DOCTYPE html>
276
-
277
- <html>
278
-
279
- <head>
280
-
281
- <title>testグラフ</title>
282
-
283
- <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
284
-
285
- <script src="https://ajax.googleapis.com/ajax/libs/jquery/1.11.2/jquery.min.js" type="text/javascript"></script>
286
-
287
- <script src="https://cdnjs.cloudflare.com/ajax/libs/Chart.js/2.1.4/Chart.min.js"></script>
288
-
289
- </head>
290
-
291
- <body>
292
-
293
- <?php
294
-
295
- try{
296
-
297
- $db=new PDO('mysql:host=localhost;dbname=webdb','root','root');
298
-
299
-
300
-
301
-
302
-
303
- print("<p>データベース[webdb]に接続しました</p>");
304
-
305
-
306
-
307
- //起算日から1週間分のデータを取り出します。
308
-
309
- $sql="SELECT * FROM test WHERE createdate >= DATE(DATE_ADD($day, INTERVAL -7 DAY))";
310
-
311
-
312
-
313
-
314
-
315
- $sto=$db->prepare($sql);
316
-
317
- $sto->execute();
318
-
319
-
320
-
321
- while($row=$sto->fetch() ){
322
-
323
- $sno =$row['sno'];
324
-
325
- $day =$row['day'];
326
-
327
- $calorie = ($row['calorie']);
328
-
329
- $protein = ($row['protein']);
330
-
331
- $lipid = ($row['lipid']);
332
-
333
- $carbohydrate = ($row['carbohydrate']);
334
-
335
- $calcium = ($row['calcium']);
336
-
337
- $iron = ($row['iron']);
338
-
339
- $zinc = ($row['zinc']);
340
-
341
- $magnesium = ($row['magnesium']);
342
-
343
- $folic_acid = ($row['folic_acid']);
344
-
345
- $vitamin_a = ($row['vitamin_a']);
346
-
347
- $vitamin_d = ($row['vitamin_d']);
348
-
349
- $vitamin_e = ($row['vitamin_e']);
350
-
351
- $vitamin_k = ($row['vitamin_k']);
352
-
353
- $vitamin_b1 = ($row['vitamin_b1']);
354
-
355
- $vitamin_b2 = ($row['vitamin_b2']);
356
-
357
- $vitamin_c = ($row['vitamin_c']);
358
-
359
- $dietary_fiber = ($row['dietary_fiber']);
360
-
361
- }
362
-
363
-
364
-
365
-
366
-
367
-
368
-
369
-
370
-
371
-
372
-
373
- //ビタミンとミネラルの合計を計算
374
-
375
- $mineral=$carbohydrate +$calcium +$iron +$zinc +$magnesium;
376
-
377
- $vitamin=$folic_acid +$vitamin_a +$vitamin_d +$vitamin_e +$vitamin_k +$vitamin_b1 +$vitamin_b2 +$vitamin_c;
378
-
379
- $sto=$db->prepare($sql);
380
-
381
- if($sto->execute()){
382
-
383
- print("<p>データ受け取りしました</p>");
384
-
385
- }else{
386
-
387
- print("<p>SQL文実行時にエラーが発生しました</p>");
388
-
389
- }
390
-
391
-
392
-
393
- $db=null;
394
-
395
-
396
-
397
- } catch (PDOException $e) {
398
-
399
- die("<h2>データベース接続に失敗しました</h2>");
400
-
401
- }
402
-
403
- }
404
-
405
- //1週間分の摂取目標
406
-
407
- $calorie_total=10850;
408
-
409
- $protein_total=245;
410
-
411
- $lipid_total=210;
412
-
413
- $carbohydrate_total=455;
414
-
415
- $calcium_total=4200;
416
-
417
- $iron_total=45.5;
418
-
419
- $zinc_total=35;
420
-
421
- $magnesium_total=910;
422
-
423
- $folic_acid_total=910;
424
-
425
- $vitamin_a_total=3150;
426
-
427
- $vitamin_d_total=280;
428
-
429
- $vitamin_e_total=35.0;
430
-
431
- $vitamin_k_total=595;
432
-
433
- $vitamin_b1_total=5.6;
434
-
435
- $vitamin_b2_total=6.3;
436
-
437
- $vitamin_c_total=385;
438
-
439
- $dietary_fiber_total=77;
440
-
441
-
442
-
443
- //ビタミンとミネラルの合計を計算
444
-
445
- $mineral_total=$carbohydrate_total +$calcium_total +$iron_total +$zinc_total +$magnesium_total;
446
-
447
- $vitamin_total=$folic_acid_total +$vitamin_a_total +$vitamin_d_total +$vitamin_e_total +$vitamin_k_total +$vitamin_b1_total +$vitamin_b2_total +$vitamin_c_total;
448
-
449
-
450
-
451
-
452
-
453
- //パーセントの計算を定義
454
-
455
- function num2per($val, $val_total, $precision = 0) {
456
-
457
- if (($val < 0)or($val_total < 0)) { return false; }
458
-
459
- $val_percent = ($val / $val_total) * 100;
460
-
461
- return round($val_percent, $precision);
462
-
463
- }
464
-
465
- $calorie_percent=num2per($calorie, $calorie_total);
466
-
467
- $protein_percent=num2per($protein, $protein_total);
468
-
469
- $lipid_percent=num2per($lipid, $lipid_total);
470
-
471
- $dietary_fiber_percent=num2per($dietary_fiber, $dietary_fiber_total);
472
-
473
- $mineral_percent=num2per($mineral, $mineral_total);
474
-
475
- $vitamin_percent=num2per($vitamin, $vitamin_total);
476
-
477
- ?>
478
-
479
-
480
-
481
- <tr><td colspan="9" style="text-align:center; font-size:30px;background-color: #33FF99;">栄養素の合計値をグラフで表示</td></tr>
482
-
483
-
484
-
485
-
486
-
487
- <!-- チャート描画キャンバス -->
488
-
489
- <tr><td colspan="9" style="width:720px;height:500px;backgroud-color:#FFF;"><div><canvas id="myChart" style="background-color:#fff;"></canvas></div></td></tr>
490
-
491
- <script>
492
-
493
-
494
-
495
- // 色の設定
496
-
497
- var colorSet = {
498
-
499
- red: 'rgb(255, 99, 132)',
500
-
501
- orange: 'rgb(255, 159, 64)',
502
-
503
- yellow: 'rgb(255, 205, 86)',
504
-
505
- green: 'rgb(75, 192, 192)',
506
-
507
- blue: 'rgb(54, 162, 235)',
508
-
509
- purple: 'rgb(153, 102, 255)',
510
-
511
- grey: 'rgb(201, 203, 207)'
512
-
513
- };
514
-
515
-
516
-
517
- // 乱数生成(0～100)
518
-
519
- var rnd100 = function(){
520
-
521
- return Math.round(Math.random() * 100);
522
-
523
- };
524
-
525
-
526
-
527
- // 色のRGB変換
528
-
529
- var color = Chart.helpers.color;
530
-
531
-
532
-
533
- /*
534
-
535
- * チャートの初期設定
536
-
537
- */
538
-
539
- var config = {
540
-
541
- type: 'radar',
542
-
543
- data: {
544
-
545
- labels: ["エネルギー", "たんぱく質", "脂質", "炭水化物", "ミネラル", "ビタミン"],
546
-
547
- datasets: [{
548
-
549
- label: "摂取量",
550
-
551
- backgroundColor: color(colorSet.red).alpha(0.5).rgbString(),
552
-
553
- borderColor: colorSet.red,
554
-
555
- pointBackgroundColor: colorSet.red,
556
-
557
-
558
-
559
- data: [<?php echo $calorie_percent ?>, <?php echo $protein_percent ?>, <?php echo $lipid_percent ?>, <?php echo $carbohydrate_percent ?>, <?php echo $mineral_percent ?>, <?php echo $vitamin_percent ?> ]
560
-
561
-
562
-
563
-
564
-
565
-
566
-
567
-
568
-
569
- },]
570
-
571
- },
572
-
573
- options: {
574
-
575
- animation:false,
576
-
577
- showTooltips: false,
578
-
579
- legend: { position: 'bottom' },
580
-
581
- title: {
582
-
583
- display: true,
584
-
585
- fontSize:20,
586
-
587
- fontColor:'#666',
588
-
589
- text: '栄養素チャート'
590
-
591
- },
592
-
593
- scale: {
594
-
595
- display: true,
596
-
597
- pointLabels: {
598
-
599
- fontSize: 15,
600
-
601
- fontColor: colorSet.blue
602
-
603
- },
604
-
605
- ticks: {
606
-
607
- display: true,
608
-
609
- fontSize: 12,
610
-
611
- fontColor: colorSet.green,
612
-
613
- min: 0,
614
-
615
- max: 100,
616
-
617
- beginAtZero: true
618
-
619
- },
620
-
621
- gridLines: {
622
-
623
- display: true,
624
-
625
- color: colorSet.yellow
626
-
627
- }
628
-
629
- }
630
-
631
- }
632
-
633
- };
634
-
635
-
636
-
637
- /*
638
-
639
- * チャートの作成
640
-
641
- */
642
-
643
- var myRadar = new Chart($("#myChart"), config);
644
-
645
-
646
-
647
- </script>
648
-
649
- </table>
650
-
651
- </body>
652
-
653
- </html>
654
-
655
- ```
656
-
657
-
658
-
659
- エラー表示は以下のエラーを書いています。
660
-
661
- DBに接続しました。までは表示されますが
662
-
663
- 以降は一切表示されておりません。
664
-
665
- ```php
666
-
667
- $sto=$db->prepare($sql);
668
-
669
- if($sto->execute()){
670
-
671
- print("<p>データ受け取りしました</p>");
672
-
673
- }else{
674
-
675
- print("<p>SQL文実行時にエラーが発生しました</p>");
676
-
677
- }
678
-
679
-
680
-
681
- $db=null;
682
-
683
-
684
-
685
- } catch (PDOException $e) {
686
-
687
- die("<h2>データベース接続に失敗しました</h2>");
688
-
689
- }
690
-
691
- ```

5

mysqliに変更してみました。

2019/07/04 03:27

投稿

mizuoniku
mizuoniku

スコア10

test CHANGED
File without changes
test CHANGED
@@ -126,6 +126,98 @@
126
126
 
127
127
  ```
128
128
 
129
+ 試したこと追記
130
+
131
+ WHERE createdate >= DATE(DATE_ADD('2019-07-10', INTERVAL -7 DAY))
132
+
133
+ where以降の日付の指定が無ければ集計が取れます。where以降を付けるとエラーになります。
134
+
135
+ #1054 - Unknown column 'createdate' in 'where clause'
136
+
137
+ mysqliに書き直すとはこれで良いのでしょうか？
138
+
139
+ ```php
140
+
141
+
142
+
143
+ $result = $mysqli->query("SELECT SUM(calorie), SUM(protein), SUM(lipid),SUM(carbohydrate), SUM(calcium), SUM(iron),SUM(zinc), SUM(magnesium), SUM(folic_acid),SUM(vitamin_a), SUM(vitamin_d), SUM(vitamin_e),SUM(vitamin_k), SUM(vitamin_b1), SUM(vitamin_b2),SUM(vitamin_c), SUM(dietary_fiber) FROM gohanmemor2 WHERE createdate >= DATE(DATE_ADD($day, INTERVAL -7 DAY))";
144
+
145
+
146
+
147
+ $row = $result->fetch_assoc($sum,$conn);
148
+
149
+
150
+
151
+ $calorie = $row['SUM(calorie)'];
152
+
153
+
154
+
155
+ $protein = $row['SUM(protein)'];
156
+
157
+
158
+
159
+ $lipid = $row['SUM(lipid)'];
160
+
161
+
162
+
163
+ $carbohydrate = $row['SUM(carbohydrate)'];
164
+
165
+
166
+
167
+ $calcium = $row['SUM(calcium)'];
168
+
169
+
170
+
171
+ $iron = $row['SUM(iron)'];
172
+
173
+
174
+
175
+ $zinc = $row['SUM(zinc)'];
176
+
177
+
178
+
179
+ $magnesium = $row['SUM(magnesium)'];
180
+
181
+
182
+
183
+ $folic_acid = $row['SUM(folic_acid)'];
184
+
185
+
186
+
187
+ $vitamin_a = $row['SUM(vitamin_a)'];
188
+
189
+
190
+
191
+ $vitamin_d = $row['SUM(vitamin_d)'];
192
+
193
+
194
+
195
+ $vitamin_e = $row['SUM(vitamin_e)'];
196
+
197
+
198
+
199
+ $vitamin_k = $row['SUM(vitamin_k)'];
200
+
201
+
202
+
203
+ $vitamin_b1 = $row['SUM(vitamin_b1)'];
204
+
205
+
206
+
207
+ $vitamin_b2 = $row['SUM(vitamin_b2)'];
208
+
209
+
210
+
211
+ $vitamin_c = $row['SUM(vitamin_c)'];
212
+
213
+
214
+
215
+ $dietary_fiber = $row['SUM(dietary_fiber)'];
216
+
217
+
218
+
219
+
220
+
129
221
  ```table
130
222
 
131
223
 

4

エラー文追記

2019/07/04 02:43

投稿

mizuoniku
mizuoniku

スコア10

test CHANGED
File without changes
test CHANGED
@@ -284,9 +284,21 @@
284
284
 
285
285
  $vitamin=$folic_acid +$vitamin_a +$vitamin_d +$vitamin_e +$vitamin_k +$vitamin_b1 +$vitamin_b2 +$vitamin_c;
286
286
 
287
-
287
+ $sto=$db->prepare($sql);
288
+
288
-
289
+ if($sto->execute()){
290
+
291
+ print("<p>データ受け取りしました</p>");
292
+
293
+ }else{
294
+
295
+ print("<p>SQL文実行時にエラーが発生しました</p>");
296
+
297
+ }
298
+
299
+
300
+
289
- $db=null;
301
+ $db=null;
290
302
 
291
303
 
292
304
 
@@ -296,7 +308,7 @@
296
308
 
297
309
  }
298
310
 
299
-
311
+ }
300
312
 
301
313
  //1週間分の摂取目標
302
314
 

3

エラーの状態を追記

2019/07/04 02:07

投稿

mizuoniku
mizuoniku

スコア10

test CHANGED
File without changes
test CHANGED
@@ -549,3 +549,39 @@
549
549
  </html>
550
550
 
551
551
  ```
552
+
553
+
554
+
555
+ エラー表示は以下のエラーを書いています。
556
+
557
+ DBに接続しました。までは表示されますが
558
+
559
+ 以降は一切表示されておりません。
560
+
561
+ ```php
562
+
563
+ $sto=$db->prepare($sql);
564
+
565
+ if($sto->execute()){
566
+
567
+ print("<p>データ受け取りしました</p>");
568
+
569
+ }else{
570
+
571
+ print("<p>SQL文実行時にエラーが発生しました</p>");
572
+
573
+ }
574
+
575
+
576
+
577
+ $db=null;
578
+
579
+
580
+
581
+ } catch (PDOException $e) {
582
+
583
+ die("<h2>データベース接続に失敗しました</h2>");
584
+
585
+ }
586
+
587
+ ```

2

MYSQLヴァージョン追記

2019/07/04 01:29

投稿

mizuoniku
mizuoniku

スコア10

test CHANGED
File without changes
test CHANGED
@@ -12,6 +12,8 @@
12
12
 
13
13
  ご指摘頂きありがとうございました。
14
14
 
15
+ MYSQLのヴァージョンはmysql - 5.1.73です。
16
+
15
17
 
16
18
 
17
19
  試したこと（mysqlのSELECT文）

1

コードをマークダウンにしました。初心者アイコン付けました。TAGを変更しました。

2019/07/04 01:13

投稿

mizuoniku
mizuoniku

スコア10

test CHANGED
File without changes
test CHANGED
@@ -10,6 +10,122 @@
10
10
 
11
11
  初心者でコードが読みにくくて申し訳ありません。
12
12
 
13
+ ご指摘頂きありがとうございました。
14
+
15
+
16
+
17
+ 試したこと（mysqlのSELECT文）
18
+
19
+ ```php
20
+
21
+ $sql='SELECT SUM(calorie), SUM(protein), SUM(lipid),SUM(carbohydrate), SUM(calcium), SUM(iron),SUM(zinc), SUM(magnesium), SUM(folic_acid),SUM(vitamin_a), SUM(vitamin_d), SUM(vitamin_e),SUM(vitamin_k), SUM(vitamin_b1), SUM(vitamin_b2),SUM(vitamin_c), SUM(dietary_fiber) FROM gohanmemor2 WHERE createdate >= DATE(DATE_ADD($day, INTERVAL -7 DAY))';
22
+
23
+
24
+
25
+ $query =mysql_query($sum,$conn);
26
+
27
+
28
+
29
+ $result=mysql_fetch_array($query_sum);
30
+
31
+
32
+
33
+ $calorie = $result_sum['SUM(calorie)'];
34
+
35
+
36
+
37
+ $protein = $result_sum['SUM(protein)'];
38
+
39
+
40
+
41
+ $lipid = $result_sum['SUM(lipid)'];
42
+
43
+
44
+
45
+ $carbohydrate = $result_sum['SUM(carbohydrate)'];
46
+
47
+
48
+
49
+ $calcium = $result_sum['SUM(calcium)'];
50
+
51
+
52
+
53
+ $iron = $result_sum['SUM(iron)'];
54
+
55
+
56
+
57
+ $zinc = $result_sum['SUM(zinc)'];
58
+
59
+
60
+
61
+ $magnesium = $result_sum['SUM(magnesium)'];
62
+
63
+
64
+
65
+ $folic_acid = $result_sum['SUM(folic_acid)'];
66
+
67
+
68
+
69
+ $vitamin_a = $result_sum['SUM(vitamin_a)'];
70
+
71
+
72
+
73
+ $vitamin_d = $result_sum['SUM(vitamin_d)'];
74
+
75
+
76
+
77
+ $vitamin_e = $result_sum['SUM(vitamin_e)'];
78
+
79
+
80
+
81
+ $vitamin_k = $result_sum['SUM(vitamin_k)'];
82
+
83
+
84
+
85
+ $vitamin_b1 = $result_sum['SUM(vitamin_b1)'];
86
+
87
+
88
+
89
+ $vitamin_b2 = $result_sum['SUM(vitamin_b2)'];
90
+
91
+
92
+
93
+ $vitamin_c = $result_sum['SUM(vitamin_c)'];
94
+
95
+
96
+
97
+ $dietary_fiber = $result_sum['SUM(dietary_fiber)'];
98
+
99
+
100
+
101
+
102
+
103
+ $sto=$db->prepare($sql);
104
+
105
+ $sto->execute();
106
+
107
+
108
+
109
+
110
+
111
+
112
+
113
+ $db=null;
114
+
115
+
116
+
117
+ } catch (PDOException $e) {
118
+
119
+ die("<h2>データベース接続に失敗しました</h2>");
120
+
121
+ }
122
+
123
+
124
+
125
+ ```
126
+
127
+ ```table
128
+
13
129
 
14
130
 
15
131
  testテーブル
@@ -40,12 +156,18 @@
40
156
 
41
157
  ('2019-07-12',828,31.6,39.7,80.4,63,3.4,4.1,45.7,56,150,2.3,1.4,14,0.32,0.58,10,0.5);
42
158
 
43
-
159
+ 
160
+
161
+ ```
44
162
 
45
163
 
46
164
 
47
165
  ここから問題のページを簡易化したものです。
48
166
 
167
+ ```php
168
+
169
+ 
170
+
49
171
  <?php
50
172
 
51
173
  //グラフ作成のPHP
@@ -142,11 +264,7 @@
142
264
 
143
265
  $dietary_fiber = ($row['dietary_fiber']);
144
266
 
145
-
146
-
147
- //1週間分の合計値の出す計算式が分からず悩んでおります。
148
-
149
- どうかご教授お願いします。
267
+ }
150
268
 
151
269
 
152
270
 
@@ -426,4 +544,6 @@
426
544
 
427
545
  </body>
428
546
 
429
- </html>
547
+ </html>
548
+
549
+ ```