troubleshooting Question

Retrieve and show store hours

Avatar of tjyoung
tjyoung asked on
PHPMySQL Server
11 Comments1 Solution183 ViewsLast Modified:
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();

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" } } 

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;
}

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;
}

Desired result
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 11 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 11 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros