Avatar of tjyoung
tjyoung

asked on 

Retrieve and show store hours

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
PHPMySQL Server

Avatar of undefined
Last Comment
Ray Paseur

8/22/2022 - Mon