ข่วยเรื่อง การหาผลรวมข้อมูล ย้อนหลังหน่อยครับ มีDB และรูปภาพ ตัวอย่าง
รับค่าวันที่มาดังนี้
$datepicker1="2015-10-20";
$datepicker2="2015-10-26";
เงื่อนไข
Current week 2015-10-20 ถึง 2015-10-26
(ย้อนหลัง 1 สัปดาห์)1 WK 2015-10-19 ถึง 2015-10-13
(ย้อนหลัง 2 สัปดาห์)2 WK 2015-10-12 ถึง 2015-10-06
(ย้อนหลัง 3 สัปดาห์)3 WK 2015-10-05 ถึง 2015-09-29
ต้องเขียน รูปแบบ คิวรี่ยังไงครับ
Code
-- phpMyAdmin SQL Dump
-- version 3.1.3.1
-- http://www.phpmyadmin.net
--
-- โฮสต์: localhost
-- เวลาในการสร้าง: 27 ต.ค. 2015 น.
-- รุ่นของเซิร์ฟเวอร์: 5.1.33
-- รุ่นของ PHP: 5.2.9
SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
--
-- ฐานข้อมูล: `eecv_data`
--
-- --------------------------------------------------------
--
-- โครงสร้างตาราง `smu_cal`
--
CREATE TABLE IF NOT EXISTS `smu_cal` (
`smu_id` varchar(10) COLLATE utf8_bin NOT NULL,
`smu_date` varchar(10) COLLATE utf8_bin NOT NULL,
`smu_cra` int(10) NOT NULL,
`smu_crb` int(10) NOT NULL,
`smu_crc` int(10) NOT NULL,
`smu_crd` int(10) NOT NULL,
`smu_crj` int(10) NOT NULL,
PRIMARY KEY (`smu_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
--
-- dump ตาราง `smu_cal`
--
INSERT INTO `smu_cal` (`smu_id`, `smu_date`, `smu_cra`, `smu_crb`, `smu_crc`, `smu_crd`, `smu_crj`) VALUES
('1', '2015-06-04', 17, 15, 19, 17, 18),
('2', '2015-06-05', 18, 18, 20, 19, 18),
('3', '2015-06-06', 18, 20, 19, 16, 18),
('6', '2015-06-07', 19, 21, 21, 0, 22),
('7', '2015-06-08', 3, 4, 3, 0, 3),
('8', '2015-06-09', 15, 15, 16, 14, 16),
('9', '2015-06-10', 15, 22, 22, 17, 21),
('10', '2015-06-11', 15, 22, 20, 10, 21),
('11', '2015-06-12', 14, 14, 14, 10, 13),
('12', '2015-06-13', 13, 16, 15, 13, 18),
('13', '2015-06-14', 12, 14, 13, 12, 13),
('14', '2015-06-15', 1, 11, 13, 8, 12),
('15', '2015-06-16', 8, 16, 15, 15, 18),
('16', '2015-06-17', 13, 21, 18, 18, 20),
('17', '2015-06-18', 11, 11, 12, 11, 11),
('18', '2015-06-19', 18, 22, 22, 6, 22),
('19', '2015-06-20', 2, 16, 16, 14, 17),
('20', '2015-06-21', 2, 20, 22, 21, 21),
('21', '2015-06-22', 7, 11, 11, 3, 9),
('22', '2015-06-23', 0, 20, 13, 15, 20),
('23', '2015-06-24', 18, 20, 18, 16, 20),
('24', '2015-06-25', 11, 13, 11, 11, 12),
('25', '2015-06-26', 10, 18, 18, 18, 10),
('26', '2015-06-27', 9, 10, 9, 9, 10),
('27', '2015-06-28', 13, 14, 14, 13, 12),
('28', '2015-06-29', 8, 9, 8, 7, 10),
('29', '2015-06-30', 5, 8, 5, 7, 6),
('30', '2015-07-01', 0, 0, 0, 0, 0),
('31', '2015-07-02', 20, 20, 21, 14, 20),
('32', '2015-07-03', 14, 12, 14, 14, 15),
('33', '2015-07-04', 20, 22, 20, 19, 21),
('34', '2015-07-05', 16, 17, 16, 17, 18),
('35', '2015-07-06', 14, 15, 16, 14, 14),
('36', '2015-07-07', 14, 15, 15, 14, 14),
('37', '2015-07-08', 8, 15, 13, 12, 12),
('38', '2015-07-09', 0, 15, 14, 15, 17),
('39', '2015-07-10', 1, 20, 23, 21, 17),
('40', '2015-07-11', 3, 11, 8, 6, 14),
('41', '2015-07-12', 0, 19, 19, 17, 18),
('42', '2015-07-13', 2, 16, 17, 18, 18),
('43', '2015-07-14', 10, 15, 12, 13, 15),
('44', '2015-07-15', 17, 18, 13, 17, 19),
('45', '2015-07-16', 9, 10, 10, 9, 9),
('46', '2015-07-17', 16, 17, 16, 19, 19),
('47', '2015-07-18', 4, 5, 6, 5, 6),
('48', '2015-07-19', 13, 17, 16, 5, 16),
('49', '2015-07-20', 8, 10, 9, 2, 9),
('50', '2015-07-21', 6, 11, 9, 9, 11),
('51', '2015-07-22', 13, 19, 20, 14, 19),
('52', '2015-07-23', 12, 13, 6, 12, 12),
('53', '2015-07-24', 19, 16, 25, 14, 19),
('54', '2015-07-25', 17, 17, 17, 15, 17),
('55', '2015-07-26', 17, 20, 16, 18, 19),
('56', '2015-07-27', 12, 18, 19, 16, 17),
('57', '2015-07-28', 10, 11, 12, 12, 14),
('58', '2015-07-29', 16, 17, 19, 9, 17),
('59', '2015-07-30', 15, 10, 17, 13, 18),
('60', '2015-07-31', 12, 13, 15, 13, 16),
('61', '2015-08-01', 13, 15, 15, 11, 13),
('62', '2015-08-02', 12, 16, 13, 13, 16),
('63', '2015-08-03', 17, 16, 18, 19, 16),
('64', '2015-08-04', 6, 9, 9, 8, 12),
('65', '2015-08-05', 9, 22, 21, 20, 20),
('66', '2015-08-06', 3, 13, 15, 16, 13),
('67', '2015-08-07', 8, 19, 15, 20, 20),
('68', '2015-08-08', 5, 15, 15, 11, 15),
('69', '2015-08-09', 14, 18, 18, 12, 20),
('70', '2015-08-10', 16, 20, 14, 11, 20),
('71', '2015-08-11', 8, 10, -17, 6, 10),
('72', '2015-08-12', 7, 14, 15, 13, 12),
('73', '2015-08-13', 3, 15, 12, 15, 15),
('74', '2015-08-14', 0, 7, 7, 6, 7),
('75', '2015-08-15', 0, 6, 6, 7, 7),
('76', '2015-08-16', 18, 0, 18, 16, 12),
('77', '2015-08-17', 12, 0, 14, 10, 0),
('78', '2015-08-18', 17, 0, 18, 18, 0),
('79', '2015-08-19', 16, 0, 13, 15, 0),
('80', '2015-08-20', 20, 0, 24, 22, 0),
('81', '2015-08-21', 22, 0, 19, 18, 0),
('82', '2015-08-22', 14, 0, 15, 10, 0),
('83', '2015-08-23', 20, 0, 20, 19, 0),
('84', '2015-08-24', 11, 9, 11, 12, 0),
('85', '2015-08-25', 15, 17, 16, 17, 0),
('86', '2015-08-26', 15, 8, 14, 13, 0),
('87', '2015-08-27', 19, 19, 18, 17, 0),
('88', '2015-08-28', 21, 19, 22, 19, 0),
('89', '2015-08-29', 15, 10, 15, 15, 0),
('90', '2015-08-30', 16, 16, 17, 17, 24),
('91', '2015-08-31', 20, 19, 20, 20, 21),
('92', '2015-09-01', 3, 2, 4, 3, 2),
('93', '2015-09-02', 10, 12, 10, 10, 9),
('94', '2015-09-03', 14, 14, 16, 13, 14),
('95', '1970-01-01', 0, 0, 0, 0, 0),
('96', '2015-09-04', 7, 7, 4, 6, 7),
('97', '2015-09-05', 9, 10, 11, 8, 10),
('98', '2015-09-06', 17, 18, 17, 14, 17),
('99', '2015-09-07', 14, 19, 14, 18, 21),
('100', '2015-09-08', 6, 10, 8, 10, 10),
('101', '2015-09-09', 16, 17, 17, 18, 16),
('102', '2015-09-10', 16, 16, 15, 14, 16),
('103', '2015-09-11', 10, 13, 13, 11, 14),
('104', '2015-09-12', 14, 17, 17, 17, 16),
('105', '2015-09-13', 17, 15, 16, 15, 16),
('106', '2015-09-14', 0, 0, 0, 0, 0),
('107', '2015-09-15', 7, 6, 3, 7, 6),
('108', '2015-09-16', 19, 19, 22, 18, 22),
('109', '2015-09-17', 12, 12, 11, 11, 9),
('110', '2015-09-18', 3, 2, 4, 2, 3),
('111', '2015-09-19', 10, 7, 11, 8, 10),
('112', '2015-09-20', 16, 16, 15, 8, 16),
('113', '2015-09-21', 20, 20, 20, 11, 19),
('114', '2015-09-22', 13, 12, 15, 1, 12),
('115', '2015-09-23', 20, 20, 20, 17, 19),
('116', '2015-09-24', 13, 12, 12, 11, 15),
('117', '2015-09-25', 21, 20, 19, 18, 19),
('118', '2015-09-26', 15, 13, 14, 14, 14),
('119', '2015-09-27', 18, 19, 20, 14, 21),
('120', '2015-09-28', 19, 19, 19, 18, 17),
('121', '2015-09-29', 12, 14, 12, 13, 14),
('122', '2015-09-30', 22, 21, 20, 22, 22),
('123', '2015-10-01', 17, 16, 16, 15, 16),
('124', '2015-10-02', 18, 19, 20, 17, 18),
('125', '2015-10-03', 16, 17, 15, 18, 19),
('126', '2015-10-04', 21, 20, 23, 19, 16),
('127', '2015-10-05', 14, 14, 11, 14, 15),
('128', '2015-10-06', 13, 21, 19, 20, 21),
('129', '2015-10-07', 20, 20, 20, 18, 19),
('130', '2015-10-08', 14, 13, 15, 15, 15),
('131', '2015-10-09', 12, 10, 12, 11, 12),
('132', '2015-10-10', 17, 17, 16, 17, 10),
('133', '2015-10-11', 13, 12, 12, 12, 11),
('134', '2015-10-12', 12, 11, 11, 12, 12),
('135', '2015-10-13', 19, 18, 19, 21, 20),
('136', '2015-10-14', 12, 12, 13, 12, 13),
('137', '2015-10-15', 13, 13, 24, 11, 13),
('138', '2015-10-16', 20, 19, 11, 20, 21),
('139', '2015-10-17', 17, 12, 17, 16, 12),
('140', '2015-10-18', 13, 14, 16, 15, 16),
('141', '2015-10-19', 20, 17, 20, 20, 20),
('142', '2015-10-24', 0, 0, 0, 0, 0),
('143', '2015-10-20', 0, 0, 0, 0, 0),
('144', '2015-10-21', 0, 0, 0, 0, 0),
('145', '2015-10-22', 18, 16, 20, 19, 21),
('146', '2015-10-23', 18, 14, 19, 8, 21),
('147', '2015-10-24', 48, 38, 50, 27, 52),
('148', '2015-10-25', 14, 17, 13, 14, 16),
('149', '2015-10-24', 12, 8, 11, 0, 10),
('150', '2015-10-25', 14, 17, 13, 14, 16),
('151', '2015-10-26', 10, 9, 12, 6, 10);
Tag : PHP, MySQL, HTML/CSS, jQuery, CakePHP
Date :
2015-10-27 15:37:06
By :
bondsanti
View :
1291
Reply :
15
อันนี้เลยครับ => PHP DateAdd
Code (PHP)
echo date('Y-m-d',strtotime("-1 week"));
หลังจากได้แล้วค่อยเอาไป Between กันครับ
Date :
2015-10-27 21:58:04
By :
mr.win
หรือใช้ mysql statement คิวรี่โดยตรงเลยครับ
Code (PHP)
$enDate = '2015-10-26';
$sql = 'select
sum( dte between DATE_SUB('$stDate', interval 6 day) and '$stDate' ) curweek,
sum( dte between DATE_SUB('$stDate', interval 13 day) and DATE_SUB('$stDate', interval 7 day)) retro1week,
sum( dte between DATE_SUB('$stDate', interval 20 day) and DATE_SUB('$stDate', interval 14 day)) retro2week,
sum( dte between DATE_SUB('$stDate', interval 27 day) and DATE_SUB('$stDate', interval 21 day)) retro3week
from table where dte >Date_Sub('$enDate', interval 28 day) ";
แก้ไข ตก คอมม่า
ประวัติการแก้ไข 2015-10-28 07:40:21
Date :
2015-10-28 02:29:47
By :
NewbiePHP
เด๋ี๋ยวจะลองทำดูนะครับ
Date :
2015-10-28 07:10:13
By :
bondsanti
Date :
2015-10-28 10:42:39
By :
mr.win
ได้แบบนี้แล้ว ข้อมูลก็ยังไม่ถูกอ่ะครับ
Code
<?php
$datepicker1="2015-10-20";
$datepicker2="2015-10-26";
$arr_item=array("CRUSHER A","CRUSHER B","CRUSHER C","CRUSHER D","CRUSHER J");
$arr_item_value[][]=array();
for($i=0;$i<=3;$i++){
$week_minus=$i+1;
$datepicker1=date("Y-m-d",strtotime("-$week_minus week"));
$datepicker2=date("Y-m-d",strtotime($datepicker1."+6 day"));
echo $strSQL="
SELECT SUM(smu_cra) as sum_a,
SUM(smu_crb) as sum_b,
SUM(smu_crc) as sum_c,
SUM(smu_crd) as sum_d,
SUM(smu_crj) as sum_j
FROM smu_cal WHERE
date(smu_date)>='".$datepicker1."'
AND date(smu_date)<='".$datepicker2."'
";
$result=mysql_db_query ($objDB,$strSQL);
while($row=mysql_fetch_assoc($result)){
$arr_item_value[$i][0]=$row['sum_a'];
$arr_item_value[$i][1]=$row['sum_b'];
$arr_item_value[$i][2]=$row['sum_c'];
$arr_item_value[$i][3]=$row['sum_d'];
$arr_item_value[$i][4]=$row['sum_j'];
}
}
?>
<?php foreach($arr_item as $k=>$v){ ?>
<tbody>
<tr>
<td><?php echo $v;?></td>
<td><?php echo $arr_item_value[3][$k];?></td>
<td><?php echo $arr_item_value[2][$k];?></td>
<td><?php echo $arr_item_value[1][$k];?></td>
<td><?php echo $arr_item_value[0][$k];?></td>
</tr>
<?php } ?>
<tr>
<td>TOTAL</td>
<td><?=array_sum($arr_item_value[3])?></td>
<td><?=array_sum($arr_item_value[2])?></td>
<td><?=array_sum($arr_item_value[1])?></td>
<td><?=array_sum($arr_item_value[0])?></td>
</tr>
</tbody>
</table>
echo SQL ดูเป็นแบบนี้
Code
SELECT SUM(smu_cra) as sum_a, SUM(smu_crb) as sum_b, SUM(smu_crc) as sum_c, SUM(smu_crd) as sum_d, SUM(smu_crj) as sum_j FROM smu_cal WHERE date(smu_date)>='2015-10-21' AND date(smu_date)<='2015-10-27' SELECT SUM(smu_cra) as sum_a, SUM(smu_crb) as sum_b, SUM(smu_crc) as sum_c, SUM(smu_crd) as sum_d, SUM(smu_crj) as sum_j FROM smu_cal WHERE date(smu_date)>='2015-10-14' AND date(smu_date)<='2015-10-20' SELECT SUM(smu_cra) as sum_a, SUM(smu_crb) as sum_b, SUM(smu_crc) as sum_c, SUM(smu_crd) as sum_d, SUM(smu_crj) as sum_j FROM smu_cal WHERE date(smu_date)>='2015-10-07' AND date(smu_date)<='2015-10-13' SELECT SUM(smu_cra) as sum_a, SUM(smu_crb) as sum_b, SUM(smu_crc) as sum_c, SUM(smu_crd) as sum_d, SUM(smu_crj) as sum_j FROM smu_cal WHERE date(smu_date)>='2015-09-30' AND date(smu_date)<='2015-10-06'
Date :
2015-10-28 14:07:46
By :
bondsanti
INSERT INTO `smu_cal` (`smu_id`, `smu_date`, `smu_cra`, `smu_crb`, `smu_crc`, `smu_crd`, `smu_crj`)
Code (PHP)
$enDate = '2015-10-26';
$sql = 'select
sum( dte between DATE_SUB('$stDate', interval 6 day) and '$stDate' ) curweek,
sum( dte between DATE_SUB('$stDate', interval 13 day) and DATE_SUB('$stDate', interval 7 day)) retro1week,
sum( dte between DATE_SUB('$stDate', interval 20 day) and DATE_SUB('$stDate', interval 14 day)) retro2week,
sum( dte between DATE_SUB('$stDate', interval 27 day) and DATE_SUB('$stDate', interval 21 day)) retro3week
from table where dte >Date_Sub('$enDate', interval 28 day) ";
เป็นตัวอย่าง การ count record
อ่านโจทย์ผิดครับ ขอโทษที
Code (PHP)
$date='2015-10-26';
$sql="
set @dte='$date';
select nm, st, en,
sum(smu_cra) cra, sum(smu_crb) crb, sum(smu_crc) crc, sum(smu_crd) crd ,sum(smu_crj) crj
from smu_cal tb1
inner join (
select 'curweek' nm, DATE_SUB(@dte, interval 6 day) st, @dte en union all
select 'retro1week', DATE_SUB(@dte, interval 13 day), DATE_SUB(@dte, interval 7 day) union all
select 'retro2week', DATE_SUB(@dte, interval 20 day), DATE_SUB(@dte, interval 14 day) union all
select 'retro3week', DATE_SUB(@dte, interval 27 day), DATE_SUB(@dte, interval 21 day)
) tb2
on tb1.smu_date between tb2.st and tb2.en
group by tb2.nm
";
เสร็จแล้วใช้ php แสดงรายการตามต้องการ
Date :
2015-10-28 15:25:11
By :
NewbiePHP
ลองเอาไปรัน ใน phpmyadmin ก่อน
Date :
2015-10-28 16:23:31
By :
NewbiePHP
Code (SQL)
set @dte='2015-10-26';
select nm, st, en,
sum(smu_cra) cra, sum(smu_crb) crb, sum(smu_crc) crc, sum(smu_crd) crd ,sum(smu_crj) crj
from smu_cal tb1
inner join (
select 'curweek' nm, DATE_SUB(@dte, interval 6 day) st, @dte en union all
select 'retro1week', DATE_SUB(@dte, interval 13 day), DATE_SUB(@dte, interval 7 day) union all
select 'retro2week', DATE_SUB(@dte, interval 20 day), DATE_SUB(@dte, interval 14 day) union all
select 'retro3week', DATE_SUB(@dte, interval 27 day), DATE_SUB(@dte, interval 21 day)
) tb2
on tb1.smu_date between tb2.st and tb2.en
group by tb2.nm
เอาแบบนี้ copy ไป paste ก่อน ไม่ต้องแก้อะไร
Date :
2015-10-28 16:32:06
By :
NewbiePHP
Load balance : Server 00