Greetings,

I have a function that will recieve an appointment start time, end time (in mysql DATE format). then it will check if there are any other appointments that are within that appointment.

the problem comes when i check for the minutes.

the whole function ( you can just start reading from /// START /// ):


<?
function do_the_ease ($start_day,$end_day,$start_time,$end_time,$query,$the_id,$to_tell,$kind_is){
list($start_y,$start_m,$start_d) = explode("-",$start_day);
list($end_y,$end_m,$end_d) = explode("-",$end_day);

list($start_yu,$start_mu,$start_du) = explode("-",$start_day);
list($end_yu,$end_mu,$end_du) = explode("-",$end_day);
$addind_up = 0;

while($start_yu <= $end_yu){
if($start_yu != $end_yu){
$start_mu = 01;
$end_mu = 12;
} else {
$start_mu = $start_m;
$end_mu = $end_m;
}
while($start_mu <= $end_mu){
if($start_mu != $end_mu){
$start_du = 01;
$end_du = 31;
} else {
$start_du = $start_d;
$end_du = $end_du;
}
while($start_du <= $end_du){
if(strlen($start_du) == 1){ $start_du = '0' . $start_du;}
if(strlen($start_mu) == 1){ $start_mu = '0' . $start_mu;}

$final_day = $start_yu . '-' . $start_mu . '-' . $start_du;

$t_query = @mysql_query("SELECT * FROM appointments WHERE `" . $to_tell . "`='$the_id' AND `start_date` <= '$final_day' AND
`end_date` >= '$final_day'");

$check_dates = @mysql_num_rows($t_query);
if($check_dates > 0){
while($get_dates = @mysql_fetch_array($t_query)){
if($final_day > $get_dates[5] && $final_day < $get_dates[6]){
if($kind_is == 1){
return false;
} else {
$addind_up++;
}
}

if($get_dates[5] == $final_day && $get_dates[6] != $final_day){
if($get_dates[6] > $final_day){
if($end_time > $get_dates[7]){
if($kind_is == 1){
return false;
} else {
$addind_up++;
}
}
}
}
if($get_dates[6] == $final_day && $get_dates[5] != $final_day){
if($get_dates[5] < $final_day){
if($start_time < $get_dates[8]){
if($kind_is == 1){
return false;
} else {
$addind_up++;
}
}
}
}
$i = 0;
$the_list = NULL;
if($get_dates[6] == $final_day && $get_dates[5] == $final_day){
list($start_hour,$start_min) = explode(":",$start_time);
list($end_hour,$end_min) = explode(":",$end_time);
list($start_houru,$start_minu) = explode(":",$start_time);
list($end_houru,$end_minu) = explode(":",$end_time);
while($start_houru <= $end_houru){

////START//////
///If != , first work, if == last will work
if($start_houru < $end_houru && $start_min == '00'){
$start_minu = '00';
$end_minu = '60';
} else {
$start_minu = $start_min;
$end_minu = $end_min;
}
//echo $start_minu . '<p>';

while($start_minu <= $end_minu){
if(strlen($start_houru) == 1){ $start_houru = '0' . $start_houru;}
if(strlen($start_minu) == 1){ $start_minu = '0' . $start_minu;}

$to_do = $start_houru . ':' . $start_minu . ':00';

$v_query = "SELECT * FROM appointments WHERE `" . $to_tell . "`='$the_id' AND `start_date` >= '$start_day' AND
`end_date` <= '$end_day' AND `start_time` < '$to_do' AND `end_time` > '$to_do'";

$z_query = @mysql_query($v_query);
//echo $v_query;
$t_query2 = @mysql_num_rows($z_query);

if($t_query2 > 0){
if($kind_is == 1){
return false;
} else {
$get_id = @mysql_fetch_array(@mysql_query("SELECT id FROM appointments WHERE `" . $to_tell . "`='$the_id' AND `start_date` >= '$start_day' AND
`end_date` <= '$end_day' AND `start_time` < '$to_do' AND `end_time` > '$to_do'"));
$is_found = true;
$x = 0;

for($x=0;$x < $i;$x++){
if($the_list[$x] == $get_id[0] || $the_list[$x] == NULL){
$is_found = false;
}
}
if($is_found == true){
$addind_up++;
$the_list[$i] = $get_id[0];
$i++;
}
}
}

$start_minu = $start_minu+15;
}
$start_houru++;
}
}
}
}
$start_du++;
}
$start_mu++;
}
$start_yu++;
}
if($kind_is == 1){
return true;
} else {
if($addind_up >=2){
return false;
} else {
return true;
}
}
return true;
}
?>


The problem where i think it is on this piece of code:

<?
if($start_houru < $end_houru && $start_min == '00'){
?>


I appreciate any help.

Peace,

why don't you use the "datetime" datatype in your database to store begin date & time in one filed and end date & time in another field? That way it is just a single SQL statement to find which appointments are in a given timeframe.

Say, $start_datetime and $end_datetime are passed to the function, both containing date and time (eg. '2004-08-08 11:15:00')


$t_query = @mysql_query("SELECT * FROM appointments WHERE `" . $to_tell . "`='$the_id' AND `start_date` < '$end_datetime' AND `end_date` > '$start_datetime'");


will give you all records with appointments which are (at least partially) inside the timeframe you specified.

the best way to work on datetimes in php is to compare just the timestamp imo


$timesec = time();

returns an integervalue and its easy to compare etc...

for the final output you can easy format it, by using

echo date("Ymd H:i:s", $timesec); or whatever

usually i "never" use the datetime fieldformat from mysql

Greetings,

Ok i will use the DATE TIME format..

What is the best query that will detect the appointments (appts can be at 15 minutes interval (05:15:00,14:45:00).

I tried the following, but none worked:


$query_it = "SELECT * FROM appointments WHERE `" . $to_tell . "`='$the_id'
AND (`st` BETWEEN '$start_datetime' AND '$end_datetime' OR `et` BETWEEN '$start_datetime' AND '$end_datetime') ";


$query_it = "SELECT * FROM appointments WHERE `" . $to_tell . "`='$the_id'
AND `st` > '$start_datetime' AND `et` < '$end_datetime'";

Peace,

you might want to try that one


$query_it = "SELECT * FROM appointments WHERE `" . $to_tell . "`='$the_id' AND `st` < '$end_datetime' AND `et` > '$start_datetime'";

Originally posted by azizny
Greetings,

Ok i will use the DATE TIME format..

What is the best query that will detect the appointments (appts can be at 15 minutes interval (05:15:00,14:45:00).

I tried the following, but none worked:


$query_it = "SELECT * FROM appointments WHERE `" . $to_tell . "`='$the_id'
AND (`st` BETWEEN '$start_datetime' AND '$end_datetime' OR `et` BETWEEN '$start_datetime' AND '$end_datetime') ";


$query_it = "SELECT * FROM appointments WHERE `" . $to_tell . "`='$the_id'
AND `st` > '$start_datetime' AND `et` < '$end_datetime'";

Peace,

Thanks alot kangar for you help..

I decided to stick to the old function, which was easier to fix and it was fit to catch appointments by the seconds too.

Thank you,