Solved

Retrieve and show store hours

Posted on 2016-07-21
11
66 Views
Last Modified: 2016-07-21
Hi,
I have a table of dealership open hours for the week. I came across a post online on 'best way to show store hours, php, mysql' and in it they show a couple functions etc. to achieve a nicely formatted table of a location's hours. I'm trying to implement this but having trouble.

Example site link

Based off the example site, my table for hours is this (I went Monday (1) to Sunday(7) instead of Monday(0) and Sunday(6) is the only diff). And I required a department field to pull the right hours for the week for that department.

id
dealership_id
department (int 0, 1 or 2 for sales/service/parts)
dow  - day of week from Monday '1' to Sunday '7'
open - (stored as 09:00 as an example)
closed - (stored as 17:00 as example)
optional_text - ('Closed' as an example)

I run a query to get the correct store and departments hours for the week ordered by DOW:

$hours = DB::table('dealership_hours')
        	->select('dow','open','closed','optional_text')
        	->where('department', 0)
        	->where('dealership_id','ab633f68-a57b-f14b-59ba-55e4a2b00044')
        	->orderBy( 'dow', 'ASC') 
        	->get();

Open in new window


result is this:
array(7) { [0]=> object(stdClass)#363 (4) { ["dow"]=> int(1) ["open"]=> string(5) "09:00" ["closed"]=> string(5) "21:00" ["optional_text"]=> NULL } [1]=> object(stdClass)#364 (4) { ["dow"]=> int(2) ["open"]=> string(5) "09:00" ["closed"]=> string(5) "21:00" ["optional_text"]=> NULL } [2]=> object(stdClass)#365 (4) { ["dow"]=> int(3) ["open"]=> string(5) "09:00" ["closed"]=> string(5) "21:00" ["optional_text"]=> NULL } [3]=> object(stdClass)#366 (4) { ["dow"]=> int(4) ["open"]=> string(5) "09:00" ["closed"]=> string(5) "21:00" ["optional_text"]=> NULL } [4]=> object(stdClass)#367 (4) { ["dow"]=> int(5) ["open"]=> string(5) "09:00" ["closed"]=> string(5) "18:00" ["optional_text"]=> NULL } [5]=> object(stdClass)#368 (4) { ["dow"]=> int(6) ["open"]=> string(5) "09:00" ["closed"]=> string(5) "17:00" ["optional_text"]=> NULL } [6]=> object(stdClass)#369 (4) { ["dow"]=> int(7) ["open"]=> string(0) "" ["closed"]=> string(0) "" ["optional_text"]=> string(6) "Closed" } } 

Open in new window


Seems fine, returns as expected.

From the example site he is using a couple functions to make the end result more human readable/nicer format times/group same day hours together etc. using below:
(this is what I can't figure out how to implement. How do I get my results to work in those functions within my controller?)

/**
 * Function to change an opening hour to a more human readable version,
 * e.g., 09:00 to 9am or 13:30 to 1.30pm
 *
 * @param   String  $time  Time to format, in HH:MM format
 *
 * @return  String         Formatted time
 *
 */
function format_opening_hour($time) {
  if ($time == '24:00') {
    $new_time = 'midnight';
  }
  else {
    list($hours, $minutes) = explode(':', $time);
    $hours = ltrim($hours, '0');
    $am_pm = ($hours >= 12) ? 'pm' : 'am';
    if ($hours > 12) $hours -= 12;
    $new_time = $hours;
    if ($minutes != '00') {
      $new_time .= '.'.$minutes;
    }
    $new_time .= $am_pm;
  }
  return $new_time;
}

Open in new window


as well as:

/**
 * Function to generate a simple html table for a business' opening hours
 *
 * @param   Array   $opening_hours    Array of rows from opening_hours table, sorted by dow (0-indexed, starting with Monday)
 * @param   String  $extra_text       Extra block of generic text that applies to all days, goes at end of table
 * @param   String  $short_day_names  Whether to use e.g. 'Mon' or 'Monday'
 *
 * @return  String                    HTML <table> output
 *
 */
function opening_hours_table($opening_hours, $extra_text='', $short_day_names=false) {
  $dow = array(
    array('long' => 'Monday', 'short' => 'Mon'),
    array('long' => 'Tuesday', 'short' => 'Tue'),
    array('long' => 'Wednesday', 'short' => 'Wed'),
    array('long' => 'Thursday', 'short' => 'Thu'),
    array('long' => 'Friday', 'short' => 'Fri'),
    array('long' => 'Saturday', 'short' => 'Sat'),
    array('long' => 'Sunday', 'short' => 'Sun')
  );
  $key = ($short_day_names) ? 'short' : 'long';

  // first, find similar days and group them together
  if (!empty($opening_hours)) {
    $opening_short = array();
    // start with current day
    for ($i=0; $i<7; $i++) {
      $temp = array($i);
      // try to find matching adjacent days
      for ($j=$i+1;$j<7;$j++) {
        if (empty($opening_hours[$i]['optional_text']) &&
            empty($opening_hours[$j]['optional_text']) &&
            $opening_hours[$i]['open'] == $opening_hours[$j]['open'] &&
            $opening_hours[$i]['closed'] == $opening_hours[$j]['closed'] ||
            !empty($opening_hours[$i]['optional_text']) &&
            !empty($opening_hours[$j]['optional_text']) &&
            strtolower($opening_hours[$i]['optional_text']) == strtolower($opening_hours[$j]['optional_text']) ) {
          // we have a match, store the day
          $temp[] = $j;
          if ($j == 6) $i = 6; // edge case
        }
        else {
          // otherwise, move on to the next day
          $i = $j-1;
          $j = 7; // break
        }
      }
      $opening_short[] = $temp; // $temp will be an array of matching days (possibly only 1 day)
    }
  }

  $html = '<table>';
  $colspan = '';

  if (!empty($opening_short)) {
    $colspan = ' colspan="2"';
    foreach ($opening_short as $os) {
      $day_text = $dow[$os[0]][$key];
      if (count($os) > 1) { // if there's another, adjacent day with the same time
        $end = array_pop($os); // get the last one
        $end = $dow[$end][$key];
        $day_text = $day_text . ' - ' . $end; // append the day to the string
      }
      // at this point, $day_text will be something like 'Monday' or 'Monday - Thursday'
      if (!empty($opening_hours[$os[0]]['optional_text'])) {
        // optional string takes precedent over any opening hours that may be set
        $hours_text = htmlentities($opening_hours[$os[0]]['optional_text']);
      }
      elseif (!empty($opening_hours[$os[0]]['open'])) {
        // otherwise generate something like '9am - 5.30pm'
        $hours_text = format_opening_hour($opening_hours[$os[0]]['open']) . ' - ' .format_opening_hour($opening_hours[$os[0]]['closed']);
      }
      else {
        // if nothing, it must be closed on that day/days
        $hours_text = 'Closed';
      }
      // new row for our table
      $html .= '<tr>
        <td>'.$day_text.':</td>
        <td>'.$hours_text.'</td>
      </tr>';
    }
  }

  // append the extra block of text at the end of the table
  if (!empty($extra_text)) {
    $html .= '<tr>
      <td'.$colspan.'>'.htmlentities($extra_text).'</td>
    </tr>';
  }

  $html .= '</table>';
  return $html;
}

Open in new window


Desired result
0
Comment
Question by:tjyoung
  • 6
  • 5
11 Comments
 
LVL 108

Expert Comment

by:Ray Paseur
Comment Utility
Wow, at first glance it looks like this code was written by an inexperienced programmer using PHP4.  Please have a look at the examples in these articles while I try to get you a better (21st century) interpretation.

https://www.experts-exchange.com/articles/20920/Handling-Time-and-Date-in-PHP-and-MySQL-OOP-Version.html

https://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL-Procedural-Version.html
0
 
LVL 108

Expert Comment

by:Ray Paseur
Comment Utility
Now that I've read his article, this seems to be the best advice:
Some seem to simply store them [business hours] as an unstructured blob of plaintext and spit that back to the user.
But as an intellectual exercise, I'll create an example to show you how I would go about this task.  The central objective here is simplicity, so that anyone who can use a web page can update the business hours.  In practice I would probably put such a web page behind a password-protected link.
0
 
LVL 1

Author Comment

by:tjyoung
Comment Utility
I did store as a string but I need to be able to test for open and closing times for various bits of the application so a blob won't work
0
 
LVL 1

Author Comment

by:tjyoung
Comment Utility
Some cron tasks run based on open and closing hours (30min before at each store do xxx) that can change during the year etc

May be not worth the effort. I thought combining days with same hours etc was a good idea
0
 
LVL 108

Accepted Solution

by:
Ray Paseur earned 500 total points
Comment Utility
Writing unnecessary code is an anti-pattern, and while it's fun to experiment with PHP and date/time processing, it's common sense to understand that if you write less code you have less risk of error.

Here's an example based on this article:
https://www.experts-exchange.com/articles/12335/PHP-and-MySQLi-Table-Maintenance.html

Please see: https://iconoun.com/demo/temp_tjyoung_table_maintenance_create.php
<?php // temp_tjyoung_table_maintenance_create.php
/**
 * https://www.experts-exchange.com/questions/28958716/Retrieve-and-show-store-hours.html
 *
 * https://www.experts-exchange.com/articles/12335/PHP-and-MySQLi-Table-Maintenance.html
 * https://www.experts-exchange.com/articles/5300/Giving-Your-Client-Control-of-a-Chalk-Board-on-her-Web-Site.html
 */
error_reporting(E_ALL);


// THIS SCRIPT CREATES A TEST DATA SET IN THIS TABLE
$tablename = 'temp_tjyoung_maintenance';


// DATABASE CONNECTION AND SELECTION VARIABLES - GET THESE FROM YOUR HOSTING COMPANY
$db_host = "localhost"; // PROBABLY THIS IS OK
$db_name = "??";
$db_user = "??";
$db_word = "??";

// OPEN A CONNECTION TO THE DATA BASE SERVER AND SELECT THE DB
$mysqli = new mysqli($db_host, $db_user, $db_word, $db_name);

// DID THE CONNECT/SELECT WORK OR FAIL?
if ($mysqli->connect_errno)
{
    $err
    = "CONNECT FAIL: "
    . $mysqli->connect_errno
    . ' '
    . $mysqli->connect_error
    ;
    trigger_error($err, E_USER_ERROR);
}


// ALLOW US TO RECREATE AND RELOAD FOR MUTIPLE TESTS
$mysqli->query("DROP TABLE $tablename");


// CREATING A TABLE FOR OUR TEST DATA
$sql
=
"
CREATE TABLE $tablename
( id    INT         NOT NULL AUTO_INCREMENT PRIMARY KEY
, bid   INT         NOT NULL DEFAULT 0
, days  VARCHAR(64) NOT NULL DEFAULT ''
, hours VARCHAR(64) NOT NULL DEFAULT ''
)
"
;

// RUN THE QUERY TO CREATE THE TABLE
$res = $mysqli->query($sql);

// IF mysqli::query() RETURNS FALSE, LOG AND SHOW THE ERROR
if (!$res)
{
    $err
    = 'QUERY FAILURE:'
    . ' ERRNO: '
    . $mysqli->errno
    . ' ERROR: '
    . $mysqli->error
    . ' QUERY: '
    . $sql
    ;
    trigger_error($err, E_USER_ERROR);
}


// LOADING OUR DATA INTO THE TABLE
$rows = array
( "Monday – Wednesday"        => "9am – 5:30pm"
, "Thursday – Friday"         => "9:30am – 7pm"
, "Saturday"                  => "9:30am – 4pm"
, "Sunday"                    => "By Appointment"
, "Phone anytime before 11pm" => NULL
)
;
foreach ($rows as $days => $hours)
{
    // ESCAPE THE DATA FOR SAFE USE IN A QUERY
    $safe_days  = $mysqli->real_escape_string($days);
    $safe_hours = $mysqli->real_escape_string($hours);

    // CONSTRUCT THE QUERY USING THE ESCAPED VARIABLES
    $sql = "INSERT INTO $tablename ( days, hours ) VALUES ( '$safe_days', '$safe_hours' )";

    // RUN THE QUERY TO INSERT THE ROW
    $res = $mysqli->query($sql);

    // IF mysqli::query() RETURNS FALSE, LOG AND SHOW THE ERROR
    if (!$res)
    {
        $err
        = 'QUERY FAILURE:'
        . ' ERRNO: '
        . $mysqli->errno
        . ' ERROR: '
        . $mysqli->error
        . ' QUERY: '
        . $sql
        ;
        trigger_error($err, E_USER_ERROR);
    }

    // OPTIONAL: DISPLAY THE AUTO_INCREMENT ID OF THE RECORD JUST INSERTED
    // echo "<br>MySQLI INSERTED A ROW CONTAINING <b>$safe_days $safe_hours</b> WITH AUTO_INCREMENT ID = $mysqli->insert_id" . PHP_EOL;
}


// PREPARE AN HTML DISPLAY TABLE
$sql = "SELECT days, hours FROM $tablename";
$res = $mysqli->query($sql);
if (!$res)
{
    $err
    = 'QUERY FAILURE:'
    . ' ERRNO: '
    . $mysqli->errno
    . ' ERROR: '
    . $mysqli->error
    . ' QUERY: '
    . $sql
    ;
    trigger_error($err, E_USER_ERROR);
}
$table = '<table>';
while ($row = $res->fetch_object())
{
    $tr = <<<EOD
<tr><td>$row->days</td><td>$row->hours</td></tr>
EOD;
    $table .= $tr;
}
$table .= '</table>';
echo $table;

Open in new window

Outputs something like:
Monday – Wednesday	9am – 5:30pm
Thursday – Friday	9:30am – 7pm
Saturday	9:30am – 4pm
Sunday	By Appointment
Phone anytime before 11pm	

Open in new window

0
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 
LVL 108

Expert Comment

by:Ray Paseur
Comment Utility
...thought combining days with same hours etc was a good idea
Yes, I think that too.  And at the same time we have to look at what the objectives are here.  If the objective is to create a useful display for the web site, just store text.  If we have to create a job scheduler, we need more code (or at least more data) and it should probably be independent of the display of the business hours.  Too much functionality bound up together violates the "separation of concerns" principle.  So my instinct is to separate these objectives.
0
 
LVL 1

Author Comment

by:tjyoung
Comment Utility
In this case each dealer adds and edits their hours throughout the year. So I'd be providing them a form with each day and their corresponding open and close hours

Can't do a string because of the scheduling and that each dealer may put in just about anything if left too loose

Also need to show on the front end if the store is currently open etc
0
 
LVL 1

Author Comment

by:tjyoung
Comment Utility
on the back I'd be showing select lists in 30min increments from 7am to 10pm
Thought that would ensure their choices fit my requirements
0
 
LVL 108

Expert Comment

by:Ray Paseur
Comment Utility
Hacking aside, select lists would probably be OK (server-side validation is required).  Maybe dependent drop-downs for the days, and open and close hours?

Here is how Costco does it: http://www.costco.com/warehouse-locations/fairfax-va-204.html

Determining if a store is open or closed is pretty easy.
https://iconoun.com/demo/temp_tjyoung.php
<?php // temp_tjyoung.php
/**
 * https://www.experts-exchange.com/questions/28958716/Retrieve-and-show-store-hours.html#a41723500
 *
 * https://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL-Procedural-Version.html
 * https://www.experts-exchange.com/articles/20920/Handling-Time-and-Date-in-PHP-and-MySQL-OOP-Version.html
 */
error_reporting(E_ALL);


// HOURS OF OPERATION
$open  = '9:00am';
$close = '3:00pm';

// MAKE TIMESTAMPS
$ts_open  = strtotime("Today $open");
$ts_close = strtotime("Today $close");
$ts_now   = time();

// ARE WE OPEN OR CLOSED?
$now = date('g:ia', $ts_now);
if ( ($ts_now > $ts_open) && ($ts_now < $ts_close) )
{
    echo "It's $now, and we are open until $close";
}
else
{
    echo "Sorry, it's $now and we closed at $close.  Reopen tomorrow at $open";
}

Open in new window

0
 
LVL 1

Author Closing Comment

by:tjyoung
Comment Utility
Amazing clean as always!
0
 
LVL 108

Expert Comment

by:Ray Paseur
Comment Utility
Thanks for the points and thanks for using E-E, ~Ray
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
Since pre-biblical times, humans have sought ways to keep secrets, and share the secrets selectively.  This article explores the ways PHP can be used to hide and encrypt information.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now