SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Zealot
    Join Date
    Feb 2011
    Location
    Colorado, USA
    Posts
    119
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Working Sql query and working calendar How do i combine them?

    I have a working query that pulls all appointments in the table for the day. I have a working calendar that builds the month and highlights the current day. I want to add the query to the calendar and just count the number of appointments for each day and show the number under the day of the month. The calendar is from a tutorial I found online. Any help is much appreciated.

    Query I currently use to pull appointments for the current day.

    PHP Code:
    $sql = <<<SQL
      SELECT client.lastName, client.firstName, dog.name, appointments.start, appointments.appointmentId, appointments.complete, client.clientId, dog.dogId
    FROM appointments LEFT JOIN client ON appointments.clientId = client.clientId
    LEFT JOIN dog ON appointments.dogId = dog.dogId 
    WHERE appointments.start > CURDATE()
    ORDER BY appointments.start ASC 
    SQL;

    if(!
    $result $db->query($sql)){
        die(
    'There was an error while running the query [' $db->error ']');
    }
        
         
    $num_rows $result->num_rows
    Calendar I am trying to use.

    PHP Code:
    <?php
    date_default_timezone_set
    ('America/Denver');
    function 
    build_calendar($month,$year,$dateArray) {
    $today_date date("d");
    $today_date ltrim($today_date'0');

         
    // Create array containing abbreviations of days of week.
         
    $daysOfWeek = array('Sun','Mon','Tue','Wed','Thr','Fri','Sat');

         
    // What is the first day of the month in question?
         
    $firstDayOfMonth mktime(0,0,0,$month,1,$year);

         
    // How many days does this month contain?
         
    $numberDays date('t',$firstDayOfMonth);

         
    // Retrieve some information about the first day of the
         // month in question.
         
    $dateComponents getdate($firstDayOfMonth);

         
    // What is the name of the month in question?
         
    $monthName $dateComponents['month'];

         
    // What is the index value (0-6) of the first day of the
         // month in question.
         
    $dayOfWeek $dateComponents['wday'];

         
    // Create the table tag opener and day headers

         
    $calendar "<table border='1px'>";
         
    $calendar .= "<caption>$monthName $year</caption>";
         
    $calendar .= "<tr>";

         
    // Create the calendar headers

         
    foreach($daysOfWeek as $day) {
              
    $calendar .= "<th class='header' width='50px' height='50px'>$day</th>";
         } 

         
    // Create the rest of the calendar

         // Initiate the day counter, starting with the 1st.

         
    $currentDay 1;

         
    $calendar .= "</tr><tr>";

         
    // The variable $dayOfWeek is used to
         // ensure that the calendar
         // display consists of exactly 7 columns.

         
    if ($dayOfWeek 0) { 
              
    $calendar .= "<td colspan='$dayOfWeek' width='50px' height='50px'>&nbsp;</td>"
         }
         
         
    $month str_pad($month2"0"STR_PAD_LEFT);
      
          while (
    $currentDay <= $numberDays) {

              
    // Seventh column (Saturday) reached. Start a new row.

              
    if ($dayOfWeek == 7) {

                   
    $dayOfWeek 0;
                   
    $calendar .= "</tr><tr>";

              }

              
    $currentDayRel str_pad($currentDay2"0"STR_PAD_LEFT);

              
    $date "$year-$month-$currentDayRel";
              
          if(
    $currentDayRel == $today_date ){  $calendar .= "<td class='day' id='today_date ' rel='$date' width='50px' height='50px' alighn='center' valign='top'align='left'><a href='appointmentdate.php?date=$date'><b>$currentDay</b></a></td>"; } 

              else { 
    $calendar .= "<td class='day' rel='$date' width='50px' height='50px' valign='top' align='left'><a href='appointmentdate.php?date=$date'>$currentDay</a></td>"; }

              
    // Increment counters

              
    $currentDay++;
              
    $dayOfWeek++;

         }
         
         

         
    // Complete the row of the last week in month, if necessary

         
    if ($dayOfWeek != 7) { 
         
              
    $remainingDays $dayOfWeek;
              
    $calendar .= "<td colspan='$remainingDays'>&nbsp;</td>"

         }
         
         
    $calendar .= "</tr>";

         
    $calendar .= "</table>";

         return 
    $calendar;

    }

    ?>

    <?php
    error_reporting
    (0);
         
    $dateComponents getdate();

         
    $month $dateComponents['mon'];                  
         
    $year $dateComponents['year'];

         echo 
    build_calendar($month,$year,$dateArray);

    ?>
    I have error reporting off because I get an undefined variable $dateArray when I echo build_calendar on the last line of the calendar code. Not sure why I am getting a error.

  2. #2
    Community Advisor bronze trophy
    fretburner's Avatar
    Join Date
    Apr 2013
    Location
    Brazil
    Posts
    1,402
    Mentioned
    45 Post(s)
    Tagged
    12 Thread(s)
    Quote Originally Posted by dxm31 View Post
    I have error reporting off because I get an undefined variable $dateArray when I echo build_calendar on the last line of the calendar code. Not sure why I am getting a error.
    You're passing $dateArray into build_calendar but you don't seem to be defining it anywhere in the code you posted. It's not actually used anywhere inside the function anyway, so you could just remove it altogether.

    As for adding the number of appointments to each day on the calendar, I'd pull the relevant data from the DB like this:

    Code PHP:
    $sql = <<<SQL 
    SELECT DAY(`start`) as `day`, count(*) as total
    FROM appointments 
    WHERE MONTH(`start`) = $month
    GROUP BY DATE(`start`)
    ORDER BY DATE(`start`) ASC 
    SQL;
     
    if(!$result = $db->query($sql)) { 
        die('There was an error while running the query [' . $db->error . ']'); 
    }
     
    $appointments = array();
    while($row = $result->fetch_assoc()){
       $appointments[ $row["day"] ] = $row['total'];
    }

    This will give you an array with an entry for each day of the month that has appointments, where the value is the number of appointments that day. Inside the build_calendar function, where it loops through each day, you can check if that day is set in the $appointments and output the number of appointments.

  3. #3
    SitePoint Zealot
    Join Date
    Feb 2011
    Location
    Colorado, USA
    Posts
    119
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi fretburner. Thanks for your help.
    When I try adding the sql query to the calendar script I get a couple of errors

    SCREAM: Error suppression ignored for
    ( ! ) Parse error: syntax error, unexpected T_SL in C:\wamp\www\Grooming\include\calendar.php on line 3
    If you remember from my last thread I am a complete noob when it comes to php and am doing all of this for fun while I start taking classes to study programming. I am trying to add the sql query to the top of the calendar.php. And then show how many appointments are scheduled for each day even if it is zero. I you don't mind will you show where in the calendar script you would have the query checking for appointments and then how you would call on that.

    Code:
    if($currentDayRel == $today_date ){  $calendar .= "<td class='day' id='today_date ' rel='$date' width='50px' height='50px' alighn='center' valign='top'align='left'><a href='appointmentdate.php?date=$date'><b>$currentDay</b></a></td>"; }  
    
              else { $calendar .= "<td class='day' rel='$date' width='50px' height='50px' valign='top' align='left'><a href='appointmentdate.php?date=$date'>$currentDay</a></td>"; }

  4. #4
    Community Advisor bronze trophy
    fretburner's Avatar
    Join Date
    Apr 2013
    Location
    Brazil
    Posts
    1,402
    Mentioned
    45 Post(s)
    Tagged
    12 Thread(s)
    Quote Originally Posted by dxm31 View Post
    When I try adding the sql query to the calendar script I get a couple of errors


    Check to make sure there aren't any spaces after $sql = <<<SQL as that could be causing the error.

    To display the number of appointments when creating the calendar, you could do something like this:
    PHP Code:
    if ( isset($appointments[$currentDay]) ) {
        
    $app_count ' ('.$appointments[$currentDay].')';
    } else {
        
    $app_count '';
    }

    if(
    $currentDayRel == $today_date ){
        
    $calendar .= "<td class='day' id='today_date ' rel='$date' width='50px' height='50px' alighn='center' valign='top'align='left'><a href='appointmentdate.php?date=$date'><b>$currentDay</b></a>$app_count</td>";
    } else {
        
    $calendar .= "<td class='day' rel='$date' width='50px' height='50px' valign='top' align='left'><a href='appointmentdate.php?date=$date'>$currentDay</a>$app_count</td>";


  5. #5
    SitePoint Zealot
    Join Date
    Feb 2011
    Location
    Colorado, USA
    Posts
    119
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks again fretburner! A few miner changes and all is working fine. And of course I still want to add a few things. I want to start figuring out how to add a next/previous month option and next/previous year. I am also stuck on appointmentdate.php where I get the date that I choose from the calendar as "2013-07-23" and how I query the table for all appointments that day the column is DATETIME format. I will see where I get with that and may ask for more help once I think I have it pretty close.

  6. #6
    SitePoint Zealot
    Join Date
    Feb 2011
    Location
    Colorado, USA
    Posts
    119
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Okay I have what I think should work for viewing all appointments on selected date. Trying to pull all records where start = $date which is formatted like Y-m-d.
    PHP Code:
    $date = new DateTime($_GET['date']); 
    $sql = <<<SQL
      SELECT client.lastName, client.firstName, dog.name, appointments.start, appointments.appointmentId, appointments.complete, client.clientId, dog.dogId
    FROM appointments LEFT JOIN client ON appointments.clientId = client.clientId
    LEFT JOIN dog ON appointments.dogId = dog.dogId 
    WHERE date(appointments.start) = 
    $date
    ORDER BY appointments.start ASC 
    SQL; 

  7. #7
    SitePoint Zealot
    Join Date
    Feb 2011
    Location
    Colorado, USA
    Posts
    119
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Not sure why this is not working.
    PHP Code:
    $date = new DateTime($_GET['date']); 
        
    $sql = <<<SQL
      SELECT client.lastName, client.firstName, dog.name, appointments.start, appointments.appointmentId, client.clientId, dog.dogId
    FROM appointments LEFT JOIN client ON appointments.clientId = client.clientId
    LEFT JOIN dog ON appointments.dogId = dog.dogId 
    WHERE DATE(appointments.start) = '
    $date'
    ORDER BY appointments.start ASC //line 13 
    SQL; 
    I am getting this error.
    Catchable fatal error: Object of class DateTime could not be converted to string in C:\wamp\www\New_Grooming\include\selectedday.php on line 13
    Have also tried $date = new Date($_GET['date']);

  8. #8
    Community Advisor bronze trophy
    fretburner's Avatar
    Join Date
    Apr 2013
    Location
    Brazil
    Posts
    1,402
    Mentioned
    45 Post(s)
    Tagged
    12 Thread(s)
    You need to use the format method of the DateTime object to return a string that you can use in your DB query:
    PHP Code:
    $date = new DateTime($_GET['date']); 
    $chosenDate $date->format('Y-m-d H:i:s'); 

  9. #9
    SitePoint Zealot
    Join Date
    Feb 2011
    Location
    Colorado, USA
    Posts
    119
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks fretburner. That worked. Kinda confused though I thought that the DATE(appointments.start) was making it so that the query was only looking at the Y-m-d of each record. Which I guess is still true since when i format $date as $date->format('Y-m-d H:i:s') it ends up 2013-07-25 00:00:00. and obviously only looks at the date when searching the records. But I do get that that now and thanks again!

    If you don't mind, I have also been trying to make it so that you can manually select the month and year on the calendar with no luck. Can you point me in the right direction if it is possible to make the calendar code i have below work this way. I thinking I could have a form at the top where you can select a month and year from dropdown and then see if the values are set before building the calendar but I would rather have a next and previous button. Having no experience this far into php I have ideas of how to go about this but always draw blanks when I actually try to make it happen.
    PHP Code:
    <?php
    date_default_timezone_set
    ('America/Denver');

    function 
    build_calendar($month,$year) {
    $today_date date("d");
    $today_date ltrim($today_date'0');


         
    // Create array containing abbreviations of days of week.
         
    $daysOfWeek = array('Sun','Mon','Tue','Wed','Thr','Fri','Sat');

         
    // What is the first day of the month in question?
         
    $firstDayOfMonth mktime(0,0,0,$month,1,$year);

         
    // How many days does this month contain?
         
    $numberDays date('t',$firstDayOfMonth);

         
    // Retrieve some information about the first day of the
         // month in question.
         
    $dateComponents getdate($firstDayOfMonth);

         
    // What is the name of the month in question?
         
    $monthName $dateComponents['month'];

         
    // What is the index value (0-6) of the first day of the
         // month in question.
         
    $dayOfWeek $dateComponents['wday'];

         
    // Create the table tag opener and day headers

         
    $calendar "<table border='1px' width='700px'>";
         
    $calendar .= "<caption>$monthName $year</caption>";
         
    $calendar .= "<tr>";

         
    // Create the calendar headers

         
    foreach($daysOfWeek as $day) {
              
    $calendar .= "<th class='header' width='50px' height='75px'>$day</th>";
         } 

         
    // Create the rest of the calendar

         // Initiate the day counter, starting with the 1st.

         
    $currentDay 1;

         
    $calendar .= "</tr><tr>";

         
    // The variable $dayOfWeek is used to
         // ensure that the calendar
         // display consists of exactly 7 columns.

         
    if ($dayOfWeek 0) { 
              
    $calendar .= "<td colspan='$dayOfWeek' width='50px' height='75px'>&nbsp;</td>"
         }
         
         
    $month str_pad($month2"0"STR_PAD_LEFT);
         include(
    "include/config.php");
         
    $sql = <<<SQL
    SELECT DAY(`start`) as `day`, count(*) as total
    FROM appointments 
    WHERE MONTH(`start`) = 
    $month
    GROUP BY DATE(`start`)
    ORDER BY DATE(`start`) ASC 
    SQL;
     
    if(!
    $result $db->query($sql)) { 
        die(
    'There was an error while running the query [' $db->error ']'); 
    }
     
    $appointments = array();
    while(
    $row $result->fetch_assoc()){
       
    $appointments$row["day"] ] = $row['total'];
    }
      
          while (
    $currentDay <= $numberDays) {

              
    // Seventh column (Saturday) reached. Start a new row.

              
    if ($dayOfWeek == 7) {

                   
    $dayOfWeek 0;
                   
    $calendar .= "</tr><tr>";

              }
              
              

              
    $currentDayRel str_pad($currentDay2"0"STR_PAD_LEFT);

              
    $date "$year-$month-$currentDayRel";
              
              if( isset(
    $appointments[$currentDay])) {
              
    $app_count ' (  '.$appointments[$currentDay].' )';
              }
              else {
              
    $app_count '';
              }
              
          if(
    $currentDayRel == $today_date ){  $calendar .= "<td class='day' id='today_date ' rel='$date' width='50px' height='75px' alighn='center' valign='top'align='left' bgcolor='#000080'><a href='appointmentdate.php?date=$date'><button type='button'>$currentDay</button></a><br />$app_count</td>"; } 

              else { 
    $calendar .= "<td class='day' rel='$date' width='50px' height='75px' valign='top' align='left' bgcolor='#737CA1'><a href='appointmentdate.php?date=$date'><button type='button'>$currentDay</button></a><br />$app_count</td>"; }

              
    // Increment counters

              
    $currentDay++;
              
    $dayOfWeek++;

         }
         
         

         
    // Complete the row of the last week in month, if necessary

         
    if ($dayOfWeek != 7) { 
         
              
    $remainingDays $dayOfWeek;
              
    $calendar .= "<td colspan='$remainingDays'>&nbsp;</td>"

         }
         
         
    $calendar .= "</tr>";

         
    $calendar .= "</table>";

         return 
    $calendar;

    }

    ?>

    <?php

         $dateComponents 
    getdate();

         
    $month $dateComponents['mon'];                  
         
    $year $dateComponents['year'];

         echo 
    build_calendar($month,$year);

    ?>
    Any help is very much appreciated.

  10. #10
    Community Advisor bronze trophy
    fretburner's Avatar
    Join Date
    Apr 2013
    Location
    Brazil
    Posts
    1,402
    Mentioned
    45 Post(s)
    Tagged
    12 Thread(s)
    Quote Originally Posted by dxm31 View Post
    Thanks fretburner. That worked. Kinda confused though I thought that the DATE(appointments.start) was making it so that the query was only looking at the Y-m-d of each record. Which I guess is still true since when i format $date as $date->format('Y-m-d H:i:s') it ends up 2013-07-25 00:00:00. and obviously only looks at the date when searching the records. But I do get that that now and thanks again!
    Yes, you're right, you could actually do $date->format('Y-m-d') as you're only comparing against the date.

    Quote Originally Posted by dxm31 View Post
    If you don't mind, I have also been trying to make it so that you can manually select the month and year on the calendar with no luck. Can you point me in the right direction if it is possible to make the calendar code i have below work this way. I thinking I could have a form at the top where you can select a month and year from dropdown and then see if the values are set before building the calendar but I would rather have a next and previous button.
    Your script currently generates a calendar based on the current month. To be able to move back and forth between different months, we can change the script to accept month and year parameters passed in the URL (e.g example.com/appointments.php?month=7&year=2013):
    PHP Code:
    $dateComponents getdate(); 
    if (!
    $month filter_input(INPUT_GET'month'FILTER_SANITIZE_NUMBER_INT)) {
        
    $month $dateComponents['mon'];
    }
    if (!
    $year filter_input(INPUT_GET'year'FILTER_SANITIZE_NUMBER_INT)) {
        
    $year $dateComponents['year'];

    Here, I'm using filter_input to pull in the values from $_GET, while at the same time filtering out non-numeric values. If the values are non-numeric, or aren't set, the IF condition will evalute to false, and the current month/year will be set as the default values.

    At the end of the build_calendar function, you need to calculate the previous/next values, and add links to the bottom of the table:
    PHP Code:
    $calendar .= "</tr>"
     
    if (
    $month == 1) {
        
    $prevMonth 12;
        
    $prevYear $year -1;
        
    $nextMonth 2;
        
    $nextYear $year;
    } elseif (
    $month == 12) {
        
    $prevMonth $month -1;
        
    $prevYear $year;
        
    $nextMonth 1;
        
    $nextYear $year 1;
    } else {
        
    $prevMonth $month -1;
        
    $prevYear $year;
        
    $nextMonth $month +1;
        
    $nextYear $year;
    }

    $calendar .= "<tr><td colspan='3'><a href='?month=$prevMonth&year=$prevYear'><< Previous</a></td><td></td><td colspan='3'><a href='?month=$nextMonth&year=$nextYear'>Next >></a></td></tr>"

    $calendar .= "</table>"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •