iT邦幫忙

1

[PHP+MySQL] 月搜尋

想請問一下
我有一資料表message如下
https://ithelp.ithome.com.tw/upload/images/20200704/20125113NaPu8gFyRw.png
想用"月"搜尋time欄位內的符合的資料
$start_time = date("Y-m",strtotime('-2 month'));
$end_time = date("Y-m",strtotime('-1 month'));
$sql=mysql_query("select distinct title from message where time between '".$start_time."' and '".$end_time."' and unit '403' group order by title"); while($row=mysql_fenth_array($sql)){ echo "$row[title]"; }
但沒有任何輸出,該如何處理呢?

P.S用"天"搜尋time欄位可正常輸出

1
海綿寶寶
iT邦大神 1 級 ‧ 2020-07-05 09:42:30
最佳解答

把 Mysql 指令 echo 出來 +1

試試

$sql=mysql_query("select distinct title from message where time between '".$start_time."' and '".$end_time."' and unit='403' order by title"); 

2020/07/05 11:39 更新
再試試看

$sql=mysql_query("select distinct title from message where date_format(time,'%Y-%m') between '".$start_time."' and '".$end_time."' and unit='403' order by title");

以下是我的測試 script



CREATE TABLE message
    (`time` timestamp, `title` varchar(7), `unit` int, `message` varchar(4))
;
    
INSERT INTO message
    (`time`, `title`, `unit`, `message`)
VALUES
    ('2020-05-16 00:00:00', 'title01', 402, 'test'),
    ('2020-05-19 00:00:00', 'title02', 403, 'test'),
    ('2020-06-01 00:00:00', 'BPM', 403, 'test'),
    ('2020-06-12 00:00:00', '123', 302, 'test'),
    ('2020-06-19 00:00:00', 'title03', 201, 'test'),
    ('2020-04-18 00:00:00', 'title04', 306, '123'),
    ('2020-04-11 00:00:00', 'title05', 409, 'test'),
    ('2020-02-03 00:00:00', 'title06', 203, 'test')
;

select distinct title 
from message 
where time between '2020-05-01' and '2020-06-30' 
  and unit='403' 
order by title;

select distinct title 
from message 
where date_format(time,'%Y-%m') between '2020-05' and '2020-06'
  and unit='403' 
order by title;
看更多先前的回應...收起先前的回應...
特倫斯 iT邦新手 5 級 ‧ 2020-07-05 10:56:15 檢舉

https://ithelp.ithome.com.tw/upload/images/20200705/20125113e1oUUeeqiI.png
有的~我先用"天"搜尋

$start_time = date("Y-m-d",strtotime('-2 month'));
$end_time = date("Y-m-d",strtotime('-1 month'));
$sql = mysql_query("select distinct title from message2 where time between '".$start_time."' and '".$end_time."' and unit ='403' order by title");
while($list = mysql_fetch_array($sql)){
print_r($list[title]); 
}

可得到ABC,但用"月"搜尋

$start_time = date("Y-m",strtotime('-2 month'));
$end_time = date("Y-m",strtotime('-1 month'));
$sql = mysql_query("select distinct title from message2 where time between '".$start_time."' and '".$end_time."' and unit ='403' order by title");
while($list = mysql_fetch_array($sql)){
print_r($list[title]); 
}

沒有輸出到值

time 欄位裡是完整的日期/時間
如果你只用 Y-m (年月)去比較
自然不會落在 between 的區間中

特倫斯 iT邦新手 5 級 ‧ 2020-07-05 11:49:51 檢舉

大大有好的建議嗎?我想到的只有土法煉鋼-再多一欄位(time1),在每次新增一筆資料時紀錄"年月"
再用sql time1 between

我的回答有新答案,請參考
....不要用 time1 這個點子

特倫斯 iT邦新手 5 級 ‧ 2020-07-05 14:43:33 檢舉

感謝大大幫忙,這方式讓我回想到上次的發文-浩瀚大提到的date_format
這裡

3
rogeryao
iT邦高手 1 級 ‧ 2020-07-04 22:45:01

1.Php 執行 Mysql 前建議把 Mysql 指令 echo 出來
2.常用SQL語法教學

特倫斯 iT邦新手 5 級 ‧ 2020-07-05 14:45:45 檢舉

感謝大大分享

1
一級屠豬士
iT邦大師 1 級 ‧ 2020-07-05 15:26:17

接著海綿寶寶以及rogeryao , 我另外做了些資料,提供參考.


create table it0705msg (
  ts timestamp not null
, title varchar(7) not null
, unit int not null
, msg varchar(4) not null
);

insert into it0705msg
VALUES
    ('2020-05-16 00:00:00', 'title01', 402, 'test'),
    ('2020-05-19 00:00:00', 'title02', 403, 'test'),
    ('2020-06-01 00:00:00', 'BPM', 403, 'test'),
    ('2020-06-12 00:00:00', '123', 302, 'test'),
    ('2020-06-19 00:00:00', 'title03', 201, 'test'),
    ('2020-04-18 00:00:00', 'title04', 306, '123'),
    ('2020-04-11 00:00:00', 'title05', 409, 'test'),
    ('2020-02-03 00:00:00', 'title06', 203, 'test')
;

insert into it0705msg values
('2020-05-31 11:59:59', '測邊界', 200, 'test');

insert into it0705msg values
('2020-05-31 23:59:59', '測邊界2', 400, 'test');

-- 假設要選2020年5月區間的 , 我們可以產生2020年5月的開始的 timestamp
-- 先產生以下幾種格式做測試觀察用
select timestamp '2020-05-01 00:00:00' as start_ts
     , date_add('2020-05-01', interval 1 month) as end_date
     , convert(date_add('2020-05-01', interval 1 month), datetime) end_dt;

+---------------------+------------+---------------------+
| start_ts            | end_date   | end_dt              |
+---------------------+------------+---------------------+
| 2020-05-01 00:00:00 | 2020-06-01 | 2020-06-01 00:00:00 |
+---------------------+------------+---------------------+
1 row in set (0.00 sec)

select *
  from it0705msg
 where ts between timestamp '2020-05-01 00:00:00'
   and convert(date_add('2020-05-01', interval 1 month), datetime);

+---------------------+------------+------+------+
| ts                  | title      | unit | msg  |
+---------------------+------------+------+------+
| 2020-05-16 00:00:00 | title01    |  402 | test |
| 2020-05-19 00:00:00 | title02    |  403 | test |
| 2020-06-01 00:00:00 | BPM        |  403 | test |
| 2020-05-31 11:59:59 | 測邊界     |  200 | test |
| 2020-05-31 23:59:59 | 測邊界2    |  400 | test |
+---------------------+------------+------+------+
5 rows in set (0.00 sec)


-- 這裡有觀察到 2020-06-01 00:00:00 的資料出現了.

-- 我們可以利用另外的函數 last_day(date) 來獲得該月的最後一天.然後再人工補 23:59:59
-- 或者是 add_month()結果 的減一秒.

select date_sub(date_add('2020-05-01', interval 1 month), interval 1 second) as end_dt;

+---------------------+
| end_dt              |
+---------------------+
| 2020-05-31 23:59:59 |
+---------------------+

select *
  from it0705msg
 where ts between timestamp '2020-05-01 00:00:00'
   and date_sub(date_add('2020-05-01', interval 1 month), interval 1 second);
+---------------------+------------+------+------+
| ts                  | title      | unit | msg  |
+---------------------+------------+------+------+
| 2020-05-16 00:00:00 | title01    |  402 | test |
| 2020-05-19 00:00:00 | title02    |  403 | test |
| 2020-05-31 11:59:59 | 測邊界     |  200 | test |
| 2020-05-31 23:59:59 | 測邊界2    |  400 | test |
+---------------------+------------+------+------+
4 rows in set (0.00 sec)

-- 其實使用 datetime 格式 就比較方便啦.範圍也比較廣.
https://dev.mysql.com/doc/refman/8.0/en/datetime.html
 the range for DATETIME values is '1000-01-01 00:00:00.000000' to '9999-12-31 23:59:59.999999', and the range for TIMESTAMP values is '1970-01-01 00:00:01.000000' to '2038-01-19 03:14:07.999999'. 
The TIMESTAMP data type is used for values that contain both date and time parts. TIMESTAMP has a range of '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC. 

我要發表回答

立即登入回答