[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
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.
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
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
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
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.
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
...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
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
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.
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.
[RESOLVED] Simple but functional PHP MySQL Calendar
Reply With Quote
Originally Posted by n0x
). 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:
)
Bookmarks