Dreamweaver insert record date format dd-mm-yyy in mysql database

Posted on 2011-04-22
Last Modified: 2012-05-11
Hi all,

I have a dreamweaver insert record behavior on my page, in the form is a text field where the user puts in a date which is formatted as dd/mm/yyyy.
However the mysql database is formatted as yyyy-mm-dd.
What do i have to change to achieve this to put it correctly in the database?
if (!function_exists("GetSQLValueString")) {
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") 
  if (PHP_VERSION < 6) {
    $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";
    case "long":
    case "int":
      $theValue = ($theValue != "") ? intval($theValue) : "NULL";
    case "double":
      $theValue = ($theValue != "") ? doubleval($theValue) : "NULL";
    case "date":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
    case "defined":
      $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
  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 ``1`` (name, birthdate) VALUES (%s, %s)",
                       GetSQLValueString($_POST['name'], "text"),
                       GetSQLValueString($_POST['birthdate'], "date"));

  mysql_select_db($database_Data_oei, $Data_oei);
  $Result1 = mysql_query($insertSQL, $Data_oei) or die(mysql_error());

  $insertGoTo = "next.php";
  if (isset($_SERVER['QUERY_STRING'])) {
    $insertGoTo .= (strpos($insertGoTo, '?')) ? "&" : "?";
    $insertGoTo .= $_SERVER['QUERY_STRING'];
  header(sprintf("Location: %s", $insertGoTo));

Open in new window

Question by:Rene-D
    LVL 14

    Assisted Solution

    please add the following line or modify

    case "date":
          $theValue = ($theValue != "") ? "'" . date('Y-m-d',$theValue) . "'" : "NULL";

    Author Comment

    Unfortuntatly this sulution is not working,
    a date submitted as 19-04-2011 which is april 19 2011 is stored in the database as  1970-01-01.
    LVL 70

    Accepted Solution

    Hi Rene-D,

    The solution above should work, but you need to also modify the insert statement to force the value to go through the check above and test the output before allowing the insert to proceed.  It's trickier and I'm on a mobile device so I can't easily deal with the code right now.

    The way I usually handle this is to ensure the user cannot enter a date except in the way that MySQL requires it by using a JavaScript calendar popup or by breaking the date field up into month / day / year drop downs which are then recombined by my script before being sent to the database.  

    Author Comment

    Hi jason 1178 and others,

    Okay i understand it but i am not that much a programmer.
    I guess for me it is the best solution to breaking the date field up into month / day / year drop downs.
    But how can i put this together a one field in the database? In other words, how do i modify the code?

    Author Comment

    Found it!
    made three form fields for day month, year, and altered the code:

    GetSQLValueString($_POST['birthdate'], "date"));


    GetSQLValueString( $_POST['year'] . '-' . $_POST['month'] . '-' .$_POST['day'], "date"));

    Thanx !!

    Author Closing Comment

    The solution was not complete, but set me on the right track

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Introduction In this installment of my SQL tidbits, I will be looking at parsing Extensible Markup Language (XML) directly passed as string parameters to MySQL 5.1.5 or higher. These would be instances where LOAD_FILE (…
    All XML, All the Time; More Fun MySQL Tidbits – Dynamically Generate XML via Stored Procedure in MySQL Extensible Markup Language (XML) and database systems, a marriage we are seeing more and more of.  So the topics of parsing and manipulating XM…
    In this sixth video of the Xpdf series, we discuss and demonstrate the PDFtoPNG utility, which converts a multi-page PDF file to separate color, grayscale, or monochrome PNG files, creating one PNG file for each page in the PDF. It does this via a c…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now