By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,165 Members | 840 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,165 IT Pros & Developers. It's quick & easy.

Matt Kruse Calendar Popup date format help please.

P: 5
I have been trying my best to display a chosen date as dd-mm-yyyy but insert it into the mysql database as yyyy-mm-dd, I know it must be simple but no matter what I try I can't get it to work.

At the moment the date is displayed as dd-MM-yyyy but of course this won't add to the mysql database, if I change the display to yyyy-MM-dd then it displays in that format (which I don't want) but will add to the database OK.

I had thought that I could change one of these sections (quoted below) to reformat the date but whatever I tried just failed, I am posting here as it may be something I need to change in the Javascript rather than the PHP/MYSQL parts.

Thanks for any help offered.

Gordon

case "date":
$theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
break;
if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "form1")) {
$insertSQL = sprintf("INSERT INTO readings (windyboy, white, `date`, meter) VALUES (%s, %s, %s, %s)",
GetSQLValueString($_POST['windyboy'], "int"),
GetSQLValueString($_POST['white'], "int"),
GetSQLValueString($_POST['date'], "date"),
GetSQLValueString($_POST['meter'], "int"));


Full Code:

[PHP]
<?php require_once('../Connections/wind.php'); ?>
<?php
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "")
{
$theValue = (!get_magic_quotes_gpc()) ? addslashes($theValue) : $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 readings (windyboy, white, `date`, meter) VALUES (%s, %s, %s, %s)",
GetSQLValueString($_POST['windyboy'], "int"),
GetSQLValueString($_POST['white'], "int"),
GetSQLValueString($_POST['date'], "date"),
GetSQLValueString($_POST['meter'], "int"));

mysql_select_db($database_wind, $wind);
$Result1 = mysql_query($insertSQL, $wind) or die(mysql_error());
}
[/PHP]

?><!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" />

[CODE-JAVASCRIPT]
<SCRIPT LANGUAGE="JavaScript" SRC="/CalendarPopup.js"></SCRIPT>
<SCRIPT LANGUAGE="JavaScript">
var cal = new CalendarPopup();
</SCRIPT>
[/code]

<title>Untitled Document</title>
</head>

[CODE-HTML]
<body>
<form id="form1" name="form1" method="POST" action="<?php echo $editFormAction; ?>">
<p>windyboy
<input name="windyboy" type="text" id="windyboy" />
</p>
<p>white
<input name="white" type="text" id="white" />
</p>
<p>date
<input name="date" type="date" id="date" />
<A HREF="#"
onClick="cal.select(document.form1.date,'anchor1', 'dd-MM-yyyy'); return false;"
NAME="anchor1" ID="anchor1">select</A>
</p>
<p>meter
<input name="meter" type="text" id="meter" />
</p>
<input type="hidden" name="MM_insert" value="form1">
submit
<input type="submit" name="Submit" value="Submit" />
<input type="hidden" name="hiddenField" />
</form>
</body>
</html>
[/code]
Jan 6 '08 #1
Share this Question
Share on Google+
6 Replies


Expert 100+
P: 785
Hi Gordon,
you need to change the SQL-statement!

If you don't use prepared statements in your SQL, which is always better (avoids malicious code injection, is faster) then you must care about
the formatting of the date yourself in your SQL. So I would advise you to use prepared statement instead of plain SQL, assigning the date value after you formatted it.
But here is how to do it with plain SQL

You wrote:

Expand|Select|Wrap|Line Numbers
  1. "INSERT INTO readings (windyboy, white, `date`, meter) VALUES (%s, %s, %s, %s)"
for example the date "06-12-2006" then must be formatted with:
Expand|Select|Wrap|Line Numbers
  1. str_to_date('06-12-2006', '%d-%m-%Y')
the function str_to_date() is an SQL function.

You should ALWAYS use this function, because a database administrator can change the default date format in mySql anytime, without warning, and if you have not used the function, suddenly your code stops working! (or worse, it still works, but suddenly writes wrong dates, like swapping the numbers for day and month)

So your code should be changed to:
Expand|Select|Wrap|Line Numbers
  1. "INSERT INTO readings (windyboy, white, `date`, meter) VALUES (%s, %s, str_to_date(%s, '%%m/%%d/%%Y'), %s)"
and all works.

But as I said,
if you would use prepared statements, you can simply use
(new SimpleDateFormat(myOriginalPattern)).parse(myOrigi nalDateString)
before assigning it with PreparedStatement.setDate(). No need to "hardcode" the date conversion inside the SQL anymore, and no need to care about database internals (the code works unchanged with mySql or Oracle or ..., independent of what the DBA configured), the JDBC library cares for it and makes all needed conversions internally!
Jan 6 '08 #2

P: 5
Hi Chaarmann

Thanks for the help, I changed the SQL statement to
Expand|Select|Wrap|Line Numbers
  1. $insertSQL = sprintf("INSERT INTO readings (windyboy, white, `date`, meter) VALUES (%s, %s, str_to_date(%s, '%%m/%%d/%%Y'), %s)",
  2.                        GetSQLValueString($_POST['windyboy'], "int"),
  3.                        GetSQLValueString($_POST['white'], "int"),
  4.                        GetSQLValueString($_POST['date'], "date"),
  5.                        GetSQLValueString($_POST['meter'], "int"));
  6.  
Also tried %%d/%%m/%%Y but get the same result each time saying date value is NULL

The display format is dd/mm/yyyy

No doubt I am not understanding something correctly, could you expand on where I am going wrong?

Thanks

Gordon

PS I will have a look at the prepared statements as suggested.
Jan 6 '08 #3

Expert 100+
P: 785
Hi Gordon,
sorry, my answer may confuse you. I wrote how to use prepared statements in Java, not how to use them in PHP. I don't know enough about PHP to tell you how to do in this language, you must research about it yourself.

But the example with plain SQL should also work in PHP.

And all the common things I said about prepared statements are true, independent of the used language. Especially "malicious code injection" is possible, because in your code you don't check the submitted values from the webpage!!!
Just imagine I insert (for example in the date field) something like
",null,null); select * from secret_table; delete * from password_table; insert into dummy_table (".
into the web-form and then press "submit" on your internet page: The SQL would run and nothing would crash. Only afterward all your passwords are deleted, and maybe some data from your secret table pops up on the result page that I can see!
Jan 6 '08 #4

Expert 100+
P: 785
Sorry,
"str_to_date(%s, '%%m/%%d/%%Y'), " was an error I made. This would parse american style (first month, then day), but you needed it in european style (first day, then month). So
'%%d/%%m/%%Y" is correct and should have worked. But I am not so sure if I remember PHP syntax correctly. "%%" is converted to a single "%" in the output, isn't it?

Just try printing the SQL-string on the screen before executing it,
your output should be something like
Expand|Select|Wrap|Line Numbers
  1. INSERT INTO readings (windyboy, white, `date`, meter) VALUES ('dummy', 'dummy', str_to_date('31/12/2007', '%d/%m/%Y'), 'dummy');
then try to run the SQL above directly with a tool like mySqlQueryBrowser, SqlYog (or whatever) directly on the database and send me the error, if any occurs.
Jan 6 '08 #5

Expert 100+
P: 785
I just saw something strange. You just wrote:

The display format is dd/mm/yyyy
But in your code, you wrote:
'dd-MM-yyyy'
if you have your date-string this format (as given in your code), then you should replace "/" with "-" in your SQL, so the string output would be something like:
str_to_date('06-12-2006', '%d-%m-%Y')
Jan 6 '08 #6

P: 5
That sorted it, needed to change each / to -, why didn't I see that myself!

Thank you very much for your help, I now intend to sort out the validation/security issue, on this database it makes no difference at all as it is only for testing but the next one's it would matter.

Again, thanks for your invaluable help.

Regards

Gordon

I just saw something strange. You just wrote:



But in your code, you wrote:


if you have your date-string this format (as given in your code), then you should replace "/" with "-" in your SQL, so the string output would be something like:
str_to_date('06-12-2006', '%d-%m-%Y')
Jan 6 '08 #7

Post your reply

Sign in to post your reply or Sign up for a free account.