Hello,

I have a reservation script that shows the what time are the drivers available.

it shows on a table with a timeframe from 8am to 10pm when the driver is not available it will show on a red background the time he is not available, what i need to do is add a feature that lets the user know why the driver is not available. so that it will show what the driver is doing at that time. so for that i added a field on the table called task. which is going to store what the driver is doing. but my problem is how can i display that along with the time he is busy here is a screenshot of how it looks now.



here is the code

PHP Code:

if ($CheckResStateCount>0){
                 $ResChapelNAme=CheckReservationChapelName($resStartTime,$actDate);            
                 $sql_1="SELECT res_startTime,res_endTime FROM reservation WHERE res_date='$actDate' AND res_driver_id=1";        
                 $result_1=mysql_query($sql_1) or die (mysql_error());
                 $rec_1_count=mysql_num_rows($result_1);
                  
                 if ($rec_1_count>0){
                 $chapaleClikOne=true; 
                 }else{ $chapaleClikOne=false;}         

 
                            if ( $chapaleClikOne==true){  

                                            $item=array();

                                            $CicketColor_1=array();
                                            $CicketColor_1_Link=array();
                                            $item = explode("-",$ResTime);
                                            $count=count($item);
                                            
                                                    for ($r=0 ;$r<=14 ;$r++){
                                                                            

                                                      $CicketColor_1[$r]='#DBFDC4'; 
                                                      $CicketColor_1_Link[$r]=1;
                                                                     }

                                                            for ($n=0;$n<$rec_1_count;$n++){
                                                              $MyDbResTimeStart_1=mysql_result($result_1,$n,'res_startTime'); 
                                                              $MyDbResTimeEnd_1=mysql_result($result_1,$n,'res_endTime'); 
                                                              $ResTime_1=$MyDbResTimeStart_1.'-'.$MyDbResTimeEnd_1;


                                        $LoopStart_1=substr($MyDbResTimeStart_1,0,2); 
                                        $LoopEnd_1=substr($MyDbResTimeEnd_1,0,2); 
                                        
                                        $arraySt=$LoopStart_1-8;
                                        $arrayEnd=$LoopEnd_1-8;
                                                                
                                        for ($m=$arraySt;$m<=$arrayEnd ;$m++){
                                           $CicketColor_1[$m]='#FF0000'; 
                                           $CicketColor_1_Link[$m]=0;

                                                            }

                                                        
                                                            }

                    } 


that is the code thats going to get the reservation details from the DB

this code is displaying the info..

<tr><?php if ($chapaleClikOne==true){?>
    
    <td id="left" width="100"><?php echo $driver1;?> </td>
    <td bgcolor="<?php echo $CicketColor_1[0];?>"><?php if ($CicketColor_1_Link[0]==1){ echo $ResTimePeriod[0];?></a><?php }else{ echo $ResTimePeriod[0];}?></td>
    <td bgcolor="<?php echo $CicketColor_1[1];?>"><?php if ($CicketColor_1_Link[1]==1){ echo $ResTimePeriod[1];?></a><?php }else{ echo $ResTimePeriod[1];}?></td>
    <td bgcolor="<?php echo $CicketColor_1[2];?>"><?php if ($CicketColor_1_Link[2]==1){ echo $ResTimePeriod[2];?></a><?php }else{ echo $ResTimePeriod[2];}?></td>
    <td bgcolor="<?php echo $CicketColor_1[3];?>"><?php if ($CicketColor_1_Link[3]==1){ echo $ResTimePeriod[3];?></a><?php }else{ echo $ResTimePeriod[3];}?></td>
    <td bgcolor="<?php echo $CicketColor_1[4];?>"><?php if ($CicketColor_1_Link[4]==1){ echo $ResTimePeriod[4];?></a><?php }else{ echo $ResTimePeriod[4];}?></td>
    <td bgcolor="<?php echo $CicketColor_1[5];?>"><?php if ($CicketColor_1_Link[5]==1){ echo $ResTimePeriod[5];?></a><?php }else{ echo $ResTimePeriod[5];}?></td>
    <td bgcolor="<?php echo $CicketColor_1[6];?>"><?php if ($CicketColor_1_Link[6]==1){ echo $ResTimePeriod[6];?></a><?php }else{ echo $ResTimePeriod[6];}?></td>
    <td bgcolor="<?php echo $CicketColor_1[7];?>"><?php if ($CicketColor_1_Link[7]==1){ echo $ResTimePeriod[7];?></a><?php }else{ echo $ResTimePeriod[7];}?></td>
    <td bgcolor="<?php echo $CicketColor_1[8];?>"><?php if ($CicketColor_1_Link[8]==1){ echo $ResTimePeriod[8];?></a><?php }else{ echo $ResTimePeriod[8];}?></td>
    <td bgcolor="<?php echo $CicketColor_1[9];?>"><?php if ($CicketColor_1_Link[9]==1){ echo $ResTimePeriod[9];?></a><?php }else{ echo $ResTimePeriod[9];}?></td>
    <td bgcolor="<?php echo $CicketColor_1[10];?>"><?php if ($CicketColor_1_Link[10]==1){ echo $ResTimePeriod[10];?></a><?php }else{ echo $ResTimePeriod[10];}?></td>
    <td bgcolor="<?php echo $CicketColor_1[11];?>"><?php if ($CicketColor_1_Link[11]==1){ echo $ResTimePeriod[11];?></a><?php }else{ echo $ResTimePeriod[11];}?></td>
    <td bgcolor="<?php echo $CicketColor_1[12];?>"><?php if ($CicketColor_1_Link[12]==1){ echo $ResTimePeriod[12];?></a><?php }else{ echo $ResTimePeriod[12];}?></td>
    <td bgcolor="<?php echo $CicketColor_1[13];?>"><?php if ($CicketColor_1_Link[13]==1){ echo $ResTimePeriod[13];?></a><?php }else{ echo $ResTimePeriod[13];}?></td>
    <td bgcolor="<?php echo $CicketColor_1[14];?>"><?php if ($CicketColor_1_Link[14]==1){ echo $ResTimePeriod[14];?></a><?php }else{ echo $ResTimePeriod[14];}?></td>
                       
   
    <?php }else{?>
    <td id="left" width="100"><?php echo $driver1;?> </td>
    <td bgcolor="<?php echo $GoColor;?>"><?php echo $ResTimePeriod[0];?></a></td>
    <td bgcolor="<?php echo $GoColor;?>"><?php echo $ResTimePeriod[1];?></a></td>
    <td bgcolor="<?php echo $GoColor;?>"><?php echo $ResTimePeriod[2];?></a></td>
    <td bgcolor="<?php echo $GoColor;?>"><?php echo $ResTimePeriod[3];?></a></td>
    <td bgcolor="<?php echo $GoColor;?>"><?php echo $ResTimePeriod[4];?></a></td>
    <td bgcolor="<?php echo $GoColor;?>"><?php echo $ResTimePeriod[5];?></a></td>
    <td bgcolor="<?php echo $GoColor;?>"><?php echo $ResTimePeriod[6];?></a></td>
    <td bgcolor="<?php echo $GoColor;?>"><?php echo $ResTimePeriod[7];?></a></td>
    <td bgcolor="<?php echo $GoColor;?>"><?php echo $ResTimePeriod[8];?></a></td>
    <td bgcolor="<?php echo $GoColor;?>"><?php echo $ResTimePeriod[9];?></a></td>
    <td bgcolor="<?php echo $GoColor;?>"><?php echo $ResTimePeriod[10];?></a></td>
    <td bgcolor="<?php echo $GoColor;?>"><?php echo $ResTimePeriod[11];?></a></td>
    <td bgcolor="<?php echo $GoColor;?>"><?php echo $ResTimePeriod[12];?></a></td>
    <td bgcolor="<?php echo $GoColor;?>"><?php echo $ResTimePeriod[13];?></a></td>
    <td bgcolor="<?php echo $GoColor;?>"><?php echo $ResTimePeriod[14];?></a></td>
    <?php }?>
  </tr>

Thanks for your help.

First off, you can really simplify your display code with a couple of loops:

PHP Code:

<tr>
<?php if ($chapaleClikOne==true){?>

    <td id="left" width="100"><?php echo $driver1;?> </td>
    <?php for($i = 0; $i < count($ResTimePeriod); $i++) { ?>
        <td bgcolor="<?php echo $CicketColor_1[$i];?>"><?php if ($CicketColor_1_Link[$i]==1){ echo $ResTimePeriod[$i];?></a><?php }else{ echo $ResTimePeriod[$i];}?></td>
    <?php } ?>
                       

<?php }else{ ?>

    <td id="left" width="100"><?php echo $driver1;?> </td>
    <?php for($i = 0; $i < count($ResTimePeriod); $i++) { ?>
        <td bgcolor="<?php echo $GoColor;?>"><?php echo $ResTimePeriod[$i];?></a></td>
    <?php } ?>
    
<?php } ?>
</tr>

The other blob of code you posted is a tabbing nightmare and it is difficult to follow. From what I can tell you'll need to update your query to select the additional column:

PHP Code:

$sql_1="SELECT res_startTime,res_endTime,task FROM reservation WHERE res_date='$actDate' AND res_driver_id=1"; 


If you can clean it up a bit I'm guessing someone might be able to help you more from there. I'm not seeing the code where ResTimePeriod is set so you may want to post that as well.

thank you so much.. that loop is so much better!!! it works great.

i know the code is messy someone did it for me and i really dont understand how it works i can't follow it either :/ also $ResTimePeriod is not defined anywhere on the page.. so i don't even know how that is working.. I cant really clean the code cause i'm not sure how it is working i can show you more code if that would help?

and yes that was my first though add the new column to the query but i don't know what to do after that

Thanks

ok so i managed to do it a different way and i got only one problem dressing is supposed to show below the time is reserved so from 6pm to 8 pm



this is the script

PHP Code:

$sqltype ="SELECT res_id,res_startTime,res_endTime,type FROM reservation WHERE res_date='$actDate' AND res_driver_id=1 ORDER BY res_startTime";  
    $result = mysql_query($sqltype); 
    $record_count=mysql_num_rows($result);   
    if($record_count>0){
        
        echo '<td id="left" width="100">Task:';
        while($row1 = mysql_fetch_assoc($result)) { 
        
        $startTime = $row1['res_startTime'];
        $endTime = $row1['res_endTime'];
        $start_sub=substr($startTime,0,2); 
        $end_sub=substr($endTime,0,2);
        $diff = $end_sub - $start_sub +1; 
        
        
        echo '<td bgcolor="#FF0000" colspan="'.$diff.'" style="font-weight:bold; text-align:center">';
            echo '<a href="edit_misa.php?resid='.urlencode($row1['res_id']).'" ><font color="white">'. $row1['type'] . '</font></a> '; 
            }
                echo '<br/> </td></tr>';
                
        }else{
            echo '<td><br/></td>';
    } 


Add a variable to keep track of the last task's ending time. When you find a new task, calculate the difference between the last task's ending time and the current task's starting time, and add that many empty cells.

PHP Code:

$last_task = 0; // This line is new

while($row1 = mysql_fetch_assoc($result)) { 
        
        $startTime = $row1['res_startTime'];
        $endTime = $row1['res_endTime'];
        $start_sub=substr($startTime,0,2); 
        $end_sub=substr($endTime,0,2);
        $diff = $end_sub - $start_sub +1;

        /*===== This is new code =====*/
        $empty = $start_sub - $last_task; // you may have to subtract 1, depending on what you store in your database
        $last_task = $end_sub;
        if ($empty > 0) {
            echo '<td colspan="' . $empty . '">&nbsp;</td>';
        }
        /*===== New code ends here =====*/
        
        
        echo '<td bgcolor="#FF0000" colspan="'.$diff.'" style="font-weight:bold; text-align:center">';
        echo '<a href="edit_misa.php?resid='.urlencode($row1['res_id']).'" ><font color="white">'. $row1['type'] . '</font></a> '; 
} 


Hello Lizciz,

the code is not working it adds the td with empty cells after dressing so it makes the new <td colspan="4"> but after dressing and it is supposed to do it before the dressing on the empty slots where there are not reservations

You need to match the start time in an iteration with the end time of the previous iteration. If the new ST is equal to the old ET, there's no empty slot(s). If it isn't, then you handle the empty slot(s).

Originally Posted by stivens

Hello Lizciz,

the code is not working it adds the td with empty cells after dressing so it makes the new <td colspan="4"> but after dressing and it is supposed to do it before the dressing on the empty slots where there are not reservations

The code block I added was before your output, how can it possibly come after? Are you sure your code looks like mine?

EDIT:
Are you using float or something, to move the cells to the left?

EDIT2:
Aha, now I noticed the error. You have everything inside one <td> tag (evan the other <td> tags). Change it to this:

PHP Code:

$sqltype ="SELECT res_id,res_startTime,res_endTime,type FROM reservation WHERE res_date='$actDate' AND res_driver_id=1 ORDER BY res_startTime";  
$result = mysql_query($sqltype); 
$record_count=mysql_num_rows($result);   
if($record_count>0){

    $last_task = 0; // This line is new

    // I don't know how the rest of your code looks. If you have already
    // echoed a <tr> tag, remove this one.
    echo '<tr>';

    echo '<td id="left" width="100">Task:</td>'; // I added a closing </td> here
    while($row1 = mysql_fetch_assoc($result)) { 
        
        $startTime = $row1['res_startTime'];
        $endTime = $row1['res_endTime'];
        $start_sub=substr($startTime,0,2); 
        $end_sub=substr($endTime,0,2);
        $diff = $end_sub - $start_sub +1;

        /*===== This is new code =====*/
        $empty = $start_sub - $last_task; // you may have to subtract 1, depending on what you store in your database
        $last_task = $end_sub;
        if ($empty > 0) {
            echo '<td colspan="' . $empty . '">&nbsp;</td>';
        }
        /*===== New code ends here =====*/
        
        
        echo '<td bgcolor="#FF0000" colspan="'.$diff.'" style="font-weight:bold; text-align:center">';
        echo '<a href="edit_misa.php?resid='.urlencode($row1['res_id']).'" ><font color="white">'. $row1['type'] . '</font></a> ';
        echo '</td>'; // I added a closing </td> here
    }

    echo '</tr>'; // if you remove the <tr> tag above, remove this as well
} 


Thanks lizciz,

the problem now is that is showing a td at the beginning of the row with 7 empty slots.. :/ this is the complete code i have on the page

PHP Code:

<tr><?php
      $sqltype ="SELECT res_id,res_startTime,res_endTime,type FROM reservation WHERE res_date='$actDate' AND res_driver_id=1 ORDER BY res_date,res_startTime";  
    $result = mysql_query($sqltype); 
    $record_count=mysql_num_rows($result);   
    if($record_count>0){
        //echo '<tr><td><br/></td></tr>';
        
        $last_task = 0;
        
        echo '<td id="left" width="100">Task:</td>'; // I added a closing </td> here
           
        while($row1 = mysql_fetch_assoc($result)) { 
        
        $startTime = $row1['res_startTime'];
        $endTime = $row1['res_endTime'];
        $start_sub=substr($startTime,0,2); 
        $end_sub=substr($endTime,0,2);
        $diff = $end_sub - $start_sub +1;

        /*===== This is new code =====*/
        $empty = $start_sub - $last_task -1; // you may have to subtract 1, depending on what you store in your database
        $last_task = $end_sub;
        if ($empty > 0) {
            echo '<td colspan="' . $empty . '">&nbsp;</td>';
        }
        /*===== New code ends here =====*/
        echo '<td bgcolor="#FF0000" colspan="'.$diff.'" style="font-weight:bold; text-align:center">';
        
            echo '<font color="white">'. $row1['type'] . '</font> ';
            if($type == $row1['type']){echo '<input type="button" value="Add On" />'; } 
            }
             
                echo '<br/> </td>';
                
                echo $empty ." empty";
                
        }else{
            echo '<td><br/></td>';
    }?> 
 
 </tr>

Alright, change it to this then (I added the if() block):

PHP Code:

/*===== This is new code =====*/
        if ($last_task != 0) {
            $empty = $start_sub - $last_task - 1; // you may have to subtract 1, depending on what you store in your database
            if ($empty > 0) {
                echo '<td colspan="' . $empty . '">&nbsp;</td>';
            }
        }
        $last_task = $end_sub;
        /*===== New code ends here =====*/ 


Originally Posted by lizciz

Alright, change it to this then (I added the if() block):

PHP Code:

/*===== This is new code =====*/
        if ($last_task != 0) {
            $empty = $start_sub - $last_task - 1; // you may have to subtract 1, depending on what you store in your database
            if ($empty > 0) {
                echo '<td colspan="' . $empty . '">&nbsp;</td>';
            }
        }
        $last_task = $end_sub;
        /*===== New code ends here =====*/ 


THANK YOU SO MUCH!!!! this did it!. it's working fine now i only have one question.. why is $last_task defined at the end of the code outside the if statement? just curious and i would like to learn this.. hehe thanks

You're welcome

Originally Posted by stivens

why is $last_task defined at the end of the code outside the if statement?

The purpose of the if() block is just to not add an empty space before the first task. Since $last_task is initially set to zero, and the first task starts at 8, $empty will be (8 - 0 - 1 = 7) for the first task, but should be zero.

Besides, the purpose of $last_task is to remeber the previous task, weather it is the first task or not. If you have it inside the if() block, $last_task would always be zero and the if() block would never be entered.

Note however, that I assumed that the first task always starts at 8. If this is not the case, then it could happen that there should be an empty space first, but it will be ignored with the current code. You could instead initialize $last_task to 7 instead of 0, hmm... and actually remove the if() block

Then it would work for all cases. If the first task starts at 8 you would get (8 - 7 - 1 = 0) empty spaces in the beginning. And if the first task would start at, lets say 10, you'd get (10 - 7 - 1 = 2) empty spaces.

Oh, and another thing. I'm assuming you added the minus one because it didn't work without it? To me, this suggests that you're thinking "the wrong way" with the hours. For example, the first task in your picture starts at 8am and goes on for 6 hours, but do you store this (the end time) as 1pm or 2pm in the database? It should, logically, be 2pm but I've seen people doing it the other way (it ends 1pm so they fill the 1pm box too). If you have it "the right way", you should not need the minus one. If the first task ends at 2pm, and the next task starts at 6pm, there would be (18 - 14 = 4) empty spaces (in 24h time format, I'm more comfortable with that ), which is correct. And then $last_task should initially be set to 8 instead of 7.


So, to sum up, remove the if() block again and set $last_task to 8 instead of 0. And make sure that you're filling the hour boxes correctly.

Thanks for the explanation

i changed the code as you said and set $last_task = 7 and removed the if() block it works good. the time in the database is stored as 1pm so start_time = 08:00:00 end_time = 13:00:00 but since i didn't write the code i don't understand how it is working i tried to follow it but couldn't get it. at the beginning of this post i posted the code how it was working. even nullpointer suggested a better way to display the available times but the loop he gave me didn't work so well, so i had to leave it like that. i wish i could understand what that code does.. but honestly it looks messy i dont get it lol

Well, I can see why you couldn't understand the code. It's very messy. The best thing to do, really, would be to throw it away and write it from scrath yourself. But I sense you don't want to do that, so you could instead change this line:

PHP Code:

for ($m=$arraySt;$m<=$arrayEnd ;$m++){ 


to this:

PHP Code:

for ($m=$arraySt;$m<$arrayEnd ;$m++){ 


From my understanding that is the loop that colors the boxes red. I simply changed the <= operator to <, which will make the loop iterate one round less, which should color the boxes properly.

Thanks lizciz,

that didn't work it shows one less red box than the time reserved i'm gonna have to leave it like that :/ although i plan to clean it up or write the code from scratch when i get more experience and know how that works

Originally Posted by stivens

that didn't work it shows one less red box than the time reserved

Well, that was the point
If the task starts 8:00 and ends 13:00, the 1pm box should not be red. The task doesn't include 1pm, it ends just before it. From 8:00 to 13:00 there are (13 - 8 = 5) hours, so there should be 5 red boxes.

Thanks Lizciz,

that actually makes lot of sense lol. so i changed the code as you suggested and i had to change $last_task = 8 and remove the -1 and +1 i didnt need them anymore. this is the final result... thank you!

PHP Code:

$last_task = 8;
        
        echo '<td id="left" width="100">Task:</td>'; // I added a closing </td> here
           
        while($row1 = mysql_fetch_assoc($result)) { 
        
        $startTime = $row1['res_startTime'];
        $endTime = $row1['res_endTime'];
        $start_sub=substr($startTime,0,2); 
        $end_sub=substr($endTime,0,2);
        $diff = $end_sub - $start_sub;

        
        /*===== This is new code =====*/
       
            $empty = $start_sub - $last_task; // you may have to subtract 1, depending on what you store in your database
            if ($empty > 0) {
                echo '<td colspan="' . $empty . '">&nbsp;</td>';
            }
        
        $last_task = $end_sub;
        /*===== New code ends here =====*/ 


Originally Posted by lizciz

Well, that was the point
If the task starts 8:00 and ends 13:00, the 1pm box should not be red. The task doesn't include 1pm, it ends just before it. From 8:00 to 13:00 there are (13 - 8 = 5) hours, so there should be 5 red boxes.

something curios just happened.. someone made a reservation from 4am to 8am which is not on the timeline table the timeline table shows from 8am to 10pm so no red boxes should appear and they don't but what is showing is a task from 8am to 12pm but the reservation is set from 4am to 8 am i didn't change the code or anything.. any clue why this happened? thanks

Well, that's because the table is hard coded to go from 8am to 10pm. The code then just takes the first task, sees that it should be 5 hours long, and fills in 5 boxes. It doesn't take into account their actual times. If it should, then you need to rework the code a bit.

Either that, or make sure that tasks cannot be started before 8am...

Thanks...

i will make sure the task wont make reservations before 8am it will be easier.. hehe