Help with inserting dates into MySQL database

[mysty]

I have a short list box with choices for entering dates into a MySQL database.  Attached is a portion of the code for the list box.

 

How many days?
        <select name="plus_date" id="plus_date">
          <option value=" " selected="selected">Please choose one</option>
          <option value="<?php echo Date("Y-m-d");?>">30 Days</option>
          <option value="NOW() + INTERVAL 90 DAY">90 Days</option>
        </select>

 

The code for 30 days is just a test piece of code which puts in todays date in the correct database column and that works like it should.  What I want to be able to do, is to put in the column, the date plus 30 days or the date plus 90 days.  So, what code do I need for the 90 days line of code?  Once I get the correct code, I will change the "30 day" line so it inputs the date plus 30 days.

[btherl]

Does this code work?  Do you get any errors?  Can you show us how you use the option value in php?  In particular, show us the mysql query you are using.

 

You may have problems because a date must be quoted, but an expression like now() + interval 90 day must not be quoted.

[mysty]

Here is the code for the whole page.  Guess I should have put that in the first message instead of just a portion. This part of the code:  <?php echo Date("Y-m-d");?>  was put there just to test whether anything was being put into the database and that part does work, although it is not what I want in the long run.  What I want is, not todays date, but the date plus 30, 60, or 90 days.

 

When I run the page (fill in the form and choose one option from the list box, then submit) the database is filled with the firstname and lastname.  If I choose 30 or 60 days (see code below) I get todays date in the DB.  If I choose 90 days, I get in the plus_date column "0000-00-00".  I do not get any errors when the page is run.

 

 

<?php require_once('Connections/conn_tester.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"] == "newuser")) {
  $insertSQL = sprintf("INSERT INTO `user` (firstname, lastname, plus_date) VALUES (%s, %s, %s)",
                       GetSQLValueString($_POST['firstname'], "text"),
                       GetSQLValueString($_POST['lastname'], "text"),
                       GetSQLValueString($_POST['plus_date'], "date"));

  mysql_select_db($database_conn_tester, $conn_tester);
  $Result1 = mysql_query($insertSQL, $conn_tester) or die(mysql_error());
}

mysql_select_db($database_conn_tester, $conn_tester);
$query_Recordset1 = "SELECT plus_date FROM `user`";
$Recordset1 = mysql_query($query_Recordset1, $conn_tester) or die(mysql_error());
$row_Recordset1 = mysql_fetch_assoc($Recordset1);
$totalRows_Recordset1 = mysql_num_rows($Recordset1);

if (!session_id()) session_start();
if (isset($_POST["Submit"]))     {
  $_SESSION["fname"] = "".((isset($_POST["firstname"]))?$_POST["firstname"]:"")  ."";
}

if (!session_id()) session_start();
if ($_SERVER["REQUEST_METHOD"] == "POST")     {
  $_SESSION["lname"] = "".((isset($_POST["lastname"]))?$_POST["lastname"]:"")  ."";
}
?><!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>Registration Page - CPG Database</title>
<table width="750" border="0" align="center">
  <tr>
    <td><div align="center" class="pageName style4">New User Registration </div></td>
  </tr>
  <tr>
    <td class="subHeader"><form action="<?php echo $editFormAction; ?>" method="POST" name="newuser" class="services-red" id="newuser">
      <label><span class="style3"><br />
      </span><br />
      <br />
      <br />
       First Name 
       <input name="firstname" type="text" id="firstname" size="70" />
      </label>
      <p>
        <label> Last Name
        
        <input name="lastname" type="text" id="lastname" size="70" />
        </label>
      </p>
      <p>
        <label></label>
        How many days?
        <select name="plus_date" id="plus_date">
          <option value=" " selected="selected">Please choose one</option>
          <option value="<?php echo Date("Y-m-d");?>">30 Days</option>
          <option value="<?php echo Date("Y-m-d");?>">60 Days</option>
	  <option value="NOW() + INTERVAL 90 DAY">90 Days</option>
        </select>
</p>
      <p>
        <input type="submit" name="Submit" value=" Register New User " />
      </p>
      
      
      
      <input type="hidden" name="MM_insert" value="newuser">
    </form>
    <p> </p>    </td>
  </tr>
  <tr>  </tr>
</table>
<br />
<br />
</body>
</html>
<?php
mysql_free_result($Recordset1);
?>

[fenway]

btherl is right...

 

case "date":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;

 

You're quoting all of your values, including date, unless it's a blank string... I guess you could easily check for the presence of non-date characters, parentheses (i.e. a function), or simply "mark" your field in the case as "non-quotable" (I prefer the latter).

[mysty]

I am not sure I understand your answer.  How do I do that?

ie "mark" your field in the case as "non-quotable"

[fenway]

A variety of ways... another parameter, tacking on a special character to the name of the field, or the field type, etc.

[mysty]

Sorry for being so dense.  Do you mean I need to change my recordset?

[fenway]

No, I mean you need to upgrade your function that builds your query.

[mysty]

How would I do that?  In what part of the code listed above is that?

[fenway]

Depends how you want to tackle it... I'd suggest adding a boolean parameter, bNoQuote, which by default is null/false unless you specify it (i.e. your current functionality; then simply check this flag when you decide to quote or not quote your strings.. it's that simple.