Storing a Date in MySQL from Flex 3?LeeMahn Jun 30, 2009 11:03 AM
Ok.. so I've got a form that's storing info in a MySQL database. All works, except that all the dates being stored are showing up as:
0000-00-00 00:00:000000-00-00 00:00:00
When I debug the program, it shows that the proper date values are being store in the variables. Just when they're being passed to MySQL do they get converted to all zeros.
Isn't there a simple way to just store the true date values?
Thanks, in advance..
1. Re: Storing a Date in MySQL from Flex 3?chris.huston.t10 Jun 30, 2009 5:21 PM (in response to LeeMahn)
How are you sending the date values to MySQL? Are you using HTTPService via PHP or remoting with AMFPHP or some other method? It will be easier to come up with a solution if you can post the part of your code that passes the date values to your backend.
Chris
2. Re: Storing a Date in MySQL from Flex 3?LeeMahn Jun 30, 2009 5:32 PM (in response to chris.huston.t10)
I'm using PHP via HTTPService.
Thanks.
3. Re: Storing a Date in MySQL from Flex 3?chris.huston.t10 Jun 30, 2009 5:40 PM (in response to LeeMahn)
There should be no problem passing the date value from Flex to PHP and then storing that in your MySQL database. Can you post your HTTPService send function and your PHP handler for storing the date in your database?
Chris
4. Re: Storing a Date in MySQL from Flex 3?LinkMc Jun 30, 2009 11:40 PM (in response to LeeMahn)
I know this might seem stupid but have you checked your column data type? I don't use PHP but I use ColdFusion with MySQL and have had the data type wrong... but I can blame that one on our DBA..lol
5. Re: Storing a Date in MySQL from Flex 3?LeeMahn Jul 1, 2009 5:12 AM (in response to chris.huston.t10)
Sure.. Here's the HTTPService send function...
private function insertData():void
{
if ( isValid() )
{
var pi:PersonalInfoVo = new PersonalInfoVo(
firstname.text,
lastname.text,
gender.text,
maidenname.text,
nickname.text,
eaddress1.text,
eaddress2.text,
website.text,
pword.text,
pwordhint.text,
bdate.selectedDate,
new Date(),
new Date(),
0,
new Date(),
'');
var event:BratsPIEvent = new BratsPIEvent("insert");
event.pi = pi;
dispatchEvent(event);
}
}
And here is the PHP code to store the data:
function insert() { global $conn; //build and execute the insert query $query_insert = sprintf("INSERT INTO `pi` (firstname,lastname,gender,maidenname,nickname,eaddress1,eaddress2,website,pword,pwordhint,bdate,joindate,lastlogin,verified,verifieddate,verifiedby) VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)" , GetSQLValueString($_REQUEST["firstname"], "text"), # GetSQLValueString($_REQUEST["lastname"], "text"), # GetSQLValueString($_REQUEST["gender"], "text"), # GetSQLValueString($_REQUEST["maidenname"], "text"), # GetSQLValueString($_REQUEST["nickname"], "text"), # GetSQLValueString($_REQUEST["eaddress1"], "text"), # GetSQLValueString($_REQUEST["eaddress2"], "text"), # GetSQLValueString($_REQUEST["website"], "text"), # GetSQLValueString($_REQUEST["pword"], "text"), # GetSQLValueString($_REQUEST["pwordhint"], "text"), # GetSQLValueString($_REQUEST["bdate"], "text"), # GetSQLValueString($_REQUEST["joindate"], "text"), # GetSQLValueString($_REQUEST["lastlogin"], "text"), # GetSQLValueString($_REQUEST["verified"], "int"), # GetSQLValueString($_REQUEST["verifieddate"], "text"), # GetSQLValueString($_REQUEST["verifiedby"], "text")# ); $ok = mysql_query($query_insert);
The issue is.. when I use an expression watcher to view the actual date values being passed to the PHP file, the show dates like this:
Date (@2191bc71)
When I expand the values in the variable, I see that the dates are all there properly (it appears that the month and day are zero-based.. but that's fine).
So, how do I get these values to turn into: YYYY-MM-DD HH:MM:SS
6. Re: Storing a Date in MySQL from Flex 3?LinkMc Jul 1, 2009 7:56 AM (in response to LeeMahn)
again I dont' know php but does this always have to be text? MySQL Date goes into as an object, MySQL will pick up the date object format it as needed. But As I see you are sending it in as text field.
GetSQLValueString($_REQUEST["verifieddate"], "text")
7. Re: Storing a Date in MySQL from Flex 3?LinkMc Jul 1, 2009 7:58 AM (in response to LeeMahn)
It should be something like this right?
$query_auto = "INSERT INTO tablename (col_name, col_date) VALUE ('DATE: Auto CURDATE()', CURDATE() )";
or this
$query_manual = "INSERT INTO tablename (col_name, col_date) VALUES ('DATE: Manual Date', '2008-7-04')";
I got this from
8. Re: Storing a Date in MySQL from Flex 3?LeeMahn Jul 1, 2009 8:14 AM (in response to LinkMc)
Actually, the PHP was all generated by Flex, itself. I created an application from a datasource. The datasource DOES show that the date fields are all set as dates.. so I'm not sure why it would default to "text".
The other issue is, this is a call to a full file as an event.. so those values will not always be current dates. They may be specifically selected dates.
As an example, the "bdate" field is a selected birth date from a field date chooser.
The output of these date() classes all look something like this: Date (@2bb74251). And that's exactly what is being passed to the PHP file to post to the SQL and finally to be inserted into the database.
When I change the value of the column in the database to "varchar (20)".. and resend the data.. I get "Thu Jul 22 00:00:00" as the value in the column. (After selecting 7/22/1965).
So it's seeing and partially understanding the data. I've tried setting the column to "date" and "datetime". I DO need one of the fields to store the actual time. Not all.. but one of them.
So bottom line is that the database is not properly using that output, or the output should be modified. And the question becomes how to modify it.
9. Re: Storing a Date in MySQL from Flex 3?tomhaskell Jul 1, 2009 8:21 AM (in response to LeeMahn)
Without seeing the rest of the app, it's hard to know what's going on here and what values are actually being passed around. However, whenever I've had to do this in the past, I go with the lowest common denominator in both systems - namely, a unix timestamp. Whilst not ideal, I've found this to be the most reliable method. In Flex, you can access this with date.time and convert back with Date(timestamp), MySQL will recognise a timestamp in an insert query, and interpret it as such.
There is still one issue with this method which took me ages to work out - in MySQL the unix timestamp is the number of seconds since 1/1/1970, whilst in flex it is the number of milliseconds. This means a lot of multiplying and dividing by 1000 (or just substr(date,0,-3) and date.'000' in PHP) to get things to come out as expected (this could even be the problem you have anyway, but as I say it's hard to know without playing around with it a bit).
Hope that helps.
10. Re: Storing a Date in MySQL from Flex 3?LeeMahn Jul 1, 2009 8:33 AM (in response to tomhaskell)
Actually.. I know what's being passed. It's what I show above.. typically something like Date(@xxxxxxxxx).
If I send it as a string.. i.e., using "bdate.selectedDate.toString()", then it shows some of the proper date info.. but again, it's a string.. not a date.
It seems to me that it doesn't need to be this hard to simply out a formatted date object.
11. Re: Storing a Date in MySQL from Flex 3?rgadiparthi Jul 1, 2009 8:36 AM (in response to LeeMahn)
did you try using Timestamp as your date type in the your database instead of varchar(20)??
12. Re: Storing a Date in MySQL from Flex 3?LeeMahn Jul 1, 2009 8:38 AM (in response to rgadiparthi)
Yes.. no luck. All Zeros
13. Re: Storing a Date in MySQL from Flex 3?tomhaskell Jul 1, 2009 8:39 AM (in response to LeeMahn)
What are you using to view the values passed around (i.e. where do you get the Date(@xxxxx) from?) That looks to me to be more like an interpreted Date object than a value. For example, Firebug in Firefox often "helpfully" interprets object for you if you spit them straight out to the console.
14. Re: Storing a Date in MySQL from Flex 3?LeeMahn Jul 1, 2009 8:49 AM (in response to tomhaskell)
Expressions debugger in Flex.
15. Re: Storing a Date in MySQL from Flex 3?LeeMahn Jul 1, 2009 8:53 AM (in response to LeeMahn)
Ok.. so there is some data here that comes through..
- Changed all the Flex var settings for "bdate" to "String".
- Changed the database column to "varchar(30)"
- Use: bdate.selectedDate.toLocaleDateString()
- I get this in the database field:
Wed Jul 21 1965
So the value being passed does contain the data. But I don't need the day.. and there doesn't seem to be a property that strips that out.
And.. I want the column to be a date, so I can do date arithemtic on it. UGH
16. Re: Storing a Date in MySQL from Flex 3?LinkMc Jul 1, 2009 9:05 AM (in response to LeeMahn)
This is how my flex function is:
private function save():void {
var obj:T20Dates = new T20Dates();
var now:Date = new Date();
var start:Date = new Date();
var end:Date = new Date();
obj.DateCreated = now;
obj.StartTime = start as Date;
obj.EndTime = end as Date;
roDates.save(obj);
}
Now my MySQL has this as the data types
hope this helps
17. Re: Storing a Date in MySQL from Flex 3?LeeMahn Jul 1, 2009 9:16 AM (in response to LinkMc)
Where are you getting "roDates"?
18. Re: Storing a Date in MySQL from Flex 3?LeeMahn Jul 1, 2009 9:21 AM (in response to LinkMc)
And actually.. have you created "T20Dates" as a value object somewhere?
19. Re: Storing a Date in MySQL from Flex 3?chris.huston.t10 Jul 1, 2009 11:34 AM (in response to LeeMahn)
You need to format your date string that you are sending to PHP/MySQL into YYYY-MM-DD HH:MM:SS format. Use a DateFormatter to convert Flex's format to a format that you can insert in your database. See the code below.
Chris
private function insertData():void
{
if ( isValid() )
{
var mysqlDate:DateFormatter = new DateFormatter();
mysqlDate.formatString = "YYYY-MM-DD JJ:NN:SS";
var pi:PersonalInfoVo = new PersonalInfoVo(
firstname.text,
lastname.text,
gender.text,
maidenname.text,
nickname.text,
eaddress1.text,
eaddress2.text,
website.text,
pword.text,
pwordhint.text,
mysqlDate.format(bdate.selectedDate),
mysqlDate.format(new Date()),
mysqlDate.format(new Date()),
0,
new Date(),
'');
var event:BratsPIEvent = new BratsPIEvent("insert");
event.pi = pi;
dispatchEvent(event);
}
}
20. Re: Storing a Date in MySQL from Flex 3?LeeMahn Jul 1, 2009 11:54 AM (in response to chris.huston.t10)
That's what I've been toying with.. and it looks like you're right.
However, how do I get this all back into a "Date()" object.. so it can be declared as a date within the VO and so I can use it as a normal date to do arithematic on it?
Thanks!
21. Re: Storing a Date in MySQL from Flex 3?LinkMc Jul 1, 2009 12:03 PM (in response to LeeMahn)
T20Dates.as is my VO
package "cfc.T20Dates")] [ Bindable] public class T20Dates{ public var GarageSaleID:Number = 0; public var SaleDate:Date = null; public var StartTime:Date = null; public var EndTime:Date = null; public var DateCreated:Date = null; public var CreatedBy:String = ""; public var CreatedProgram:String = ""; public var DateUpdated:Date = null; public var UpdatedBy:String = ""; public var UpdatedProgram:String = ""; public function T20Dates(){ } } } dto{ [RemoteClass(alias=
My Remote Object in my index.MXML
<mx:RemoteObject <mx:method </mx:RemoteObject>
I don't have format my date as a string to get into MYSQL, I insert a date object and MySQL picks that up.
I cast my obj.DateCreated value object to a date
var now:Date = new Date(); ... obj.DateCreated = now;
I use ColdFusion, but the object is created and passed into from object in as to MySQL
22. Re: Storing a Date in MySQL from Flex 3?LinkMc Jul 1, 2009 12:08 PM (in response to LeeMahn)
please take a look at casting.
var start:Date = new Date(); obj.StartTime = start as Date;
23. Re: Storing a Date in MySQL from Flex 3?chris.huston.t10 Jul 1, 2009 5:45 PM (in response to LeeMahn)
You need to parse the MySQL date and then use the parse to create a date for your VO. You can use the function below to convert a MySQL datetime variable (YYYY-MM-DD HH:MM:SS) into a Flex date.
private function parse_mysql(mysql_date:String):Date {
var split_mysql:Array = mysql_date.split(" ");
var date_array:Array = split_mysql[0].split("-");
var time_array:Array = split_mysql[1].split(":");
var new_date:Date = new Date(date_array[0], date_array[1]-1, date_array[2], time_array[0], time_array[1], time_array[2], 0);
return new_date;
}
In your VO, just assign your date property to the return value of the above function.
So, send the Flex date as a string using the DateFormatter, then when you read it back from MySQL as a string, convert the string back into a Flex date. This is the basic flow I use for my apps. I hope this solves your problem.
Chris
24. Re: Storing a Date in MySQL from Flex 3?LeeMahn Jul 2, 2009 12:40 PM (in response to chris.huston.t10)
Ok.. found the easiest answer. Here's exactly what I did.. and it works:
// START: Convert Dates to MySQL friendly
var formattedDate:DateFormatter = new DateFormatter();
formattedDate.formatString = "YYYY-MM-DD JJ:NN:SS";
var now:Date = new Date();
// END: Convert Dates to MySQL friendly
So now we've created a new variable called "formattedDate", typed as a DateFormatter and instantiated it as a new DateFormatter.
Then, we define the DateFormatter's output, using the "formatString".. and in this case, I've set it to what MySQL likes.
Next, I created a new variable called "now", typed as a "Date", and populated it with today's date.
Now here are two different date outputs, using this formattedDate formatting:
formattedDate.format(bdate.selectedDate),
formattedDate.format(now),
The first is a formatter that properly formats the date returned from a "DateField" field type called "bdate".. which returned "1965-08-02 00:00:00", since I selected August 2, 1965 as the date in the field chooser. The zero's are added, because it does not see a timestamp coming back with the date.
The second is a formatted date of today's date.. which returns "2009-07-02 12:01:34"
All that's left now is to type the output back to a date type.. and VOILA!
Whew! I KNEW it didn't have to be that hard to do. And it wasn't!
Thanks to everyone.. and I hope this helps others.
Lee