Converting separate fields to date

Question

markaman 0 Newbie Poster

16 Years Ago

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);
?>

php

3 Contributors
7 Replies
174 Views
2 Years Discussion Span
Latest Post 13 Years Ago Latest Post by luespi

darkagn 315 Veteran Poster

16 Years Ago

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 315 Veteran Poster

16 Years Ago

$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.

Reply to this topic

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.

markaman 0 Newbie Poster · Answer 1 · 2008-11-13T21:59:02+00:00

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);
?>

markaman 0 Newbie Poster · Answer 2 · 2008-11-14T09:30:49+00:00

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

markaman 0 Newbie Poster · Answer 3 · 2008-11-18T02:00:51+00:00

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

luespi 0 Newbie Poster · Answer 4 · 2010-12-04T07:52:50+00:00

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

luespi 0 Newbie Poster · Answer 5 · 2010-12-04T07:54:06+00:00

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.