Solved

insert australian date format into mysql database

Posted on 2007-03-18
10
731 Views
Last Modified: 2013-12-13
I am developing a website for an australian business, and I would like to be able to enter the dates of news items into the mysql database in australian format.
I can pull a mysql date out and reformat with no problems, but how do i manage to format the date to mysql format when entering into database.

Australians use the date format Date-Month-Year
I have set up the form to show <?php echo date("d-M-Y") ?> as the output.

when it enters into database it does not work because it is in the wrong format.

this is how it is entered into database

if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "WADAInsertForm")) {
  $insertSQL = sprintf("INSERT INTO news (news_title, news_shortdesc, news_content, news_date, news_photoset) VALUES (%s, %s, %s, %s, %s)",
                       GetSQLValueString($_POST['news_title'], "text"),
                       GetSQLValueString($_POST['news_shortdesc'], "text"),
                       GetSQLValueString($_POST['news_content'], "text"),
                       GetSQLValueString($_POST['news_date'], "date"),
                       GetSQLValueString($_POST['news_photoset'], "int"));


what would i do to insert the date into the correct format
0
Comment
Question by:paulp75
  • 5
  • 5
10 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 18746355
>when it enters into database it does not work because it is in the wrong format.
yes. the only format that is valid for date data type is YYYY-MM-DD.
0
 
LVL 6

Author Comment

by:paulp75
ID: 18746475
yes but what i'd like to do is format the date into the correct format.
the form needs to have an australian date format as the input standard, as that is what the client reads and understands. no one in australia reads the date as yyyy-mm-dd. its the complete opposite to what we use.
is there a way to format the date when the form is submitted before it is entered into the database?
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 18746551
I assume that you are using php to actualy save the data into your table, so there is the place to reformat the australian format into the mysql required format.
on the other hand, in the form where you read/display the data from the table, you can format the mysql format into the australian format.
0
 
LVL 6

Author Comment

by:paulp75
ID: 18746751
thanks for the responses angellll.
thats what I'd like to know how to do. after the person presses submit, i'd like it to have the format changed to mysql format.
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 18746773
the posted data will contain the value in the format: d-M-Y

so, just reverse the order of the array:

list ( $day , $month , $year) = split ( $_POST['news_date'] ,  '-' );
$news_date = mktime ( 0, 0, 0, $month , $day  $year);

if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "WADAInsertForm")) {
  $insertSQL = sprintf("INSERT INTO news (news_title, news_shortdesc, news_content, news_date, news_photoset) VALUES (%s, %s, %s, %s, %s)",
                       GetSQLValueString($_POST['news_title'], "text"),
                       GetSQLValueString($_POST['news_shortdesc'], "text"),
                       GetSQLValueString($_POST['news_content'], "text"),
                       GetSQLValueString($news_date, "date"),
                       GetSQLValueString($_POST['news_photoset'], "int"));


however, not sure what the function GetSQLValueString does with the date value, but I guess it's correct... if not, change the line to build the $news_date to this:
$news_date = date ( 'Y-m-d' , mktime ( 0, 0, 0, $month , $day  $year));

0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 6

Author Comment

by:paulp75
ID: 18746839
looks like that type of thing would be the way to go, but its giving me unexpected T_VARIABLE error on the line where we change the format. I tried both versions.
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 18746848
let's remove the spaces before the (:

list( $day , $month , $year) = split( $_POST['news_date'] ,  '-' );
$news_date = mktime( 0, 0, 0, $month , $day  $year);

the error is a php parse error...
0
 
LVL 6

Author Comment

by:paulp75
ID: 18746879
ok feel like we're getting somewhere. i might have to take a look later. the date output as
943880400943880400
and therefore when it went into the database it still showed up as 0000-00-00
so i'll have to take a look a little later. i feel like we're on the right track though.
thanks so much for your help
0
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 18746900

then, the second version (with no spaces before the (  ):

list( $day , $month , $year) = split( $_POST['news_date'] ,  '-' );
$news_date = date( 'Y-m-d' , mktime( 0, 0, 0, $month , $day  $year));
0
 
LVL 6

Author Comment

by:paulp75
ID: 18747337
i ended up getting it right in the end. had to make some changes.
changed it to

list($day, $month, $year) = split( '-' , $_POST['news_date'] );
$news_date = "$year-$month-$day";

and it worked great.

thanks for your persistance. This will help me with a lot of projects. thanks
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

863 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

27 Experts available now in Live!

Get 1:1 Help Now