This one has me pulling my hair out.
Structure for date_table:
ec_id (int - primary key)
visit_date1 (date)
visit_date2 (date)
visit_date3 (date)
visitdate1_type (varchar)
visitdate2_type (varchar)
visitdate3_type (varchar)
2 Record examples:
1001,07-19-2005,07-19-2005,07-20-2005,welcome,delivery,goodbye
1002,07-23-2005,07-24-2005,07-24-2005,welcome,delivery,goodbye
I want to input a start date and an end date in a script. The system will then display a table of all activities that are happening between those date ranges.
I have done this:
// this will actually be from the form but I manually set the variables for testing purposes
$start_date=2005-07-19;
$end_date=2005-07-30;
// set the current date to start from
$current_date = $start_date;
// I make the stop_date 1 day later than the real end_date so in my loop it will include the end_date
$stop_date = $end_date;
$pieces = explode("-", $stop_date);
$pieces[0];
$pieces[1];
$pieces[2];
$targetexpiredate = mktime (0,0,0,date("$pieces[1]") ,date("$pieces[2]")+1,date("$pieces[0]"));
$stop_date= date("Y-m-d", $targetexpiredate);
// set up my while loop
while ($current_date != $stop_date) {
// my sql statement
$sql = "Select * from date_table where
(visit_date1 ='$current_date') ||
(visit_date2='$current_date') ||
(visit_date3 ='$current_date')
";
$result = @($sql,$db)or die("$sql");
while($row = mysql_fetch_array($result)){
$ec_id=$row['ec_id'];
$visit_date1=$row['visit_date1''];
$visit_date2=$row['visit_date2''];
$visit_date3=$row['visit_date3'];
$visitdate1_type=$row['visitdate1_type'];
$visitdate2_type=$row['visitdate2_type']; $visitdate3_type=$row['visitdate3_type'];
if ($visit_date1 !="" || $visit_date1 !="0000-00-00") {
$type="$visitdate1_type";
}
if ($visit_date2 !="" || $visit_date2 !="0000-00-00") {
$type="$visitdate2_type";
}
if ($visit_date3 !="" || $visit_date3 !="0000-00-00") {
$type="$visitdate3_type";
}
echo "$current_date $ec_id $type";
} // end while sql loop
// set my current_date to the next day
$pieces = explode("-", $current_date);
$pieces[0];
$pieces[1];
$pieces[2];
$targetexpiredate = mktime (0,0,0,date("$pieces[1]") ,date("$pieces[2]")+1,date("$pieces[0]"));
$current_date= date("Y-m-d", $targetexpiredate);
} // end while date loop
I want to get the script to display exactly this:
DATE ID TYPE
2005-07-19 1001 Welcome
2005-07-19 1001 Delivery
2005-07-20 1001 Goodbye
2005-07-23 1002 Welcome
2005-07-24 1002 Delivery
2005-07-24 1002 Goodbye
Instead I get:
DATE ID TYPE
2005-07-19 1001 Welcome
2005-07-20 1001 Welcome
2005-07-23 1001 Welcome
2005-07-24 1001 Welcome
I know my $sql statement is not right, but I can not figure out for the life of me how to write it!
Thanks for any help.
