So, in my mysql database I am storing days and hours open in one field, called days, the data is stored in the following format:
[Monday:9:17[Tuesday:9:17[Wednesday:10:18[
As you may've guessed, it goes: [Day:From:Till and brackets are just seperatars for PHP to distinguish how many days are there.
I've been thinking all day what query would be but I could not figure out, so basically I need to get current date and time using PHP:
date(l); // Day in full text representation.
date(G); // current hour in 24 hour format.
So basically I need a query which in simple english would sound like:
SELECT all FROM businessdetails WHERE column date CONTAINS [current date] and :#:# numbers to be less than current hour and greater than current hour.
Help? My brain is melting by now.
So honestly the best thing to do is to normalize your database so you can do better queries. BUT I love to see if I can solve impossible situations so here is what you can do!
This will check all the business that are open on Tuesday at 11am
SELECT * FROM `businessdetails` WHERE `date` REGEXP 'Tuesday:(0|1|2|3|4|5|6|7|8|9|10|11):(11|12|13|14|15|16|17|18|19|20|21|22|23)[^0-9]'
(Funny thing I've found I can't seem to escape the [ in the column so I had to make sure the Regex doesn't have any extra digits at the end or it may erroneously match 2 and 20 or something.)
Here's how you can generate that REGEXP string via PHP:
<?php
$regexp = date('l') . ':(' . join('|', range(0, date('G'))) . '):(' . join('|', range(date('G'), 23)) . ')[^0-9]';
DISCLAIMER I don't actually recommend doing this but I thought it was clever and wanted to share since it directly answers your question.