[SOLVED] Date Time Difference

alexcmm · March 16, 2007

I have a question similar to another post I see on here about showing arrays.

I am trying to break down what nights someone is staying at a hotel.

They choose a start date & end date from drop down menus. (Also, it's in euro format dd-mm-yyyy) I'm not using a timestamp, which I'm afraid may hurt me.

I'm trying to get it to read like this:

Name 5 May 6 May 7 May 8 May

Bob Roberts 1 1

meaning that Bob is staying on the 6th and 7th... not the 5th or 8th.

Please tell me this is easy! I took this site over from someone else and recreated it. They had this function, but I'm not sure how they did it. (long story, but I can't ask him :-\ )

Thanks!

only one · March 16, 2007

you need to do

yyyymmdd

so you get a number that is always greater than the one before... never less

then minus the day before and return your answer...

obsidian · March 16, 2007

If there is too much data for you to change the datatype to a DATE or DATETIME column easily, I'd recommend using a little PHP magic to figure out your dates:

<?php
$start = '06-05-2007';
$end   = '07-05-2007';

list($stDay, $stMon, $stYear) = explode('-', $start);
list($edDay, $edMon, $edYear) = explode('-', $end);

$startTS = strtotime("$stYear-$stMon-$stDay");
$endTS   = strtotime("$edYear-$edMon-$edDay");

$day = 60 * 60 * 24;
$nights = array();
while ($startTS <= $endTS) {
  $nights[] = date('Y-m-d', $startTS);
  $startTS += $day;
}

echo "Staying for " . count($nights) . " nights.<br />\n";
echo "<pre>\n";
print_r($nights);
echo "</pre>\n";
?>

When you run this calculation, your $nights array contains the date of each night of the stay in YYYY-MM_DD format so that you can easily work with them in both PHP and SQL.

Hope this helps.

alexcmm · March 16, 2007

Man, this is awesome! You guys blow me away.

With that said, may I ask another question taking in one step further? (or maybe a different direction)

How can I get it to show that a person is or isn't staying on a particular day? Like the crude table layout I gave earlier:

Name                    5 May                 6 May                 7 May                 8 May
Bob Roberts                                     1                     1

obsidian · March 16, 2007

With the example I gave above, as you loop through the day list (in your chart), check and see if the current day being displayed is in the array provided (just use in_array()). If it exists, they're staying that day, and you can leave your mark, otherwise, just leave it blank.

alexcmm · March 17, 2007

I'm not sure if I'm asking too much from the people on this forum. I feel bad for needing my hand held. If there's another place meant for beginners can someone share that with me?

I understand what's happening in the code you gave if I can see it, and I even understand the logic behind the last part you mentioned, but can't put the code together. Even with the link you gave... that page is a mile long and I'm not really sure what I'm looking for.

If anyone has any tolerance left for me, can you please show me what this would look like.

obsidian · March 17, 2007

I'm not sure if I'm asking too much from the people on this forum. I feel bad for needing my hand held....

If anyone has any tolerance left for me, can you please show me what this would look like.

LOL... don't feel bad about that kind of question. That's what this whole community is around for

Let's say you want to run through the dates July 1 - July 7 and see if the person in question is staying on any of those nights using the code shown above, we just need to do something like this:

<?php
// Assuming you already have the $nights array created above
$start = strtotime("July 1");
echo "<table>\n";
echo "<tr>\n";
while ($start <= strtotime("July 7")) {
  echo "<td>\n";
  echo date('F j', $start);
  if (in_array(date('Y-m-d', $start), $nights)) {
    echo "<br /><b>X</b>\n";
  }
  echo "</td>\n";
  $start += $day;
}
echo "</tr>\n";
echo "</table>\n";
?>

Hope this helps you out some. Good luck!

alexcmm · March 18, 2007

This is great... you make it look so easy. Thanks!

alexcmm · March 18, 2007

Ok, so this is cool, but this code shows the day only if the person is staying in the hotel... I need it to show and empty cell for the days the person isn't there too.

Would that look anything like this?

  if (in_array(date('Y-m-d', $start), $nights)) {
    echo "<br /><b>X</b>";
} else {
echo "<br /><b>O</b>";

obsidian · March 18, 2007

Actually, look at the whole code above. It creates a row with a cell for each day of the specified week. Then it checks to see if that date is in the array of $nights. If so, it outputs the 'X', otherwise, it just spits out the table cell with the day.

alexcmm · March 18, 2007

Ohhhhhh, I see.... I was trying to replace your static July dates with some date variable pulled from the database... I realize now that I should have just replaced those with my own static dates that I'm actually dealing with. When I first tried replacing those july dates I replaced it with:

$stYear-$stMon-$stDay

&

$edYear-$edMon-$edDay

Now I replaced it with my real May dates and I see what you mean.

Thanks Obsidian... I appreciate your help, and your hesitation to just hand it to me. (Although you pretty much did )

obsidian · March 18, 2007

Thanks Obsidian... I appreciate your help, and your hesitation to just hand it to me. (Although you pretty much did )

Just here to serve ... and you've been much easier to work with than some because you actually have the desire to learn and not simply be spoon fed everything. I'll mark this topic as "Solved" but feel free to come back if you have any further questions.

Good luck!

alexcmm · March 18, 2007

I have this working just like you said but have a few more questions about this. As it is now my html looks like this when the script is run:

| Roberts | Bob | USA | Hilton | • | • | | 2 |

My first issue isn't that big of a deal... but if the person has no dates selected yet, but the total says 1, even though there are no bullets. Any ideas?

Second issue:

How can I show a total per day? eg. how many rooms are occupied per day? So it would look something like this:

| Roberts | Bob | USA | Hilton | • | • | | 2 |

| Doe | Joh | USA | Hilton | • | • | • | 3 |

...

| | | | | 45 | 64 | 23 | |

I guess that's all I have for now. I will definitely make a phpfreaks donation after all of this!!

obsidian · March 18, 2007

Can you show your code so far? You're to the point that it will be much easier to help you optimize your code for what you're wanting to accomplish than to try to tell you from scratch

alexcmm · March 18, 2007

Yes, thanks for sticking with me obsidian.

*disclaimer* I know I probably have some extra stuff in there that's not needed. I'll go through and clean up after I have the hard stuff over with.

Here goes:

<table width="100%" border="1" cellspacing="1" cellpadding="1">
<tr height="30" bgcolor="#999999" class="smalldate">
<td align='left' valign='middle'>Last Name</td>
<td align='left' valign='middle'>First Name</td>
<td align='center' valign='middle'>Market Country</td>
<td align='center' valign='middle'>Hotel</td>
<td align='center' valign='middle'>Check-in</td>
<td align='center' valign='middle'>Checkout</td>
<td align='center' valign='middle'>May 1</td>
<td align='center' valign='middle'>May 2</td>
<td align='center' valign='middle'>May 3</td>
<td align='center' valign='middle'>May 4</td>
<td align='center' valign='middle'>May 5</td>
<td align='center' valign='middle'>May 6</td>
<td align='center' valign='middle'>May 7</td>
<td align='center' valign='middle'>May 8</td>
<td align='center' valign='middle'>May 9</td>
<td align='center' valign='middle'>May 10</td>
<td align='center' valign='middle'>May 11</td>
<td align='center' valign='middle'>May 12</td>
<td align='center' valign='middle'>May 13</td>
<td align='center' valign='middle'>May 14</td>
<td align='center' valign='middle'>Total Nights</td>
</tr>
<?php
$id=$_GET['id'];
include 'dbinfo.php';

MYSQL_CONNECT($hostname, $username, $password) OR DIE("DB connection unavailable");
@mysql_select_db( "$database") or die( "Unable to select database"); 

$query="SELECT * FROM $usertable";
$result=mysql_query($query);
$num=mysql_numrows($result);
mysql_close();

$i=0;
while ($i < $num) {
$id=mysql_result($result,$i,"id");
$firstname=mysql_result($result,$i,"firstname");
$lastname=mysql_result($result,$i,"lastname");
$marketcountry=mysql_result($result,$i,"marketcountry");
$hotel=mysql_result($result,$i,"hotel");
$checkin=mysql_result($result,$i,"checkin");
$checkout=mysql_result($result,$i,"checkout");

$start = mysql_result($result,$i,"checkin");
$end   = mysql_result($result,$i,"checkout");

list($stDay, $stMon, $stYear) = explode('-', $start);
list($edDay, $edMon, $edYear) = explode('-', $end);

$startTS = strtotime("$stYear-$stMon-$stDay");
$endTS   = strtotime("$edYear-$edMon-$edDay");

$day = 60 * 60 * 24;
$nights = array();
while ($startTS <= $endTS) {
  $nights[] = date('Y-m-d', $startTS);
  $startTS += $day;
}

$start = strtotime("May 1");
echo "<tr align='center' valign='middle' class='smalldate'><td align='left'>$lastname</td><td align='left'>$firstname</td><td>$marketcountry</td><td>$hotel</td><td>$checkin</td><td>$checkout</td>\n";
while ($start <= strtotime("May 14")) {
  echo "<td>";
  echo " ";
  if (in_array(date('Y-m-d', $start), $nights)) {
    echo "<b>•</b>";
  }
  echo "</td>\n";
  $start += $day;
}
echo "<td>" . count($nights) . "</td>";

++$i;
}
?>
</tr>
</table>

I guess while I'm at it, I'll tell you that I'd love to get the rows to alternate colors too. I have it for other pages, but there are no other arrays to contend with, so I'm not sure how to stick it in with this one... that existing code is:

<?php
$arr = get_defined_functions();
for($i = 0; $i < $numofrows; $i++) {
    $row = mysql_fetch_array($result); //get a row from our result set
    if($i % 2) { //this means if there is a remainder
        echo "<TR bgcolor=\"#F0EEEE\">\n";
    } else { //if there isn't a remainder we will do the else
        echo "<TR bgcolor=\"white\">\n";
    }
    echo "<td class=\"smalldate\"><a href=\"register.php?id=".$row['ID']."\">edit</a></font></td>
<td class=\"smalldate\" class=\"tablebg\"><img src=\"images/spacer.gif\" width=\"0\" height=\"0\">".$row['lastname']."</td>
<td class=\"smalldate\" class=\"tablebg\"><img src=\"images/spacer.gif\" width=\"0\" height=\"0\">".$row['firstname']."</td>
<td class=\"smalldate\" class=\"tablebg\"><img src=\"images/spacer.gif\" width=\"0\" height=\"0\">".$row['marketcountry']."</td>
<td class=\"smalldate\" class=\"tablebg\"><img src=\"images/spacer.gif\" width=\"0\" height=\"0\">".$row['arrivaldate']."</td>\n";
}
?>

obsidian · March 19, 2007

OK, I've made a couple adjustments and some slight additions:

<?php
$class  = 'even'; // Get ready to alternate rows
$totals = array(); // Set up array to hold grand totals per night

for ($i = 0; $i < $num; $i++) {

$id=mysql_result($result,$i,"id");
$firstname=mysql_result($result,$i,"firstname");
$lastname=mysql_result($result,$i,"lastname");
$marketcountry=mysql_result($result,$i,"marketcountry");
$hotel=mysql_result($result,$i,"hotel");
$checkin=mysql_result($result,$i,"checkin");
$checkout=mysql_result($result,$i,"checkout");

$start = mysql_result($result,$i,"checkin");
$end   = mysql_result($result,$i,"checkout");

list($stDay, $stMon, $stYear) = explode('-', $start);
list($edDay, $edMon, $edYear) = explode('-', $end);

$startTS = strtotime("$stYear-$stMon-$stDay");
$endTS   = strtotime("$edYear-$edMon-$edDay");

$day = 60 * 60 * 24;
$nights = array();
while ($startTS <= $endTS) {
  $nights[] = date('Y-m-d', $startTS);
  $startTS += $day;
}

$start = strtotime("May 1");

$class = $class == 'even' ? 'odd' : 'even'; // Let the alternation begin!

      // Attach the $class variable to your class of each row for CSS styling
echo "<tr align='center' valign='middle' class='smalldate $class'><td align='left'>$lastname</td><td align='left'>$firstname</td><td>$marketcountry</td><td>$hotel</td><td>$checkin</td><td>$checkout</td>\n";
while ($start <= strtotime("May 14")) {
  echo "<td>";
  if (in_array(date('Y-m-d', $start), $nights)) {
    echo "<b>•</b>";
		$totals[$start] += 1;
  } else {
		echo " ";
		$totals[$start] += 0;
	}
  echo "</td>\n";
  $start += $day;
}
echo "<td>" . count($nights) . "</td>";

// $totals now holds the total count of all nights, so you can loop through and display
}
?>

With this, each day in your range will have an entry in the $totals array. You can create your totals row and loop through the array, spitting out your numbers as you wish.

Good luck.

alexcmm · March 19, 2007

Thanks obsidian... but I don't get it.

You did simplify my code but it looks exactly the same in HTML.

I'm not sure how to loop the $totals array, the alternating colors don't work, and it still has a total of 1 night when there are no dates chosen for a person. It's well over my head at the moment and when I get help that says something like, "now you just need to..." I'm new and lost I just want to give up. I need to see it to understand the logic. I have no idea of the syntax to get this to loop as you suggested. I'm not sure how else to ask, but to say, "can you spell it out for me?". If not, I understand... I've been a pain in your a$$ for days now.

You have gotten me far. I really appreciate the help you've given and hope that you haven't run out of patience for me.

Sign In

[SOLVED] Date Time Difference

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information