Date not inserting

zenAngler2000

Hi,
I'm just learning PHP and I'm setting up a sample form so somone can enter data into a background mySQL table. Whenever I enter a date however, it inserts into the database as all zeros. Can anyone tell me what I'm doing wrong? Thank you very much. My code is below.


<!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=iso-8859-1" />
<title>Form Entry for Tournaments</title>
</head>

<body>

<?php 

if (!isset($_POST['submit'])) { 
// form not submitted 
?> 

<form action="<?=$_SERVER['PHP_SELF']?>" method="post"> 
	<table border="0">
  <tr>
    <td>Tournament Name:</td>
	<td><input type="text" name="tournyName" size="40"></td>
  </tr>
  <tr>
    <td>Tournament Date:</td>
	<td><input type="text" name="tournyDate" size="15"></td>
  </tr>
  <tr>
    <td>Location:</td>
	<td><input type="text" name="Location" size="30"></td>
  </tr>
  <tr>
    <td colspan="2"><input type="submit" name="submit" value="Submit"> </td>
  </tr>

</table>
</form> 


<?php 
} 
else { 
// form submitted 
// set database server access variables: 
$host = "hostname";
$user = "user"; 
$pass = "pass"; 
$db = "TestDBJM"; 

// get form input 
    // check to make sure it's all there 
    // escape input values for greater safety 
    $tournyName = empty($_POST['tournyName']) ? die ("ERROR: Enter a Tournament Name") : mysql_escape_string($_POST['tournyName']); 
	$tournyDate = empty($_POST['tournyDate']) ? die ("ERROR: Enter a Tournament Date") : mysql_escape_string($_POST['tournyDate']); 
    $Location = empty($_POST['Location']) ? die ("ERROR: Enter an Location") : mysql_escape_string($_POST['Location']); 

// open connection 
$connection = mysql_connect($host, $user, $pass) or die ("Unable to connect!"); 

// select database 
mysql_select_db($db) or die ("Unable to select database!"); 

// create query 
$query = "INSERT INTO Tournament (tournyName, tournyDate, Location) VALUES ('$tournyName', '$tournyDate', '$Location')"; 

// execute query 
$result = mysql_query($query) or die ("Error in query: $query. ".mysql_error()); 

// print message with ID of inserted record 
echo "New record inserted with ID ".mysql_insert_id(); 

// close connection 
mysql_close($connection); 
} 
?>

</body>
</html>

masat

As you work with dates you will find there are a variety of uses that will be encountered. Specifically to your point your trouble is with the formatting of the date. MYSQL requires the date to be input be formatted as YYYY-MM-DD. Allowing your user to simply insert a date as you are will most often fail causing your users to become frustrated. Two solutions I have used formats the date for proper input.

The first is a java script date picker. It is simple to use and when you prepare the date for input it is already in the proper format. JS date pickers are easily found by a simple search.

The other method I have used is a set of drop lists. One each for the month, day year. Prep your data input and simply set the date as a new variable by combining the three variables into the proper format. I use this method if I need to do something to the date before db input. i.e. add three days to the posted date.

Either one of these methods will give you a consistently formatted date for insertion into the database.

There are of course many methods to work with dates and I suggest you build yourself a library of the various methods because you will find you will want to use different methods for different situations. There are many code examples available via a search for php date format. I highly recommend you view the php manual on this subject.

I am sure you have posted here just a basic example of your script but please be sure to error check both client side and server side and be certain the info you get is the info (and only the info) you want BEFORE you store the info to the db.

Take care.

bradgrafelman

When posting PHP code, please use the board's [PHP][/PHP] bbcode tags - they make the code much, much easier to read (as well as aiding the diagnosis of syntax errors). You should edit your post now and add these bbcode tags to help others in reading/analyzing your code.

As masat points out, controlling the format is your best bet - the method of using dropdowns is a common way to do this. Don't forget to use a function such as [man]checkdate/man to validate the submitted date before using it, though.