?
Solved

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

Posted on 2011-04-22
6
Medium Priority
?
1,155 Views
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?
<?php
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";
      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 ``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

0
Comment
Question by:Rene-D
  • 4
6 Comments
 
LVL 14

Assisted Solution

by:Kalpan
Kalpan earned 300 total points
ID: 35447019
please add the following line or modify

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

Author Comment

by:Rene-D
ID: 35447451
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.
0
 
LVL 70

Accepted Solution

by:
Jason C. Levine earned 450 total points
ID: 35448162
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.  
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

Author Comment

by:Rene-D
ID: 35449704
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?
0
 

Author Comment

by:Rene-D
ID: 35450143
Found it!
made three form fields for day month, year, and altered the code:

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

into

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


Thanx !!
0
 

Author Closing Comment

by:Rene-D
ID: 35450156
The solution was not complete, but set me on the right track
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article, I’ll talk about multi-threaded slave statistics printed in MySQL error log file.
Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses
Course of the Month17 days, 13 hours left to enroll

830 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