SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Member
    Join Date
    Mar 2012
    Posts
    1
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Determine if the store is Open or Close?

    In PHP and MySQL - how to determine if the Store is Open or Close (return true or false)?

    Also how to get the next opening hours if the store is closed?

    Example of Opening_Hours table:

    Code SQL:
        +----+---------+----------+-----------+------------+---------+
        | id | shop_id | week_day | open_hour | close_hour | enabled |
        +----+---------+----------+-----------+------------+---------+
        |  1 |       1 |        1 | 16:30:00  | 23:30:00   |       1 |
        |  2 |       1 |        2 | 16:30:00  | 23:30:00   |       1 |
        |  3 |       1 |        3 | 16:30:00  | 23:30:00   |       0 |
        |  4 |       1 |        4 | 16:30:00  | 23:30:00   |       1 |
        |  5 |       1 |        5 | 10:00:00  | 13:00:00   |       1 |
        |  6 |       1 |        5 | 17:15:00  | 00:30:00   |       1 |
        |  7 |       1 |        6 | 17:15:00  | 01:30:00   |       1 |
        |  8 |       1 |        7 | 16:30:00  | 23:30:00   |       0 |
        +----+---------+----------+-----------+------------+---------+

    The open_hour and close_hour are TIME type fields. Is the table design ok?

    If the current times are:

    - Current time: Tue 23:00
    - Output: Status - Open (Open at Tue 16:30 - 23:30)


    - Current time: Tue 23:40
    - Output: Status - Close (Open at Thur 16:30 - 23:30)

    Open on Thursday because Opening_Hours.week_day = 3 is disabled


    Now how to handle the midnight time? This get more complicated.

    As you can see, on Saturday (Opening_Hours.week_day = 5), it is open from 17:15 PM to 01:30 (closed next day Sunday)

    If the current time is Sunday 01:15 AM, then the store would still be open base on Opening_Hours.week_day = 5

    - Output: Status - Open (Open at Sat 17:15 - 01:30)

  2. #2
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    What have you got so far?

    Have you at least got a query which turns PHPs date('N') and date('H:i:s') into a valid query which can at least detect todays day number and present (server) time?

    If so, show it ... if not, try and create it....

  3. #3
    SitePoint Addict
    Join Date
    Dec 2005
    Posts
    336
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Code PHP:
    <?php 
     
    $sql = "
    	SELECT
    		  CASE 
    			WHEN TIME(NOW()) BETWEEN open_hour AND close_hour
    				THEN 'Open'
    			ELSE 'Closed'
    		  END AS 'Open/Closed'
    		, null AS WeekDay
    		, null AS OpenHour
    		, null AS CloseHour	  
    	FROM Opening_Hours
    	WHERE DAYOFWEEK(NOW()) = week_day
     
    	UNION ALL
     
    	SELECT
    		  null
    		, N.week_day
    		, N.open_hour
    		, N.close_hour 
    	FROM Opening_Hours AS N
    	WHERE N.week_day > DAYOFWEEK(NOW())
    		AND N.enabled = 1
    	LIMIT 2
    ";
    $output = array();
    foreach ( db()->query($sql) as $r ) {
    	$output[] = $r;
    }
    echo 'Status - '.$output[0]['Open/Closed'].' (Open at '.yourDateFunction($output[1]['WeekDay']).' '.$output[1]['OpenHour'].'  to '.$output[1]['CloseHour'].')';
    Slightly tested...

    Now how to handle the midnight time? This get more complicated.

    As you can see, on Saturday (Opening_Hours.week_day = 5), it is open from 17:15 PM to 01:30 (closed next day Sunday)

    If the current time is Sunday 01:15 AM, then the store would still be open base on Opening_Hours.week_day = 5

    - Output: Status - Open (Open at Sat 17:15 - 01:30)
    For this, I would split it up in the database - Saturday 17:15 - 24:00, then Sunday 00:00 - 01:30 since technically you are open on Sunday, only during those hours.

  4. #4
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    9,031
    Mentioned
    152 Post(s)
    Tagged
    2 Thread(s)
    That second select needs an ORDER BY. Think about it

    Quote Originally Posted by centered effect View Post
    For this, I would split it up in the database - Saturday 17:15 - 24:00, then Sunday 00:00 - 01:30 since technically you are open on Sunday, only during those hours.
    Yes, that's what I would do as well -- just makes the problem so much easier
    Rémon - Hosting Advisor

    Minimal Bookmarks Tree
    My Google Chrome extension: browsing bookmarks made easy

  5. #5
    SitePoint Addict
    Join Date
    Dec 2005
    Posts
    336
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by ScallioXTX View Post
    That second select needs an ORDER BY. Think about it
    Thanks, I did mention that I slightly tested it out...


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •