I have 3 text fields named "month, day, and year"
And 1 hidden field named "Date2"
My goal is to have the 3 fields combine to enter as date in Mysql.
I've tried this many different ways, the latest having the value of Date2 as <?php echo $year,'-', $month,'-', $day; ?> Below is my complete code.
Any help to show me the proper way would be appreciated.

<?php require_once('Connections/MyHouse.php'); ?>

<?php
$year=$_POST['year'];
$month=$_POST['month'];
$day=$_POST['day'];


?>
<?php
if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "")
{
 $theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;

 $theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);

 switch ($theType) {
   case "text":
     $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
     break;
   case "long":
   case "int":
     $theValue = ($theValue != "") ? intval($theValue) : "NULL";
     break;
   case "double":
     $theValue = ($theValue != "") ? "'" . doubleval($theValue) . "'" : "NULL";
     break;
   case "date":
     $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
     break;
   case "defined":
     $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
     break;
 }
 return $theValue;
}
}

$editFormAction = $_SERVER['PHP_SELF'];
if (isset($_SERVER['QUERY_STRING'])) {
 $editFormAction .= "?" . htmlentities($_SERVER['QUERY_STRING']);
}

if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "form1")) {
 $insertSQL = sprintf("INSERT INTO Bday (`Date`) VALUES (%s)",
                     GetSQLValueString($_POST['Date2'], "date"));

 mysql_select_db($database_MyHouse, $MyHouse);
 $Result1 = mysql_query($insertSQL, $MyHouse) or die(mysql_error());
}

mysql_select_db($database_MyHouse, $MyHouse);
$query_Recordset1 = "SELECT bdayid, `Date`, `day`, `month`, `year` FROM Bday";
$Recordset1 = mysql_query($query_Recordset1, $OrchardHouse) or die(mysql_error());
$row_Recordset1 = mysql_fetch_assoc($Recordset1);
$totalRows_Recordset1 = mysql_num_rows($Recordset1);
?><!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>Untitled Document</title>
</head>

<body>
<form action="<?php echo $editFormAction; ?>" id="form1" name="form1" method="POST">
 <table width="800" border="0">
   <tr>
     <td>date</td>
     <td><label>
    <input name="month" type="text" id="month" size="2" maxlength="2" />
/
<input name="day" type="text" id="day" size="2" maxlength="2" />
/
<input name="year" type="text" id="year" size="4" maxlength="4" />
     </label></td>
   </tr>
   <tr>
     <td>&nbsp;</td>
     <td>&nbsp;</td>
   </tr>
   <tr>
     <td>&nbsp;</td>
     <td>&nbsp;</td>
   </tr>
   <tr>
     <td><input name="Date2" type="hidden" id="Date2" value="<?php echo $year,'-', $month,'-', $day; ?>" /></td>
     <td><label>
       <input type="submit" name="submit" id="submit" value="Submit" />
     </label></td>
   </tr>
 </table>

 <input type="hidden" name="MM_insert" value="form1" />
</form>
<?php
echo $year,'-', $month,'-', $day; ?>


<?php echo $Date2
?>

</body>
</html>
<?php
mysql_free_result($Recordset1);
?>

Recommended Answers

All 7 Replies

In MySQL, you need to enter a date in the format "YYYY-MM-DD", so with your three variables you can make the date string in that format using the date and mktime functions. Something like this should work:

// date needs format and timestamp
// mktime needs hours, minutes, seconds, month, day, year
$date = date( 'Y-m-d', mktime( 12, 0, 0, $month, $day, $year ));

Then you use the $date string that you have created to insert into the DATE field in your database.

Darkagn
Thank you for taking the time to respond.
No matter what I input, Mysql gets the date as "1999-11-30" Perhaps I'm not getting it.
My latest code below.

<?php require_once('Connections/OrchardHouse.php'); ?>

<?php
$year=$_POST['year'];
$month=$_POST['month'];
$day=$_POST['day'];

$date = date( 'Y-m-d', mktime( 12, 0, 0, $month, $day, $year ));

?>
<?php
if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "")
{
 $theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;

 $theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);

 switch ($theType) {
   case "text":
     $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
     break;
   case "long":
   case "int":
     $theValue = ($theValue != "") ? intval($theValue) : "NULL";
     break;
   case "double":
     $theValue = ($theValue != "") ? "'" . doubleval($theValue) . "'" : "NULL";
     break;
   case "date":
     $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
     break;
   case "defined":
     $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
     break;
 }
 return $theValue;
}
}

$editFormAction = $_SERVER['PHP_SELF'];
if (isset($_SERVER['QUERY_STRING'])) {
 $editFormAction .= "?" . htmlentities($_SERVER['QUERY_STRING']);
}

if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "form1")) {
 $insertSQL = sprintf("INSERT INTO Bday (`Date`) VALUES (%s)",
                      GetSQLValueString($_POST['Date2'], "date"));

 mysql_select_db($database_OrchardHouse, $OrchardHouse);
 $Result1 = mysql_query($insertSQL, $OrchardHouse) or die(mysql_error());
}

mysql_select_db($database_OrchardHouse, $OrchardHouse);
$query_Recordset1 = "SELECT bdayid, `Date`, `day`, `month`, `year` FROM Bday";
$Recordset1 = mysql_query($query_Recordset1, $OrchardHouse) or die(mysql_error());
$row_Recordset1 = mysql_fetch_assoc($Recordset1);
$totalRows_Recordset1 = mysql_num_rows($Recordset1);
?><!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>Untitled Document</title>
</head>

<body>
<form action="<?php echo $editFormAction; ?>" id="form1" name="form1" method="POST">
 <table width="800" border="0">
   <tr>
     <td>date</td>
     <td><label>
     <input name="month" type="text" id="month" size="2" maxlength="2" />
/
<input name="day" type="text" id="day" size="2" maxlength="2" />
/
<input name="year" type="text" id="year" size="4" maxlength="4" />
     </label></td>
   </tr>
   <tr>
     <td>&nbsp;</td>
     <td>&nbsp;</td>
   </tr>
   <tr>
     <td>&nbsp;</td>
     <td>&nbsp;</td>
   </tr>
   <tr>
     <td><input name="Date2" type="hidden" id="Date2" value="<?php echo $date ?>" /></td>
     <td><label>
       <input type="submit" name="submit" id="submit" value="Submit" />
     </label></td>
   </tr>
 </table>

 <input type="hidden" name="MM_insert" value="form1" />
</form>
<?php
//echo $year,'-', $month,'-', $day; ?>


<?php //echo $Date2
?>

</body>
</html>
<?php
mysql_free_result($Recordset1);
?>
$insertSQL = sprintf("INSERT INTO Bday (`Date`) VALUES ('%s')", 
                     GetSQLValueString($_POST['Date2'], "date"));

Note the single quotes around the %s. I think this should fix your problem.

I get the error
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '1999-11-30'')' at line 1

I have server version 5.0.45

Still unable to solve my problem. I thought maybe the error on my last post was due to my mysql version.
I've since installed 5.1.29 (along with PDP 5.2.6) on another machine and still get the error.

I tried to research what '%s' dose but couldn't find anything.

It appears to me that $month, $day, $year are not getting their values until submit, hence the date entered into mysql is whatever the PHP page is loaded with.

Dose anybody have any ideas?
Thank you

Please do tell if you solved this and how did you solve it!!!

my 2 cents:


THIS INFORMATION WA SGIVEN BY: TONYB

I'd recommend if you're dealing with dates you use a mysql date type: http://dev.mysql.com/doc/refman/5.0/en/datetime.html

Why over complicate things by storing unix time then having to do calculations that way. There is a lot of power here: http://dev.mysql.com/doc/refman/5.0/...functions.html . Give you a lot more options as far as reports also.


All it's a matter of doing here is formatting your user given values into a single date string that you can run through your report queries.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.