We help IT Professionals succeed at work.
Get Started

Retrieve and show store hours

tjyoung
tjyoung asked
on
181 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
Comment
Watch Question
Most Valuable Expert 2011
Author of the Year 2014
Commented:
This problem has been solved!
Unlock 1 Answer and 11 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE