如何将PHP中的两个值与mysql数据库中的两列进行比较？ [英] How to compare two values from PHP with two columns in mysql database?
问题描述
我在MySQL数据库中有两个不同的列，例如：start_hour和end_hour在一个名为books的表名中
当我插入新的时PHP中的值（新记录）到Books表中的start_hour和end_hour列，我需要检查两列之间是否存在两个新值，如果两个列之间存在任何新值列值然后无法向表中添加新记录。
我已经尝试了以下查询来检查现有记录，但似乎无法按要求工作。 />
测试链接： SQL Fiddle [ ^ ]
表结构：
I have two different columns in MySQL database such as: "start_hour" and "end_hour" within a table name called: "Books"
When I insert new value (new record) from PHP to "start_hour" and "end_hour" columns in the Books table, I need to check if the two new values was exists in between the two columns, if any of the new value was exists in between the two columns value then can't add new record to the table.
I've tried the follow query to check for existing record but seem not work as required.
Link for testing: SQL Fiddle[^]
Table structure:
CREATE TABLE Books
(`id` int, `book_id` int, `start_hour` varchar(11), `end_hour` varchar(11))
;
INSERT INTO Books
(`id`, `book_id`, `start_hour`, `end_hour`)
VALUES
(1, 2, '6:00', '10:00'),
(2, 2, '11:00', '12:00'),
(3, 1, '13:00', '19:00'),
(4, 1, '20:00', '22:00')
;
SELECT * FROM Books WHERE ('6:00' >= start_hour OR '12:00' <= end_hour) AND book_id = 2
示例：
1.案例1：如果存在记录start_hour为'6 ：00'和end_hour是'12：00'然后如果start_hour的新记录是7:00而end_hour是11:00那么7和11都是6-12之间那么意思是不能预订
2.案例2：如果存在记录start_hour是'6:00'而end_hour是'12：00'那么如果start_hour的新记录是7:00而end_hour是17:00。这个案子仍然无法预订。
3.案例3：start_hour和end_hour都应该超出现有记录范围才能出书。
所以请帮助建议有没有办法比较这两个新值，如果已经单独存在于表中。
提前谢谢。
Example:
1. Case 1: If exists record start_hour is '6:00' and end_hour is '12:00' then if the new record of start_hour is 7:00 and end_hour is 11:00 then 7 and 11 is both between 6 - 12 so that mean can't book
2. Case 2: If exists record start_hour is '6:00' and end_hour is '12:00' then if the new record of start_hour is 7:00 and end_hour is 17:00. This case still can't book.
3. Case 3: Both start_hour and end_hour should be out of existing record range in order to make a book.
So please kindly help to suggestion is there any way on comparing the two new values if already exists in the table individually.
Thanks in advance.
推荐答案
如果我理解正确的话，试试这个：
If I understand you correctly, try this:
SELECT * FROM books WHERE (start_hour >= '6:00' OR end_hour <= '12:00') AND book_id=2
你需要一个'OR'作为start_hour，end_hour可以跨越多个行，并使用book_id条件在'AND'之前的括号中设置'OR'条件。
+++++ [round 2] +++++
将字符串参数转换为时间，然后使用 STR_TO_DATE（）函数 [ ^ ]
You need an 'OR' as the start_hour and end_hour can span more than one row, and set the 'OR' condition in brackets before 'AND' with the book_id condition.
+++++[round 2]+++++
Convert the string parameters to time, then compare using STR_TO_DATE() function[^]
SELECT * FROM books WHERE (STR_TO_DATE(start_hour, '%h:%m') >= STR_TO_DATE('13:00', '%h:%m')
OR STR_TO_DATE(end_hour, '%h:%m') <= STR_TO_DATE('22:00', '%h:%m'))
AND book_id=2
希望这次有用。
最后但并非最不重要的是，你应该认真考虑使用最适合其预期目的的数据类型，即 MySQL :: MySQL 5.7参考手册:: 11。3日期和时间类型 [ ^ ]
说实话，我不太明白为什么你只处理时间而不考虑日期。但无论如何，我建议使用时间或日期时间数据类型。这使得计算比使用字符串更容易。
例如，对于time数据类型，您的声明和查询可能类似于以下内容。我们的想法是查询搜索预留，因此如果它返回一行或几行，则该书不可用。如果它没有返回任何行，则该书可用。
请考虑以下示例：
To be honest I don't quite understand why you handle only times and don't take dates into account. But regardless of that, I would suggest using either time or datetime data type. This makes calculation a lot easier than playing with strings.
For example with time datatype your declarations and queries could look something like the following. The idea is that the query searches for reservations so if it returns a row or several rows, the book isn't available. If it returns no rows, the book is available.
Consider the following examples:
CREATE TABLE Books2 (
id int,
book_id int,
starttime time,
endtime time)
;
INSERT INTO Books2 (id, book_id, starttime, endtime)
VALUES
(1, 2, convert(time, '1900-01-01 06:00:00.000', 121), convert(time, '1900-01-01 10:00:00.000', 121)),
(2, 2, convert(time, '1900-01-01 11:00:00.000', 121), convert(time, '1900-01-01 12:00:00.000', 121)),
(3, 1, convert(time, '1900-01-01 13:00:00.000', 121), convert(time, '1900-01-01 19:00:00.000', 121)),
(4, 1, convert(time, '1900-01-01 20:00:00.000', 121), convert(time, '1900-01-01 22:00:00.000', 121))
;
-- test 1, fail
DECLARE @teststart varchar(100) = '1900-01-01 07:00:00.000';
DECLARE @testend varchar(100) = '1900-01-01 11:00:00.000';
-- test 2, fail
--DECLARE @teststart varchar(100) = '1900-01-01 07:00:00.000';
--DECLARE @testend varchar(100) = '1900-01-01 17:00:00.000';
-- test 3, fail
--DECLARE @teststart varchar(100) = '1900-01-01 07:00:00.000';
--DECLARE @testend varchar(100) = '1900-01-01 10:00:00.000';
-- test 4, fail
--DECLARE @teststart varchar(100) = '1900-01-01 05:00:00.000';
--DECLARE @testend varchar(100) = '1900-01-01 17:00:00.000';
-- test 5, succeed
--DECLARE @teststart varchar(100) = '1900-01-01 05:00:00.000';
--DECLARE @testend varchar(100) = '1900-01-01 06:00:00.000';
-- test 6, succeed
--DECLARE @teststart varchar(100) = '1900-01-01 14:00:00.000';
--DECLARE @testend varchar(100) = '1900-01-01 17:00:00.000';
SELECT * -- row found, book not available, row not found, book available
FROM Books2
WHERE book_id = 2
AND (
( convert(time, @teststart, 121) <= starttime
AND convert(time, @testend, 121) > starttime) -- test time ends within non-allowed time
OR ( convert(time, @teststart, 121) < endtime
AND convert(time, @testend, 121) >= endtime) -- test time starts within non-allowed time
OR ( convert(time, @teststart, 121) < endtime
AND convert(time, @testend, 121) >= endtime)
OR ( ( convert(time, @teststart, 121) > starttime and convert(time, @teststart, 121) < endtime )
OR ( convert(time, @testend, 121) > starttime and convert(time, @testend, 121) < endtime ) ) -- test time is within non-allowed time
);
这篇关于如何将PHP中的两个值与mysql数据库中的两列进行比较？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
