www.webdeveloper.com
Results 1 to 15 of 15

Thread: [RESOLVED] Simple but functional PHP MySQL Calendar

  1. #1
    Join Date
    Dec 2011
    Posts
    163

    resolved [RESOLVED] Simple but functional PHP MySQL Calendar

    I have been putting this little coding thing up for a little while until now.

    I want to be able to make a simple, yet very functional, PHP calendar that can connect to my MySQL db and retrieve stored events.

    The columns I figured I would need would be something along...

    - eventID
    - year
    - month
    - day
    - title
    - description

    I have found this simple PHP calendar code HERE and would like to add some code to it.

    I also want to be able to have previous and next month buttons (maybe I could generate 3 months of events on a page, but only display the current month, previous and next buttons would change the display value?) so users can see what they missed and what's coming up.

    I need some starter points for this, I think I know how I'm going to do this, but I just got home from work and my brain is off

    - Thanks

  2. #2
    Join Date
    Mar 2012
    Posts
    21
    create a new table in your database

    Code:
    CREATE TABLE `cal` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `date` int(11) NOT NULL DEFAULT '0',
    `title` text COLLATE utf8_bin NOT NULL,
    `description` text COLLATE utf8_bin NOT NULL,
    PRIMARY KEY (`id`)
    ) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=1 ;
    when inserting a new event, use strototime( "$day/$month/$year" ) for the date, if using droplist numerics

    then the code goes something like

    Code:
    $day = date( "d", time() );
    $month = date( "m", time() );
    $year = date( "y", time() );
    
     while ( $day_num <= $days_in_month ) 
    
     { 
    
    	if( mysql_query("select date from cal where date >='". strtotime( "$day/$month/$year 00:00:00" ) ."' and date <= '". strtotime( ($day+1) ."/$month/$year 00:00:00" ) ."'") ) {
    		
    		echo "<td style:'background:red;'> $day_num </td>"; 
    		
    	} else { 
    	 
     		echo "<td> $day_num </td>"; 
     		
     	}
    
     $day_num++; 
    
     $day_count++;
    
    
    
     //Make sure we start a new row every week
    
     if ($day_count > 7)
    
     {
    
     echo "</tr><tr>";
    
     $day_count = 1;
    
     }
    
     }

  3. #3
    Join Date
    Dec 2011
    Posts
    163
    Quote Originally Posted by n0x View Post
    when inserting a new event, use strototime( "$day/$month/$year" ) for the date, if using droplist numerics
    I'm still trying to figure this line out, do you mean when inserting a new events from the database, or when I submit a new event via form to database? And you're gonna have to go more layman with the "droplist numerics" part.

    Thanks.

  4. #4
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,322
    I would [pretty strongly] advise not using an INT column in the DB for the date/time, instead using a DATE or DATETIME or TIMESTAMP type (whichever is most applicable to what you want to use it for), so that you can then make use of the DBMS's built-in date/time functions instead of always having to convert from unix timestamp to a date/time type in order to use them.
    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

  5. #5
    Join Date
    Dec 2011
    Posts
    163
    Wouldn't I just switch around the variables to ("$year/$month/$day") to do that?

  6. #6
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,322
    Quote Originally Posted by Dragonfire2008 View Post
    Wouldn't I just switch around the variables to ("$year/$month/$day") to do that?
    Basically, yes. You might want to validate them first with checkdate() (though note which order the date parts get supplied to it, just to keep us all confused ). I find the 'YYYY-mm-dd' format is the most portable across DBMS's, so would supply that to the query. So, to cover all my bases, I might do:
    PHP Code:
    if(checkdate($month$day$year)) {
      
    $date sprintf("%04-%02-%02"$year$month$day);
      
    $sql "blah blah blah ".mysql_real_escape_string('$date')." blah blah blah"// probably shouldn't be necessary to escape it, but just in case
      // etc....
    }
    else {
       
    // handle invalid date condition

    And (hopefully obviously) you could arrange the logic flow differently to do all your validations and error-handling first, or however you like to organize things.
    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

  7. #7
    Join Date
    Dec 2011
    Posts
    163
    Digging deeper into this dynamic MySQL calendar, I have encountered a bit of a problem which I know is gonna be a simple fix (i hope), but I can't seem to figure it

    Code:
    if( mysql_query("SELECT date FROM calendar WHERE date ='". strtotime( "$year-$month-$day_num" ) ."'") ) {
    		
    		if ($day_count == 6 || $day_count == 7) {
    			
    			echo "<td class='hasEvent'> $day_num  <div class='calendarNotesLeft'>" .$row_dynCalendar['title']. "</div></td>"; 
    		} else {
    			
    			echo "<td class='hasEvent'> $day_num  <div class='calendarNotes'>" .$row_dynCalendar['title']. "</div></td>";
    			
    		}
    		
    	} else { 
    	 
     		echo "<td> $day_num </td>"; 
    		
    	}
    I think I'm using the wrong statements here. I'm trying to get my php to call the database at each day that is created and see if there is a stored event, if so, then it will create the class necessary to host the information. But all I'm getting right now is my first event repeated every day instead of each day having it's own event, or none if there isn't one.

    Need a little more help, I will try to provide a better description if they need one.

    -Thanks.

  8. #8
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,322
    mysql_query() only runs the query and gets a resource to the result set -- it does not return any actual data, so you'll need to use something like mysql_num_rows() in your if() to determine if any evens were found for that date. Then you'll need to loop on mysql_fetch_assoc() (or other 'fetch' function of your choice) to get the data for each event.
    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

  9. #9
    Join Date
    Dec 2011
    Posts
    163
    ...and then use mysql_data_seek() to set the internal data pointer back to 0 so it can be used again to find the next event, yes?

  10. #10
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,322
    Quote Originally Posted by Dragonfire2008 View Post
    ...and then use mysql_data_seek() to set the internal data pointer back to 0 so it can be used again to find the next event, yes?
    Hmm...really? Admittedly, I've not looked at everything here in detail, so I'm not sure why you'd need to loop through the results again?
    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

  11. #11
    Join Date
    Dec 2011
    Posts
    163
    wouldn't the mysql_fetch_assoc() have to be reset for each day since it's ran multiple times?

  12. #12
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,322
    You should be able to get everything you need in one query, either using a date comparison or a "between" clause in the query. Then just dump the result into an array of array indexed by the dates, and output it something like this:
    PHP Code:
    <?php
    $date 
    '2012-09-14';

    $sql "
    SELECT `id`, `date`, `title`, `description`
    FROM `cal`
    WHERE `date` > '"
    .mysql_real_escape_string($date)."'
    ORDER BY `date` ASC -- probably not really necessary in this case
    "
    ;
    $result mysql_query($sql) or die(mysql_error()."<br />\n$sql"); // for test only, make it "nicer" for real code
    $data = array();
    while(
    $row mysql_fetch_assoc($result)) {
        
    $data[$row['date']][] = $row;
    }
    // just to see what the data looks like for now:
    echo "<pre>".print_r($data,1)."</pre>\n";

    // At that point, you can set up a loop to output the table cells, and for each cell, grab the sub-array from $data for that date (if any):
    ?>
    <table>
    <tr>
    <?php
    $startTime 
    strtotime($date);
    for(
    $dayInterval 0$dayInterval 7$dayInterval++) {
        
    $thisDate date('Y-m-d'strtotime("+$dayInterval days"$startTime));
        echo 
    "<td>";
        if(isset(
    $data[$thisDate])) {
            foreach(
    $data[$thisDate] as $event) {
                echo 
    "<h3>".$event['title']."</h3>\n";
                echo 
    "<p>".$event['description']."</p>\n";
            }
        }
        echo 
    "</td>\n";
    }
    ?>
    </tr>
    </table>
    (All untested, of course, and likely containing typos, etc. )
    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

  13. #13
    Join Date
    Dec 2011
    Posts
    163
    Ok, so I've been looking at new code (new to me anyways, I'm not exactly fluent) and have not managed to get anything to work even properly. But I did however manage to find one "bug" in my code, which was the $date variable, inside of getting 2012-09-17, I was getting 1302588744 (or whatever) from that, so I used mktime(0,0,0,$month,$day_num,$year) instead and fixed it. I have managed to get my first event on the list to show like normal (woohoo!), but it seems to stop there and not bother looking for anymore events, I tried throwing a mysql_data_seek of 0 in there to reset the pointer, but that wasn't the case.

    Here is my following code...
    Code:
     $thisDate = date("Y-m-d", mktime(0,0,0,$month,$day_num,$year));
    	if(mysql_real_escape_string($thisDate) == $row_dynCalendar['date']) {
    		
    		if ($day_count == 6 || $day_count == 7) {
    			
    			echo "<td class='hasEvent'>$day_num<div class='calendarNotesLeft'>".$row_dynCalendar['title']."</div></td>"; 
    		} else {
    			
    			echo "<td class='hasEvent'>$day_num<div class='calendarNotes'>".$row_dynCalendar['title']."</div></td>";
    			
    		}
    		
    	} else { 
    	 
     		echo "<td>$day_num</td>"; 
     		
     	}
    I know it isn't what has been replied to me, but it is working for the most part.

    Just need to get this code to keep looking for events, not just stopping at one.

    Thanks.

  14. #14
    Join Date
    Dec 2011
    Posts
    163
    UPDATE: I have gotten all my events to show now, but I have a seemingly bigger problem, it's hard to explain for me, but when I add another event (row in the db), PHP will repeat each day one more time before going to the next.

    This is my code...
    PHP Code:
    while ( $day_num <= $days_in_month 

     { 
     
    $thisDate date("Y-m-d"mktime(0,0,0,$month,$day_num,$year));
        
        do if(
    $row_dynCalendar['date'] == mysql_real_escape_string($thisDate)) {
            
            if (
    $day_count == || $day_count == 7) {
                
                echo 
    "<td class='hasEvent'>$day_num<div class='calendarNotesLeft'>".$row_dynCalendar['title']."</div></td>";
                
            } else {
                
                echo 
    "<td class='hasEvent'>$day_num<div class='calendarNotes'>".$row_dynCalendar['title']."</div></td>";
                
            }
            
        } else { 
         
             echo 
    "<td>$day_num</td>";
             
         } while (
    $row_dynCalendar mysql_fetch_assoc($dynCalendar));
        
    mysql_data_seek($dynCalendar,0);
             
     
    $day_num++; 

     
    $day_count++;

     
    //Make sure we start a new row every week

     
    if ($day_count 7)

     {

     echo 
    "</tr><tr>";

     
    $day_count 1;

     }

     } 
    And here is a screenshot of what's going on front-side...
    http://tricityfullgospelministries.o...ndar_issue.jpg

    Any help would be much appreciated...

  15. #15
    Join Date
    Dec 2011
    Posts
    163
    Finally, I have gotten this code to work correctly. Here is the following code...

    Code:
     while ( $day_num <= $days_in_month ) 
    
     {
    	 
     //get date
     
     $date = date("Y-m-d", mktime(0,0,0,$month,$day_num,$year));
     
     //connect to database
     
     $sql = "
     SELECT `date`, `title` FROM `calendar` WHERE `date` = '".mysql_real_escape_string($date)."'";
    
     $result = mysql_query($sql, $tcfgm) or die(mysql_error()."<br />$sql");
     
     $row = mysql_fetch_assoc($result); 
     $totalRows = mysql_num_rows($result);
    		
    		if($totalRows > 0) {
    		
    			if ($day_count == 6 || $day_count == 7) {
    				
    				echo "<td class='hasEvent'>$day_num<div class='calendarNotesLeft'>".$row['title']."</div></td>";
     
     				mysql_data_seek($result,0);
    				
    			} else {
    				
    				echo "<td class='hasEvent'>$day_num<div class='calendarNotes'>".$row['title']."</div></td>";
     
     				mysql_data_seek($result,0);
    				
    			}
    		
    		} else {
    			
    			echo "<td>$day_num</td>";
    			
    		}
     
     mysql_free_result($result);
    
     $day_num++; 
    
     $day_count++;
    
     //Make sure we start a new row every week
    
     if ($day_count > 7)
    
     {
    
     echo "</tr>";
     echo "<tr>";
    
     $day_count = 1;
    
     }
    
     }
    The only thing not included here is the previous and next month buttons, but that's an easy js display switch function

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

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



Recent Articles