PHP MySQL Booking / Schedule Component

[scofro]

Hi all,

I'm looking for some help with a PHP / MySQL scheduling component that our website users will use to schedule our services. I've drawn out 2 calendars that sit side by side using PHP. One for the current month, and one for the following month...to give users the option to choose a date up to 2 months in advance.

Here is the code that I'm using to draw out the actual calendars:

<?php
        // Begin setting up the PHP calendar
        // Set all of the month names
        $monthNames = Array("January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December");

        // Set variables for calendar months and years
        $curMonthName = date(F);
        $curMonthNum = date(n);
        $curYear = date(Y);

        if ($curMonthNum == 12) {
            $advMonthNum = 1;
        }
            else {
                $advMonthNum = $curMonthNum + 1;
            }

        if ($curMonthNum == 12) {
            $advYear = $curYear + 1;
        }
        else {
            $advYear = date(Y);
        }

        if ($curMonthNum == 12) {
            $advMonthName = "January";
        }
        else {
            $advMonthName = $monthNames[$curMonthNum];
        }



        ?>

        <table class="table_calendar_main">
          <tr class="tr_center">
            <td class="td_center">
              <table width="100%" border="0" cellpadding="2" cellspacing="2">
                <tr class="tr_center">
                  <td class="td_calendar_nav" colspan="7"><?php echo $curMonthName.' '.$curYear; ?></td>
                </tr>
                <tr>
                  <td class="td_calendar_nav">S</td>
                  <td class="td_calendar_nav">M</td>
                  <td class="td_calendar_nav">T</td>
                  <td class="td_calendar_nav">W</td>
                  <td class="td_calendar_nav">T</td>
                  <td class="td_calendar_nav">F</td>
                  <td class="td_calendar_nav">S</td>
                </tr>

                <?php 
                $timestamp = mktime(0,0,0,$curMonthNum,1,$curYear);
                $maxday = date("t",$timestamp);
                $thismonth = getdate ($timestamp);
                $startday = $thismonth['wday'];
                for ($i=0; $i<($maxday+$startday); $i++) {
                    if(($i % 7) == 0 ) echo "<tr>\n";
                    if($i < $startday) echo "<td></td>\n";
                    else echo "<td align='center' valign='middle' height='20px'>". "<a href=''>". ($i - $startday + 1). "</a>" . "</td>\n";
                    if(($i % 7) == 6 ) echo "</tr>\n";
                }
                ?>
                </table>
              </td>
            </tr>
          </table>
      </div>   

      <div class="div_calendar_right">
          <table class="table_calendar_main">
          <tr class="tr_center">
            <td class="td_center">
              <table width="100%" border="0" cellpadding="2" cellspacing="2">
                <tr class="tr_center">
                  <td class="td_calendar_nav" colspan="7"><?php echo $advMonthName.' '.$advYear; ?></td>
                </tr>
                <tr>
                  <td class="td_calendar_nav">S</td>
                  <td class="td_calendar_nav">M</td>
                  <td class="td_calendar_nav">T</td>
                  <td class="td_calendar_nav">W</td>
                  <td class="td_calendar_nav">T</td>
                  <td class="td_calendar_nav">F</td>
                  <td class="td_calendar_nav">S</td>
                </tr>

                <?php 
                $timestamp2 = mktime(0,0,0,$advMonthNum,1,$advYear);
                $maxday2 = date("t",$timestamp2);
                $thismonth2 = getdate ($timestamp2);
                $startday2 = $thismonth2['wday'];
                for ($i=0; $i<($maxday2+$startday2); $i++) {
                    if(($i % 7) == 0 ) echo "<tr>\n";
                    if($i < $startday2) echo "<td></td>\n";
                    else echo "<td align='center' valign='middle' height='20px'>". ($i - $startday2 + 1) . "</td>\n";
                    if(($i % 7) == 6 ) echo "</tr>\n";
                }
?>

I have a MySQL table called servicearea_dates that is constructed like this:

scheduleID, int(11), NO, PRI, , auto_increment
areaCode, varchar(6), YES, , , 
scheduleDate, date, YES, , , 
carsPerDay, int(11), YES, , , 

I will know the value of 'areaCode' from a previous form that will be completed by the user. I would like to query the MySQL table for all matches to 'areaCode' up to 60 days from current day, and then use the results of the query to create hyperlinks on the above calendars, but only for the the days that match the 'scheduleDate' value of the query.

The query would look like this:

SELECT areaCode,scheduleDate,carsPerDay from servicearea_dates where areaCode='CODE-HERE'

In addition, I need to control the number of services for a given day to the value 'carsPerDay' field to keep from getting over booked. Once the value of 'carsPerDay' is reached, that specific day should no longer be a hyperlink to click on.

Thanks in advance for your help.