Store Open or Closed

[ptara1]

I would like to be able to create a script that displays whether or not a store is open when the user looks at the page. It would be simple if some didn't close for an hour during the day.

I could set it up:
MondayOpen 800
MondayClosed 1600
and have the script determine if the current time is in between those two times.

However, like I said, some stores are closed for a little time during the day. I'm at a loss how I should set up my tables and script.

Any help would be greatly appreciated.

[blocblue]

Hi,

Create a table which stores the opening and closing time for each day of the week. Hence, if the store was open from 08:00 until 12:00 and then 13:00 until 18:00 on Monday through Friday, you could do:

id day open close
1 Monday 08:00:00 12:00:00
2 Monday 13:00:00 18:00:00
3 Tuesday 08:00:00 12:00:00
4 Tuesday 13:00:00 18:00:00
...

Then, using PHP you find the current day and time which can be used to query the table:

$day = date('l');
$time = date('H:i:s');

$sql = sprintf("SELECT `id` FROM `opening_times` WHERE `day` = '%s' AND '%s' BETWEEN `open` AND `close`", $day, $time);

If the query returns a row, then the shop is open. Otherwise it is closed. Is this what you wanted?

R.

You may need to sort out timezones too. Those times could be meaningless unless the user shares the same timezone. Also, is the server time TZ the same as the store TZ? It all gets very untidy unless TZs are tied down.

[blocblue]

If the server is configured to match the same timezone as the shop, then it doesn't matter where the user is. If the shop is closed, it is closed to everyone.

[vibhaJ]

[By mistake]

@blocblue - yes clear thinking, me cloudy as usual.

[ptara1]

Thanks a million, definitely moving in the right direction...

Now my problem is I currently have two separate tables, one for store hours and one for store info. I am using the unique id for store info multiple times within the store hours table. How can I run another mysql select statement with this to select records from the store info table? I'm going to keep working on figuring it out on my own but any help would be greatly appreciated.

Also, how do I configure the server to match the timezone for where the stores are? I didn't even think about that problem...Shows how little I actually know...

Seriously though the help is amazing.

[pritaeas]

Depending on your server you can create a PHP.INI file to configure the timezone to be used, or you can do it in code: http://us.php.net/manual/en/function.date-default-timezone-set.php

[ptara1]

what happens if the store is open past midnight or 0:00:00 hours. Will the script still work?

[blocblue]

You have the basis of the script within this post... so what does your testing show?

If all of your stores are in one country with one timezone, just use date_default_timezone_set to set your own TZ to that so everybody is then synchronized (as noted by blocblue).
However, if stores across a few timezones, you could be OK if none of them use a DST (daylight saving time). This means that they may move an hour forward/backward at certain times of the year in order to get more light in the evening/morning. If no DSTs, you could have a DB tinyint (or float) field with the timediffs from your server time on it, usu. in hours (or half hours).

If those TZs or your server time use DST, you'll need a more robust method. E.g. the UK is currently in DST mode, so it is not the same as using GMT/UTC.

[ptara1]

Alright, I have the time zone figured out. Need to continue my own testing to see if it works when it's open past 0:00:00. Thanks for all your help

[ptara1]

Ok this is the first time I've tried to run multiple selects from different tables so I'm having a little bit of a hard time.

Here is my code, I get one response returned. What should I tweak it to get all the results returned?

<?
date_default_timezone_set('America/Chicago');

include 'connect.php';

$day = date('l');
$time = date('H:i:s');

$sql = sprintf("SELECT `ID` FROM `storehours` WHERE  `Day` = '%s' AND '%s' BETWEEN `Open` AND `Close`", $day, $time);
$result=mysql_query($sql); 


while($row = mysql_fetch_array($result)){{

$ID=$row['ID'];
}

if (mysql_num_rows($result)>0) {


echo "
<table width='700px' border='0px' cellspacing='5px' cellpadding='5px'>

<tr>
<th><font face='Arial, Helvetica, sans-serif'>Name</font></th>

<th><font face='Arial, Helvetica, sans-serif'>Phone Number</font></th>

<th><font face='Arial, Helvetica, sans-serif'>Location</font>

</tr>

"; 

$sql1="SELECT * FROM stores WHERE ID ='".$ID."'"; 
$result1=mysql_query($sql1); 


while($row = mysql_fetch_array($result1))
{


$Name = $row['Name'];

$Phone_Number = $row['Phone_Number'];

$Location = $row['Location'];



echo "<tr><td>";

echo $Name;

echo "</td><td>";

echo $Phone_Number;

echo "</td><td>";

echo $Location;

echo "</td></tr>";



echo "</table>";


}


}

else {

echo "No Stores Open"; 

}
}
?>

Any help will be greatly appreciated.

Ok you want a join clause so that you just need to use one sql query. Also your braces look a bit odd. As soon as you finish your while loop, your id will point to the last record.
BTW - do storehours and stores have to be in different tables? Seems to me that the data belongs together. ANyway:

I'd do this:

<?php
$sql = mysql_query("..."); //use a join clause for tables storehours and stores
$tbldata = "";
if(mysql_num_rows($sql) > 0){
  while($data = mysql_fetch_assoc($sql)){ 
    $tbldata .= "<tr><td>{$data['name']}</td><td>...</td><td>...</td></tr>";
  }
}else{
  $tbldata = "<tr><td colspan=\"3\">No stores open</td></tr>";
}
?>

Put your table html header...

<?php echo $tbldata;?>

Put your table footer...

[ptara1]

Awesome, I was thinking that I was going to have to do a join statement but wasn't sure.

I've never done a table that way. Generally do it in the way that's illustrated in my code.

When you say $tbldata = ""; what should be put there?

Nothing. It just initialises the variable. Some sites throw a wobbly if you try to concatenate (.=) a string that doesn't exist yet. Don't know why, but I've found that this works.

[ptara1]

I've been trying to do a join table and having some trouble with mysql statement. Here is what I have and I keep getting an error.

$result = sprintf("SELECT 'hours.ID', 'store.ID', 'store.Name', 'store.Phone_Number', 'store.Location' FROM 'hours', 'stores' WHERE 'Day' = '%s' AND '%s' BETWEEN 'Open' AND 'Close' AND 'hours.ID' = 'store.ID'", $day, $time);

while($row = sprintf($result))

{

$ID=$row['ID'];

$Name = $row['Name'];

$Phone_Number = $row['Phone_Number'];

$Location = $row['Location'];
}

if (mysql_num_rows($result)>0) {
//working if statement
}

It says "Fatal error: Maximum execution time of 30 seconds exceeded"

Should I still use sprintf or should I use something else?

[blocblue]

Can we see the schemas for the two MySQL tables??

$result = mysql_query("SELECT 'hours.ID', 'store.ID', 'store.Name', 'store.Phone_Number', 'store.Location' FROM 'hours', 'stores' WHERE 'Day' = '$day' AND '$time' BETWEEN 'Open' AND 'Close' AND 'hours.ID' = 'store.ID'");
 
if(mysql_num_rows($result) > 0){
   echo "<table><thead><tr><th>ID</th><th>Store</th><th>Phone</th><th>Location</th></tr></thead<tbody>";
   while($row = mysql_fetch_assoc($result)){
      $ID=$row['ID'];
      $Name = $row['Name'];
      $Phone_Number = $row['Phone_Number'];
      $Location = $row['Location'];
      echo "<tr><td>$ID</td><td>$Name</td><td>$Phone_Number</td><td>$Location</td></tr>";
   }
   echo "</tbody></table>";
}

You could try that. If it doesn't work, look at the SQL.

[ptara1]

I have tried your fix and am currently getting this error:

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource

I will keep trying to tweak it and fix it. But any idea why this is occurring

Your table names / col names are surrounded by single quotes, they should be backticks: `

Also, I don't understand your WHERE clause, doesn't make any sense to me.

[blocblue]

Because your query is wrong. Use backticks (`) rather than single quotes (').

[ptara1]

Got it to work by only putting Day and Time in single parenthesis