【SQL】日時範囲指定して取得する方法

実現したいこと

例えば
データベースのカラム「start_time」と「end_time」の値が
2022-03-1411:00〜2022-03-1411:15の間にあれば
3/14の11:00の所を「▲」と表示したいです。

エラーメッセージを見るとbetweenの書き方が間違えているのかな？と思うのですが、、解決できません。アドバイスをいただけると助かります。よろしくお願いします。

foreach ( $daterange as $date ) を使い今日の日付と時間11:00から23:00を15分間隔で下記のように値を取得しています。

2022-03-1411:00
2022-03-1411:15
：
：
2022-03-1422:45
2022-03-1423:00

データベース

テーブル名「customer」
カラム名「start_time」「end_time」「shop_id」

「start_time」と「end_time」には2021-09-1711:00のような形で日時が入っています。

発生している問題・エラーメッセージ

Fatal error: Uncaught PDOException: SQLSTATE[42000]: Syntax error or access violation: 1064 
You have an error in your SQL syntax; check the manual that corresponds to your MySQL 
server version for the right syntax to use near ':00 and 2022-03-1411:15' at line 1 in /home/●/●:67
 Stack trace: #0 /home/●/●(67): PDO->query('SELECT * FROM c...') 
#1 /home/●/●(264): include('/home/●...') #2 {main} thrown in /home/● on line 67

該当のソースコード

PHP
1<?php
2require_once( '../../core/config.php' );
3try {
4
5  // PDOインスタンスを生成
6  $dbh = new PDO( DSN, DB_USER, DB_PASS );
7  $dbh->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );
8
9} catch ( PDOException $e ) {
10
11  // エラーメッセージを表示させる
12  echo 'データベースにアクセスできません！' . $e->getMessage();
13
14  // 強制終了
15  exit;
16
17}
18$sql = 'SELECT * FROM customer WHERE shop_id = ' . $shop_id;
19
20?>
21<!--※※【共通部分】日時選択table※※-->
22<table>
23<th class="no">時間</th>
24<th> <?php echo $todayFmt1; ?>（<?php echo $week[ $todayWeek ];?>）<br>
25<?php
26if ( in_array( $week[ $todayWeek ], $closed ) ) {
27  echo "<span>定休日</span>";
28} elseif ( in_array( $today, $closedDay ) ) {
29  echo "<span>休業日</span>";
30} else {
31  echo "&nbsp;";
32}
33?></th>
34<?php
35date_default_timezone_set( 'Asia/Tokyo' );
36//日付を取得(今日・明日・明後日）
37$todayFmt1 = date('m/d');
38$today = date('Y-m-d');
39//曜日を取得(今日・明日・明後日）
40$todayWeek = date('w');
41$week = [
42  '日', //0
43  '月', //1
44  '火', //2
45  '水', //3
46  '木', //4
47  '金', //5
48  '土', //6
49];
50//予約開始時間設定(時)
51$start = "11:00";
52//受付終了時間設定(時)
53$end = "23:10";
54//時間間隔（分）
55$timeFrame = "15";
56//予約table時間変数設定
57$begin = new DateTime($start);
58$end = new DateTime($end);
59$interval = new DateInterval('PT'.$timeFrame.'M');
60$daterange = new DatePeriod($begin, $interval, $end->modify('+1 min'));
61
62foreach ( $daterange as $date ) {
63  $time = $date->format( "H:i" );
64  //日時（今日）スペースあり
65  $variable = $today . $time;
66  $variablePlus = date( "Y-m-dH:i", strtotime( $variable . "+15 minute" ) ); //15分後
67  $variableSplit = str_split( $variable, 10 );
68  $result1 = implode( ' ', $variableSplit );
69
70?>
71<tr class='day_table'>
72<td class="time"><?php echo $time; ?></td>
73<!--今日-->
74<td><input type='radio' name='day' id='<?php echo $today.$time; ?>' value='<?php echo $today.$time; ?>' 
75<?php
76if(in_array( $week[ $todayWeek ], $closed ) ) {
77echo 'disabled';
78}elseif(in_array( $today, $closedDay )){
79echo 'disabled';
80}elseif ( in_array( $result1, $breakTime ) ) {
81 echo 'disabled';
82}elseif ( strtotime( date( 'H:i' ) ) > strtotime( $time ) ) {
83  echo 'disabled';
84}elseif($time <= $HalfAnHour ){
85echo 'disabled';
86}
87?>>
88<label for='<?php echo $today.$time;?>'>
89<?php
90　//$sql = $sql . " AND start_time between ".$variable." and ".$variablePlus;
91　//
92　//$prepare = $dbh->query( $sql );
93　//$prepare->execute();
94　//$count = $prepare->rowCount(); //データ数を取得
95　//$dbh = null;
96if ( in_array( $week[ $todayWeek ], $closed ) || in_array( $today, $closedDay ) ) {
97  echo "<i class='fas fa-minus'></i>";
98}  elseif ( strtotime( date( 'H:i' ) ) > strtotime( $time ) ) {
99  echo "<i class='fas fa-times'></i>"; //×
100} elseif ( in_array( $result1, $breakTime ) ) {
101  echo "<i class='fas fa-minus'></i>";
102}elseif($count == 1){
103  echo "▲";
104}elseif ( strtotime( date( 'H:i' ) ) > strtotime( $time ) || $time <= $HalfAnHour ) {
105  echo "TEL";
106} else {
107  echo "<span class='result1'></span>"; //result1は選択可能のアイコン
108}
109?>
110</label></td>
111
112
113</tr>
114<?php
115}
116?>
117</table>
118

試したこと

上記該当のソースコードでコメントアウトをしている部分

PHP
1$sql = $sql . " AND start_time between ".$variable." and ".$variablePlus;
2　
3　$prepare = $dbh->query( $sql );
4　$prepare->execute();
5　$count = $prepare->rowCount(); //データ数を取得
6　$dbh = null;

このコードを追記するとエラーが出ます。

DBテーブルサンプル

CREATE TABLE IF NOT EXISTS customer (
reserve_no int(11) NOT NULL,
start_time varchar(50) NOT NULL,
end_time varchar(50) NOT NULL,
shop_id int(11) NOT NULL,
created varchar(50) NOT NULL
)
INSERT INTO customer (reserve_no, start_time, end_time, shop_id, created) VALUES
(1, '2021-09-1711:00', '2021-09-1712:10', 1, '2021-01-16 14:20'),
(2, '2021-10-1623:45', '2021-10-1701:00', 2, '2021-10-16 14:37'),
(3, '2022-03-0616:00', '2022-03-0617:40', 1, '2022-03-06 11:44'),
(4, '2022-03-0821:15', '2022-03-0822:55', 1, '2022-03-08 18:32'),
(5, '2022-03-1423:00', '2022-03-1423:35', 1, '2022-03-14 16:47');

yambejp

2022/03/14 06:44

想定するデータベースはmysqlでよろしいですね？ 2021-09-1711:00のような形、日付と時間の間にスペースは入らないのでしょうか？ DBのテーブルサンプルを提示されたほうがよいでしょう

emi_ono

2022/03/14 07:11

ありがとうございます。・データベースはmysqlです・日付と時間の間にスペースは入りません・DBのテーブルサンプルを追記しましたよろしくお願いします。

yambejp

2022/03/14 07:12 編集

サンプルデータはcreate table+insert形式で例示してください >日付と時間の間にスペースは入りませんということは文字列型(char/varchar?)で持たせているのでしょうか？ datetime型の方が管理しやすいと思いますが・・・

emi_ono

2022/03/14 07:27

「create table+insert形式」とはどのような形式でしょうか？ DBのテーブルサンプル追記も見よう見まねで追記したのですが、全然分かっていなくて本当に申し訳ないです。「phpMyAdmin」を利用しているのですが、構造の事でしょうか？

yambejp

2022/03/14 07:31

phpMyAdminを利用している場合は、データベースの構造からチェックボックスでテーブルを選択してSQL形式で「エキスポート」すると create table・・・、insert into ・・・という記述がテキストとして吐き出されますそれをコピペしてください

emi_ono

2022/03/14 07:35

>ということは文字列型(char/varchar?)で持たせているのでしょうか？文字列型（varchar）にしていました。 datetime型に変更してみます。

yambejp

2022/03/14 12:23

サンプル見ましたが、そもそも23時までのスケジュールといっているのに、 end_timeを23:35にするのはNGでは？

emi_ono

2022/03/14 13:02

説明が悪くてすみません。 start_timeは23時までなのですが、end_timeは23:35でも大丈夫です。

yambejp

2022/03/14 13:09

PM11時の予約は23:01までか翌10:59までかわからないのですね・・・あまりよい予約システムには見えませんが・・

行動規範の内容に同意します

回答1件

一旦こんな感じで

SQL
1create table tbl( reserve_no int primary key, start_time datetime, end_time datetime, shop_id int, created datetime);
2insert into tbl values
3(1,'2021-09-17 11:00','2021-09-17 12:10',1,'2021-01-16 14:20'),
4(2,'2021-10-16 23:45','2021-10-17 01:00',2,'2021-10-16 14:37'),
5(3,'2022-03-06 16:00','2022-03-06 17:40',1,'2022-03-06 11:44'),
6(4,'2022-03-08 21:15','2022-03-08 22:55',1,'2022-03-08 18:32'),
7(5,'2022-03-14 11:10','2022-03-14 12:20',1,'2022-03-14 00:00'),
8(6,'2022-03-14 14:25','2022-03-14 15:20',1,'2022-03-14 00:00');

3/14の予約状況を表示

SQL
1select t1.dt,case when t2.reserve_no is null then '-' else '▲' end as reserve from (
2SELECT (select @a := @a + interval 15 minute from (select @a:='2022-03-14 10:45') as dummy) AS dt
3FROM information_schema.COLUMNS
4LIMIT 49) as t1
5left join tbl t2 on t1.dt between t2.start_time and t2.end_time
6order by dt;

もしかしたらこうかも

SQL
1select t1.dt,case when t2.reserve_no is null then '-' else '▲' end as reserve from (
2SELECT (select @a := @a + interval 15 minute from (select @a:='2022-03-14 10:45') as dummy) AS dt
3FROM information_schema.COLUMNS
4LIMIT 49) as t1
5left join tbl t2 on t1.dt between t2.start_time and t2.end_time
6or t1.dt + interval 15 minute  between t2.start_time and t2.end_time
7order by dt;