Working on event calendar better way to store dates.

[bobby317]

Ok I am working on an event calendar and am making good progress but I feel I am storing my dates wrong. As of now I have them set up as varchar in the database. Which works fine the way I have them but I belive when I go to desplay them on the calendar I will have issues or atleast there is a batter way.  I think I need to set them up using the date type instead of varchar but I am not sure how to transfer the formating of my php so that it stores the right date and then allowes me to retrive the right date. Here is how I have everything set up as now

 

Here is the form collecting the info:

<script src="SpryAssets/SpryValidationTextField.js" type="text/javascript"></script>
<link href="SpryAssets/SpryValidationTextField.css" rel="stylesheet" type="text/css" />
<script src="SpryAssets/SpryValidationTextarea.js" type="text/javascript"></script>
<link href="SpryAssets/SpryValidationTextarea.css" rel="stylesheet" type="text/css" />
<form name="addEvent" action="addevent.php" method="post">

<p>
    <label for="eventName">Event Name:</label>
    <span id="sprytextfield1">
    <input type="text" name="eventName" maxlength="30" size="20" />
    <span class="textfieldRequiredMsg">Please enter a event name.</span></span></p>
    
    <p>
    <label for="date">Date:</label>
    
    <select name="month" id="date">
    	<option>January</option>
    	<option>February</option>
        <option>March</option>
        <option>April</option>
        <option>May</option>
        <option>June</option>
        <option>July</option>
        <option>August</option>
        <option>September</option>
        <option>October</option>
        <option>November</option>
        <option>December</option>
</select>
    
    <select name="day" id="date">
    	<option>1</option>
        <option>2</option>
        <option>3</option>
        <option>4</option>
        <option>5</option>
        <option>6</option>
        <option>7</option>
        <option>8</option>
        <option>9</option>
        <option>10</option>
        <option>11</option>
        <option>12</option>
        <option>13</option>
        <option>14</option>
        <option>15</option>
        <option>16</option>
        <option>17</option>
        <option>18</option>
        <option>19</option>
        <option>20</option>
        <option>21</option>
        <option>22</option>
        <option>23</option>
        <option>24</option>
        <option>25</option>
        <option>26</option>
        <option>27</option>
        <option>28</option>
        <option>29</option>
        <option>30</option>
        <option>31</option>
    </select>
    
    <select name="year" id="date">
    	<option>2010</option>
        <option>2011</option>
        <option>2012</option>
        <option>2013</option>
        <option>2014</option>
        <option>2015</option>
        <option>2016</option>
        <option>2017</option>
        <option>2018</option>
        <option>2019</option>
    </select>
    </p>
    
    <p>
    <label for="startTime">Start Time:</label>
    <span id="sprytextfield2">
    <input type="text" name="startTime" maxlength="10" size="10" />
    <span class="textfieldRequiredMsg">Please enter a start time.</span><span class="textfieldInvalidFormatMsg">Invalid format. 00:00</span></span>
    <select name="timeOfDay1" id="startTime">
    	<option>AM</option>
        <option>PM</option>
    </select>
    </p>
    
     <p>
    <label for="endTime">End Time:</label>
    <span id="sprytextfield3">
    <input type="text" name="endTime" maxlength="10" size="10" />
    <span class="textfieldRequiredMsg">Please enter a end time.</span><span class="textfieldInvalidFormatMsg">Invalid format. 00:00</span></span>
    <select name="timeOfDay2" id="endTime">
    	<option>AM</option>
        <option>PM</option>
    </select>
    </p>
    
    <p>
    <label for="description">Description:</label>
    <span id="sprytextarea1">
    <textarea name="description" rows="5" cols="50"></textarea>
    <span class="textareaRequiredMsg">Please enter your description.</span></span></p>
    
    <p>
    <input type="submit" value="Add Event" />
    <input type="hidden" name="submit" value="true" />    
    
</form>
    
    
        
    
    <script type="text/javascript">
<!--
var sprytextfield1 = new Spry.Widget.ValidationTextField("sprytextfield1");
var sprytextfield2 = new Spry.Widget.ValidationTextField("sprytextfield2", "time");
var sprytextfield3 = new Spry.Widget.ValidationTextField("sprytextfield3", "time");
var sprytextarea1 = new Spry.Widget.ValidationTextarea("sprytextarea1");
//-->
    </script>

 

PHP script to put info into database

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Add Event</title>
</head>

<link href="eventMain.css" rel="stylesheet" type="text/css" />

<body>

<?php

//set addEventForm.html for include to a varibal
$form = "addEventForm.html";

//Start code when form is submited
if( !isset($_POST['submit'])) {
include_once "$form";
} else { 

//Flag varible to track success
$okay = TRUE;

//Valadate name field
if (empty($_POST['eventName'])) {
print '<p class="error">Please the event your name.</p>';
$okay = FALSE;
include_once "$form";

//Valadate date
//Month
} elseif (empty($_POST['month'])) {
print '<p class="error">Please enter the month.</p>';
$okay = FALSE;
include_once "$form";

//Day
} elseif (empty($_POST['day'])) {
print'<p class="eroor">Please enter the day.</p>';
$okay = FALSE;
include_once "$form";

//Year
} elseif (empty($_POST['year'])) {
print'<p class="error">Please enter the year.</p>';
$okay = FALSE;
include_once "$form";

//Valadate startTime
} elseif (empty($_POST['startTime'])) {
print'<p class="error">Please enter a start time.</p>';
$okay = FALSE;
include_once "$form";

//Valadate endTime	
} elseif (empty($_POST['endTime'])) {
print'<p class="error">Please enter a end time.</p>';
$okay = FALSE;
include_once "$form";

//Valadate description
} elseif (empty($_POST['description'])) {
print'<p class="error">Please enter a description.</p>';
$okay = FALSE;
include_once "$form";

//If $okay = TRUE orginize data and input into database
} elseif ($okay == TRUE) {

//remove white spaces create varables
$eventName = trim($_POST['eventName']);
$month = trim($_POST['month']);
$day = trim($_POST['day']);
$year = trim($_POST['year']);
$startTime = trim($_POST['startTime']);
$timeOfDay1 = $_POST['timeOfDay1'];
$endTime = trim($_POST['endTime']);
$timeOfDay2 = $_POST['timeOfDay2'];
$description = trim($_POST['description']);

//Set am or pm for time
$fStartTime = "$startTime $timeOfDay1";
$fEndTime = "$endTime $timeOfDay2";

//format date
$date = "$month $day, $year"; 

//Include files for conecting to database:
$dbc = mysql_connect('rwddesign.com:3306', 'rwddesi1_bobby31', 'jessica');
mysql_select_db('rwddesi1_test');

//Define the query:
$query = "INSERT INTO events (eventID, date, startTime, endTime, eventName, description) VALUES (0, '$date', '$fStartTime', '$fEndTime', '$eventName', '$description')";

//Exicute query
if (@mysql_query($query)) {

	//Print message if secsessful
	print '<h1>Your event has been added!</h1>';

} else {
	//print message for all other errors.
		print '<h1 class="error">Could not add event because:' . mysql_error() . ' .</h1>
			   <p class="error">The query being run was: ' . $query . '</p>';
			   include_once "regform.html";
}

}


}

?>	

</body>
</html>

 

Please explain anythingyou do so I may learn and also could you give me a hint how how to go about retriving the info for my next script. Thanks

[Psycho]

You should definitely be using "date" fields to store the data. Here is an article on the subject that might help:

 

http://www.richardlord.net/blog/dates-in-php-and-mysql

[bobby317]

Thanks that does make it clearer but I still am not sure how to take the dates the user puts into the drop boxes and convert that into php and then into MySql to enter it into the database and then reverse that when retrieving the event for the calendar. Thanks again for the help. I have learned a lot from this site and these forms.

[Psycho]

Using the first example from that page, you can convert a php timestamp to a MySQL date using

$mysqldate = date( 'Y-m-d H:i:s', $phpdate );

 

And you can convert a MySQL date value to a PHP timestamp using

$phpdate = strtotime( $mysqldate );

 

So, you only have two issues left. 1) Converting the user entered date to a PHP timestamp and converting a PHP timestampt to the appropriate string for display purposes. Not knowing how you want the date displayed or how the user inputs the date, I can't say how it should be done.

 

What is the format of the user entered date and what is the format you want it displayed? E.g.

 

2-15-2010

Feb. 15th, 2010

2010-15-2

????

[bobby317]

Ok the form that collects the date is posted above and as far as displaying it I have a php calendar created and am going to have it detect the dates and then display the event name on the calendar on that date. Not quite that far as of now thought.  I guess I just need to know how to convert my user input from the form into a php time stamp so I can convert it to MySql to store in the database. Thanks again for all the help.