Find open and closed status using mysql php

Question

I've restaurant & restaurant_time table. I need to find the restaurant opening status using these below queries:

SELECT rest.resid, rest.resname, restime.tue_ot, restime.tue_ct,
     IF( (  (restime.tue_status = 'Open') AND 
            (CURTIME() BETWEEN restime.tue_ot AND restime.tue_ct, 'Open','Closed') = 'Open' ), 'Open', 'Closed') AS final_status
     FROM restaurant AS rest 
     LEFT JOIN restaurant_time AS restime ON rest.resid = restime.res_id 
     WHERE rest.restaurant_status = '1'

I'm getting the mysql error like below.

1241 - Operand should contain 3 column(s)

I need the output below like this.

resid   resname tue_status  tue_ot      tue_ct      final_status
1       aaaa    Open        08:00:00    23:00:00    Open
2       bbbb    Closed      21:00:00    23:00:00    Closed
3       cccc    Open        21:00:00    23:00:00    Closed
4       ddddd   Closed      08:00:00    23:00:00    Closed

db structure is below...

CREATE TABLE IF NOT EXISTS `restaurant` (
  `resid` int(11) NOT NULL AUTO_INCREMENT,
  `resname` varchar(10) NOT NULL,
  `restaurant_status` enum('0','1') NOT NULL,
  PRIMARY KEY (`resid`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;

--
-- Dumping data for table `restaurant`
--

INSERT INTO `restaurant` (`resid`, `resname`, `restaurant_status`) VALUES
(1, 'aaaa', '1'),
(2, 'bbbbb', '1'),
(3, 'ccccc', '1'),
(4, 'dddddd', '1');


-- Table structure for table `restaurant_time`


CREATE TABLE IF NOT EXISTS `restaurant_time` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `res_id` int(11) NOT NULL,
  `tue_status` enum('Open','Closed') NOT NULL DEFAULT 'Open',
  `tue_ot` time NOT NULL,
  `tue_ct` time NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;

--Dumping data for table `restaurant_time`
INSERT INTO `restaurant_time` (`id`, `res_id`, `tue_status`, `tue_ot`, `tue_ct`) VALUES
(1, 1, 'Open', '08:00:00', '23:00:00'),
(2, 2, 'Closed', '21:00:00', '23:00:00'),
(3, 3, 'Open', '21:00:00', '23:00:00'),
(4, 4, 'Closed', '08:00:00', '23:00:00');

why you don't get the parameter curtime() in the WHERE clause request SQL ? — miltone, Jan 05 '16 at 11:46
Could you add your db structure, it would be helpful. Otherwise check if removing the opening and closing braces helps at certain places-> see [this](http://stackoverflow.com/q/15820288/3664960). — davejal, Jan 05 '16 at 11:51
What are you expecting of `(CURTIME() BETWEEN restime.tue_ot AND restime.tue_ct, 'Open','Closed') = 'Open' )`, I don't understand that part. — davejal, Jan 05 '16 at 11:55
@devejal, We need to check with restime.tue_status & also (CURTIME() BETWEEN restime.tue_ot AND restime.tue_ct, 'Open','Closed') = 'Open' ) condition result. We need to check both status. If anyone status is closed then restaurant is closed. If both status is open then restaurant is opened.. — mikejohnvino, Jan 05 '16 at 12:18

score 1 · Answer 1 · edited Jan 05 '16 at 12:24

1

Below query will solve your issue

SELECT rest.resid, rest.resname, restime.`tue_status`, restime.tue_ot, restime.tue_ct, 
 CASE 
  WHEN (CURTIME() BETWEEN restime.tue_ot AND restime.tue_ct AND restime.`tue_status` = 'Open') 
  THEN 'Open' ELSE 'CLOSED' 
 END as final_status 
FROM restaurant as rest 
JOIN  `restaurant_time` as restime ON rest.resid = restime.`res_id`

edited Jan 05 '16 at 12:24

davejal

6,009
10
39
82

answered Jan 05 '16 at 11:59

Anto S

2,448
6
32
50

We need to check with restime.`tue_status` & also your case condition result. We need to check both status. If anyone status is closed then restaurant is closed. If both status is open then restaurant is opened.. – mikejohnvino Jan 05 '16 at 12:15
@mikejohnvino with your db structure also my query works fine. – Anto S Jan 05 '16 at 12:21
@mikejohnvino if this query works fine, accept my answer – Anto S Jan 20 '16 at 08:35

davejal · Answer 2 · 2016-01-05T12:28:47.347

As I already commented you don't have any function in this part:

(CURTIME() BETWEEN restime.tue_ot AND restime.tue_ct, 'Open','Closed') = 'Open' ),

you should change it to something like this (also Proposed by @anto.nishanth):

SELECT rest.resid, rest.resname, restime.`tue_status`, date_format(restime.tue_ot,'%h:%i'), date_format(restime.tue_ct,'%h:%i'),
 CASE 
  WHEN (CURTIME() BETWEEN restime.tue_ot AND restime.tue_ct AND restime.`tue_status` = 'Open') 
  THEN 'Open' ELSE 'CLOSED' 
 END as final_status 
FROM restaurant as rest 
JOIN  `restaurant_time` as restime ON rest.resid = restime.`res_id`

Try out this sqlfiddle for reference

Find open and closed status using mysql php

2 Answers2