24 Replies Latest reply on Jul 2, 2009 12:40 PM by LeeMahn

    Storing a Date in MySQL from Flex 3?

    LeeMahn

      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: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 Level 3

          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 Level 1

            I'm using PHP via HTTPService.

             

            Thanks.

            • 3. Re: Storing a Date in MySQL from Flex 3?
              chris.huston.t10 Level 3

              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

                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 Level 1

                  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 Level 1

                    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 Level 1

                      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 http://www.ntchosting.com/mysql/insert-date.html

                      • 8. Re: Storing a Date in MySQL from Flex 3?
                        LeeMahn Level 1

                        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

                          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 Level 1

                            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

                              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 Level 1

                                Yes.. no luck.  All Zeros

                                • 13. Re: Storing a Date in MySQL from Flex 3?
                                  tomhaskell Level 1

                                  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 Level 1

                                    Expressions debugger in Flex.

                                    • 15. Re: Storing a Date in MySQL from Flex 3?
                                      LeeMahn Level 1

                                      Ok.. so there is some data here that comes through..

                                       

                                      1. Changed all the Flex var settings for "bdate" to "String".
                                      2. Changed the database column to "varchar(30)"
                                      3. Use: bdate.selectedDate.toLocaleDateString()
                                      4. 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 Level 1

                                         

                                         

                                         

                                         

                                        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

                                         

                                        datetime.png

                                         

                                        hope this helps

                                        • 17. Re: Storing a Date in MySQL from Flex 3?
                                          LeeMahn Level 1

                                          Where are you getting "roDates"?

                                          • 18. Re: Storing a Date in MySQL from Flex 3?
                                            LeeMahn Level 1

                                            And actually.. have you created "T20Dates" as a value object somewhere?

                                            • 19. Re: Storing a Date in MySQL from Flex 3?
                                              chris.huston.t10 Level 3

                                              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 Level 1

                                                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 Level 1

                                                  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 id="roDates" destination="ColdFusion" source="cfc.T20DatesGateway" showBusyCursor="true">
                                                  
                                                  <mx:method name="save" result="roDates.getAllByGarageSale(ItemObj);"/>
                                                  
                                                  </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 Level 1

                                                    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 Level 3

                                                      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 Level 1

                                                        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