Date promlem inserting nto MySQL table

[dcjones]

Hi all.

I I solve this problem I can go to bed.

I am trying to insert a date into my table. Simple I know but I cannot get this to work.
My SQL INSERT query is this

$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 amember_bfr (id, `t_date`, title, firstname, lastname, telephone, email, location, pin, sec, carriername) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)",
                       GetSQLValueString($_POST['id'], "int"),
                       GetSQLValueString($_POST['t_date'], "date"),
                       GetSQLValueString($_POST['title'], "text"),
                       GetSQLValueString($_POST['firstname'], "text"),
                       GetSQLValueString($_POST['lastname'], "text"),
                       GetSQLValueString($_POST['telephone'], "text"),
                       GetSQLValueString($_POST['email'], "text"),
                       GetSQLValueString($_POST['location'], "text"),
                       GetSQLValueString($_POST['pin'], "int"),
					   GetSQLValueString($_POST['sec'], "int"),
                       GetSQLValueString($_POST['carriername'], "text"));

  mysql_select_db($database_baggageppin, $baggageppin);
  $Result1 = mysql_query($insertSQL, $baggageppin) or die(mysql_error());

The form I am using looks like this:

<form method="post" name="form1" action="<?php echo $editFormAction; ?>">
echo $t_date = date("Y-m-d");

Theres a lot more in the form but I left it our for easier reading.

If I echo the $t_date var "

$_SESSION['$t_date']; 

I can see the correct date, todays date.
But when I look in the data table all I see is "0000-00-00"

I know i'm missing something but what.

[tams]

My dear, I guess we are both having the same problem. If your problem gets solved could you post the code you used to solve it so I can fix mine too?

Thank you

[bradgrafelman]

Can you echo out GetSQLValueString($_POST['t_date'], "date") and paste the result to us?

[dcjones]

Hi and many thanks for looking at this problem.

I have echoed the line of code with the result as:

Array ( [member_id] => 1338013 [carriername] => Bmi [firstname] => [lastname] => [pin] => 1338013 ) 
Id:  

Date: 2007 07 27 
Title: Mr 
Firstname: d 
Lastname: d 
Telephone: d 
Email: [e-mail] 
Location: d 
Pin: 1338013 
Sec:  

Carriername: Bmi 

The above is what I would expect to see and below is the result of the echoed line

'2007 07 27'

As you can see "GetSQLValueString($_POST['t_date'], "date") " does contain the correct data but it does not get written into the table.

The var "t_date" has a data type of "DATE".

Thanks again

[dcjones]

Hi all.

I have played around with this problem and the only way I can get it to work is if I change the field type from "DATE" to "TIMESTAMP".

Can anyone see why the date does not get written to the data table when the field type is "DATE"

[NogDog]

A space-delimited set of numbers is not a valid date string in MySQL. The possible strings you could use are:

2007-07-27
2007/07/27
2007.07.27
2007@07@27
20070727

[dcjones]

Hi NogDog

I am having so much trouble with this query. This is what I have, the complete code.

The in_date field is data type "DATETIME" in the MySQL table.

The script is called from another script which passes the "$_SESSION['in_date']" content. I have echoed this SESSION var and it does contain a date in the format "2007-07-27"

<?php  session_start();
print_r ($_SESSION); ?>
<?php require_once('../../Connections/pin.php'); ?>
<?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 amember_bfr (id, in_date, title, firstname, lastname, telephone, email, location, pin, sec, carriername) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)",
                       GetSQLValueString($_POST['id'], "int"),
                       GetSQLValueString($_POST['in_date'], "date"),
                       GetSQLValueString($_POST['title'], "text"),
                       GetSQLValueString($_POST['firstname'], "text"),
                       GetSQLValueString($_POST['lastname'], "text"),
                       GetSQLValueString($_POST['telephone'], "text"),
                       GetSQLValueString($_POST['email'], "text"),
                       GetSQLValueString($_POST['location'], "text"),
                       GetSQLValueString($_POST['pin'], "text"),
                       GetSQLValueString($_POST['sec'], "text"),
                       GetSQLValueString($_POST['carriername'], "text"));

  mysql_select_db($database_pin, $pin);
  $Result1 = mysql_query($insertSQL, $pin) or die(mysql_error());

  $insertGoTo = "thankyou.php";
  if (isset($_SERVER['QUERY_STRING'])) {
    $insertGoTo .= (strpos($insertGoTo, '?')) ? "&" : "?";
    $insertGoTo .= $_SERVER['QUERY_STRING'];
  }
  header(sprintf("Location: %s", $insertGoTo));
}
GetSQLValueString($_POST['in_date'] = $_SESSION['in_date']);

?>
<form method="post" name="form1" action="<?php echo $editFormAction; ?>">
  <table align="center">
    <tr valign="baseline">
      <td nowrap align="right">Id:</td>
      <td><?php echo $id ; ?></td>
    </tr>
    <tr valign="baseline">
      <td nowrap align="right">Date:</td>
      <td><?php echo $_SESSION['in_date']; ?></td>
    </tr>
    <tr valign="baseline">
      <td nowrap align="right">Title:</td>
      <td><?php echo $title ; ?></td>
    </tr>
    <tr valign="baseline">
      <td nowrap align="right">Firstname:</td>
      <td><?php echo $firstname ; ?></td>
	  <?php  $_SESSION['firstname'] = $_POST['firstname'] ; ?>
    </tr>
    <tr valign="baseline">
      <td nowrap align="right">Lastname:</td>
      <td><?php echo $lastname ; ?></td>
    </tr>
    <tr valign="baseline">
      <td nowrap align="right">Telephone:</td>
      <td><?php echo $telephone ; ?></td>
	  <?php  $_SESSION['lastname'] = $_POST['lastname'] ; ?>
    </tr>
    <tr valign="baseline">
      <td nowrap align="right">Email:</td>
      <td><?php echo $email ; ?></td>
    </tr>
    <tr valign="baseline">
      <td nowrap align="right">Location:</td>
      <td><?php echo $location ; ?></td>
    </tr>
    <tr valign="baseline">
      <td nowrap align="right">Pin:</td>
      <td><?php echo $pin ; ?></td>
	  <?php  $_SESSION['pin'] = $_POST['pin'] ; ?>
    </tr>
    <tr valign="baseline">
      <td nowrap align="right">Sec:</td>
      <td><?php echo $sec ; ?></td>
    </tr>
    <tr valign="baseline">
      <td nowrap align="right">Carriername:</td>
      <td><?php echo $carriername ; ?></td>
	  <?php  $_SESSION['carriername'] = $_POST['carriername'] ; ?>
    </tr>
    <tr valign="baseline">
      <td nowrap align="right">&nbsp;</td>
      <td><input type="submit" value="Insert record"></td>
    </tr>
  </table>
  <input type="hidden" name="MM_insert" value="form1">
</form>