CODEWITHSUNDEEP
mysqltime
Renai
Renai

Reputation: 195

mysql BETWEEN 2 time(s)

I've been nosing the forum and found posts similar but not working for me, so here goes.

I am trying 2 things, firstly: I have created a checkbox which when selected will show the results of shops open. shop_open and shop_close are my columns in use here which are of time data type (07:00:00 for example). When the user selects and submits the form with checkbox ticked, I want something like CURTIME() to check the current time, hour and minute only, and then see if the current hour:minute is between the shop_open and shop_close times, which should display shops which are open at the time submitted.

The second option was to have 2 input boxes to enter open and/or close times to see if the data matches similar to above. The first option sounds better to me, fiddling with the 2 options has resulted in steps backwards.

Here is my query:

"SELECT shops.*, services.*, ratings.*, temp.total as final_total, CONCAT(shop_number, ' ', shop_location, ' ', shop_city, ' ', shop_province, ' ', shop_postcode) AS address FROM shops 
 inner join services on shops.shop_id=services.shop_id
 inner join ratings on shops.shop_id=ratings.shop_id
 inner join (select SUM(comfort + service + ambience + friendliness + spacious + experience )/(6) / COUNT(shop_id) AS total, shop_id FROM ratings GROUP BY shop_id ) as temp on shops.shop_id=temp.shop_id HAVING temp.total >= '$search_total_rating'
 AND CURTIME() BETWEEN shop_open AND shop_close
 AND address LIKE '%$search_shop_address%' AND shop_name LIKE '%$search_shop_name%' ORDER BY shop_name ASC

I am aware this is messy and complex query but it works great minus this issue. The code you suggested, I was already using but in a different circumstance because of my messy complex query, i imagine i've put it in the wrong place:s

I am not tring to use '$search_open_now' BETWEEN shop_open AND shop_close instead of getting the current time and it returns the wrong results :(

Upvotes: 1

Views: 4026

Answers (2)

Joe
Joe

Reputation: 15802

SELECT field_names FROM table_name WHERE CONCAT(DATE_FORMAT(NOW(), '%H:%i'), ':00') BETWEEN shop_open AND shop_close

Should ignore the seconds for you.

Update: Something like this seems to work for me, moving the WHERE before the HAVING clause

SELECT shops.*, services.*, ratings.*, temp.total as final_total, CONCAT(shop_number, ' ', shop_location, ' ', shop_city, ' ', shop_province, ' ', shop_postcode) AS address FROM shops 
inner join services on shops.shop_id=services.shop_id
inner join ratings on shops.shop_id=ratings.shop_id
inner join (select SUM(comfort + service + ambience + friendliness + spacious + experience )/(6) / COUNT(shop_id) AS total, shop_id FROM ratings GROUP BY shop_id ) as temp on shops.shop_id=temp.shop_id
WHERE CONCAT(DATE_FORMAT(NOW(), '%H:%i'), ':00') BETWEEN shop_open AND shop_close
HAVING temp.total >= '$search_total_rating'
AND address LIKE '%$search_shop_address%' AND shop_name LIKE '%$search_shop_name%' ORDER BY shop_name ASC

Upvotes: 2

Jon Bright
Jon Bright

Reputation: 13738

Something like this will do what I think you want:

SELECT * FROM yourtable WHERE CURTIME() BETWEEN shop_open AND shop_close;

Upvotes: 4

Related Questions