Mysql Dates / PHP

Question

I am working on a calendar in PHP. With the calendar, I am able to query a mysql database and it will show the number of events for each day. The problem I'm having is that it is showing the start and end dates but i need it to also show all the days inbetween.Thus, showing that the days inbetween are also occupied. My current Table has DepDate (departure) AND RetDate (Return Date). Im thinking this has to be done through the mysql query. Any help would be awesome! Thanks buds

  <script>
function goLastMonth(month, year, keyname){
if(month == 1) {
--year;
month = 13;
}
--month
var monthstring= ""+month+"";
var monthlength = monthstring.length;
var keyname = "<?php echo $searchTerm; ?>";
if(monthlength <=1){
monthstring = "0" + monthstring;
}
document.location.href ="<?php $_SERVER['PHP_SELF'];?>?month="+monthstring+"&year="+year+"&keyname="+keyname;
}


function goNextMonth(month, year, keyname){
if(month == 12) {
++year;
month = 0;
}
++month
var monthstring= ""+month+"";
var monthlength = monthstring.length;
var keyname = "<?php echo $searchTerm; ?>";
if(monthlength <=1){
monthstring = "0" + monthstring;
}
document.location.href ="<?php $_SERVER['PHP_SELF'];?>?month="+monthstring+"&year="+year+"&keyname="+keyname;
}


</script>
</head>

<?php
if (isset($_GET['day'])){
$day = $_GET['day'];
} else {
$day = date("j");
}
if(isset($_GET['month'])){
$month = $_GET['month'];
} else {
$month = date("n");
}
if(isset($_GET['year'])){
$year = $_GET['year'];
}else{
$year = date("Y");
}
$currentTimeStamp = strtotime( "$year-$month-01");
$monthName = date("F", $currentTimeStamp);
$numDays = date("t", $currentTimeStamp);
$currentTimeStamp = strtotime( "$day-$month-$year"); // added this to reset this value for prev* and next* code
$counter = 0;
?>
<?php
include ('plane.css');
/* ------------------------------------------------------------Calendar Creation---------------------------------------------------------------------- */
include ('planeh.html');
?>



<?php
    //get previous month
     $prevMonth = $month - 1;
     $prevYear = $year;
     if($prevMonth <= 0){
       $prevMonth = 12;
       $prevYear--;
     }

     $pMonthStr = "" + $prevMonth;
     if(strlen($pMonthStr) <= 1)
       $pMonthStr = "0" + $pMonthStr;

     //get num of day for previous month
     $previousTimeStamp = strtotime( "01-$pMonthStr-$prevYear");
     $prevNumDays = date("t", $previousTimeStamp);
     $numDays = date("t", $currentTimeStamp);
     $counter = 0;



for($i = 1; $i < $numDays+1; $i++, $counter++){
$timeStamp = strtotime("$year-$month-$i");


if($i == 1) {
$firstDay = date("w", $timeStamp);
for($j = 0; $j < $firstDay; $j++, $counter++) {
$prevDay = $prevNumDays-$firstDay+$j+1;
        echo "<td class = td3><div id = prev>$prevDay</div></td>";
}
}


if($counter % 7 == 0) {
 echo"</tr><big></big><tr>";
}
/* Up to this point, everything is the development of the calendar */



$monthstring = $month;
$monthlength = strlen($monthstring);
$daystring = $i;
$daylength = strlen($i);

if($daylength <=0){
$daystring = "0".$daystring;
}
// links and date located on calendar
$todaysDate = date("m/d/y");

/* Indicates date located on calendar */
echo "<td align = center class = td3><div class = button>".$i."</a></div>";



$sqlEvent2 = mysql_query("select * FROM trips where DepDate  = '".$year."-".$month."-".$i."'"); 
$num_rows = mysql_num_rows($sqlEvent2);
if(mysql_errno()){
    echo "MySQL error ".mysql_errno().": "
         .mysql_error()."\n<br>When executing <br>\n$query\n<br>";
}
echo '<div id="button">';
echo "<a  href='".$_SERVER['PHP_SELF']."?month=".$monthstring."&day=".$i."&year=".$year."&v=true' >".$num_rows."</a></td>"; 
echo '</div>';





}
















echo "<tr>";
echo"</table>";

?>
<div id ="menu">

<?php include ('menu2.php');?>





</ul>
</div>
</div>




</tr>
</body>
</html>

possible duplicate of [Mysql: Select all data between two dates](http://stackoverflow.com/questions/1080207/mysql-select-all-data-between-two-dates) — OIS, Dec 17 '14 at 23:22
Are you talking about like a single day view or is this for like a month view. I ask because if this is a single day view and you want to get anything that overlaps that single day it is quite easy to just say `WHERE DepDate <= $curDate AND RetDate >= $curDate` and that would give you everything that touches `$curDate`. If this is for a month view then you would just use php to loop from `DepDate` to `RetDate` and mark each day in between as touching. — Jonathan Kuhn, Dec 17 '14 at 23:29
This is a month view calendar. It fills every day of the month with the data it retrieves from mysql. Ive done this calendar before but I had every date between the two dates stored which made it easier. This one has a start and end date. — josh, Dec 17 '14 at 23:36
So you can either use a calendar table like OIS is suggesting or when you put events on the calendar you need to loop over each day from start to end and add to each day. This would mean looping from start to end in php and adding events for each day to an array so all days in between have a reference to the event. I would need to see some code in order to tell you how it is done in your situation. Also, just an FYI, when responding you should tag who you are talking to like @josh because it will notify them of the response. I didn't even know you responded and just stumbled back in here. — Jonathan Kuhn, Dec 18 '14 at 00:03
@JonathanKuhn Sorry about that. I dont write much on here. Heres my code that is currently placing links into each cell on the Calendar.I put the code on my original post. Thanks man — josh, Dec 18 '14 at 00:12
That's fine. I could tell from your score you don't post much and so I just wanted to let you know. Typically the original poster (you in this case) gets notified about every comment/response and the other users only get notified if they are tagged. There is some auto notifications that get sent out sometimes, but I wouldn't rely on those. — Jonathan Kuhn, Dec 18 '14 at 00:15
Since it appears you are running a query for each day, you could likely just change the where clause in your query to something like `"...WHERE '".$year."-".$month."-".$i."' BETWEEN DepDate AND RetDate"`. Also, since you are just using the count, you would likely be better off selecting `count(*)` and getting that value instead selecting `*` and not using the data. Or at the very least change `SELECT *` to `SELECT idColumn` so you are only querying for a single column. — Jonathan Kuhn, Dec 18 '14 at 00:24
@JonathanKuhn Im gonna implement a description window later on in the project but for now, im just trying to get the days filled inbetween. I appreciate all your help and will give this a shot. Thanks again — josh, Dec 18 '14 at 00:39

score 0 · Answer 1 · answered Dec 17 '14 at 23:19

All you need here is to reference the MySQL date and time functions, specifically datediff: http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_datediff

An example would be:

SELECT start_dt, end_dt, datediff(end_dt, start_dt) as days_elapsed
FROM my_table

where start_dt and end_dt are both date valued.

Mysql Dates / PHP

1 Answers1