13 Replies Latest reply on Nov 25, 2009 8:06 AM by lathoudero

    Best way to retrieve/store date Flex <> PHP <> MySQL?

    lathoudero

      Hi All,

       

      I have a question about storing/retrieving a date-field from the MySQL database, using Flash Builder 2 & Zend AMF.

       

      This is as much as I know:

      MySQL Database stores date in this format: yyyy-mm-dd hh:mm:ss

      And Flex will not recognise that as a "date", instead it will suggest this value is an "object".

      With "Configure return type", I can manually set this field to "date", but it will not work retrieving of updating the date as MySQL will not understand the standard date-format it'll receive,

       

      I guess, the best solution would be if I could tell the MySQL Server how the standard date-format should be, but I have not found this option. Can anyone confirm this?

       

      The remaining options are:

      1. Format the date in the SQL-query
      2. Format the date in the PHP-function sending/retrieving
      3. Format the date in Flex, befor assigning it to a date-field...

       

      Can someone tell me the BEST practice to handle date-objects? I guess it's the date_format function in MySQL....

      I'm trying to build an app with over 3200 fields, shared over 60 tables, of which many are date-fields.

       

      Would be awesome if I could just use SELECT * FROM myTable, and all would be fine with date-fields and all...

       

      Any tips?

        • 1. Re: Best way to retrieve/store date Flex <> PHP <> MySQL?
          mewk Level 3

          hi lathoudero,

           

          Converting times between flex, php, and mysql can be a pain in the *** . One method is to reduce all time inputs/outputs into unix time (the time since 1970 jan 1). You can bypass php conversions altogether and simply use the mysql functions UNIX_TIMESTAMP() and FROM_UNIXTIME(). See all the goodness attached in the zip below. cherio,

           

          - e

          1 person found this helpful
          • 2. Re: Best way to retrieve/store date Flex <> PHP <> MySQL?
            David_F57 Level 5

            Dates are always a pain, I ran into this a while ago rather than put up my mods to suit what I was doing this link I found very useful at the time(sic).

             

            http://forum.sephiroth.it/showthread.php?t=8764

             

            I went with the date formatter idea.

             

            David

            1 person found this helpful
            • 3. Re: Best way to retrieve/store date Flex <> PHP <> MySQL?
              mewk Level 3

              Thanks for the link David. If the DateFormatter class can interpret an sql date string, which it seems it can, then this might be an ideal solution to the problem. I'm definitately holding onto this link for future exploration. Thanks!

               

              - e

              • 4. Re: Best way to retrieve/store date Flex <> PHP <> MySQL?
                David_F57 Level 5

                hi Mewk,

                 

                We can only get better by sharing knowledge, and at the end of the day thats what it is really all about, you've made some great contributions to these forums and its good to see people who aren't afraid to share. Even when someone gets it wrong(i'm good at that ) its all food for thought.

                 

                David.

                • 5. Re: Best way to retrieve/store date Flex <> PHP <> MySQL?
                  lathoudero Level 1

                  Guys, thanks for the help!

                   

                  I'm surprised though Flex <> Zend AMF <> MySQL didn't make this obvious step...

                  I'll be right back with another post, as I have an idea I'd like to test first.

                  • 6. Re: Best way to retrieve/store date Flex <> PHP <> MySQL?
                    David_F57 Level 5

                    you think dates are bad wait till you decide to read n write images to the table

                     

                    David.

                    • 7. Re: Best way to retrieve/store date Flex <> PHP <> MySQL?
                      Mayank (Adobe) Adobe Employee

                      When an operation's return type is configured in Flash Builder, using the

                      Configure Return Type wizard date, datetime, timestamp and time columns are

                      converted to String. The same conversion is done if the Generate Sample PHP

                      from Database wizard is used.

                       

                      Using string to communicate the date information between client and server

                      reduces the amount of manipulation required on the server before the data

                      can be used in a query.

                       

                      While the intent of these workflows was to point a user towards using

                      String, that doesn't seem to have been self apparent.

                       

                      Do you have any recommendations on how you would have liked to see this

                      work?

                       

                      --

                      Mayank Kumar

                      Computer Scientist, Flash Builder

                      • 8. Re: Best way to retrieve/store date Flex <> PHP <> MySQL?
                        David_F57 Level 5

                        Hi Mayank,

                         

                        using unix epoch is the smoothest way to deal with dates. Can't go wrong with an integer representation of time.

                         

                        David.

                        • 9. Re: Best way to retrieve/store date Flex <> PHP <> MySQL?
                          lathoudero Level 1

                          I'm confused. This is what I have:

                          On my MySQL database, I've created a VIEW with the query that I need. I thought it'd generally be efficient to create views for every 'Form' that I need.

                           

                          This is my VIEW v_person

                          SELECT p.`person_id`, p.`name`, p.`lastname`, DATE_FORMAT(p.`birthdate`,'%d/%m/%Y %r') as birthdate
                          FROM person p
                          

                           

                          Now I have a simple table v_person returning

                          person_idnamelastname
                          birthdate
                          0JohnDoenull
                          1JaneDid23/08/1976 12:00:00 AM
                          2JunoDoh01/04/2001 12:00:00 AM

                           

                          "Great! Now I can set up my query once, returning the date in whatever format Flex likes."

                          ....

                          Not.

                           

                          This is the standard generated PHP function to get information by ID. This works fine.

                               public function getV_personByID($itemID) {
                                    
                                    $stmt = mysqli_prepare($this->connection, "SELECT * FROM $this->tablename where person_id=?");
                                    $this->throwExceptionOnError();
                                    
                                    mysqli_bind_param($stmt, 'i', $itemID);          
                                    $this->throwExceptionOnError();
                                    
                                    mysqli_stmt_execute($stmt);
                                    $this->throwExceptionOnError();
                                    
                                    mysqli_stmt_bind_result($stmt, $row->person_id, $row->name, $row->lastname, $row->birthdate);
                                    
                                    if(mysqli_stmt_fetch($stmt)) {
                                         return $row;
                                    } else {
                                         return null;
                                    }
                               }
                          

                           

                           

                          This is the standard generated Form in Flex:

                          One note to make when using RETURN TYPE,

                          is that in when I use person_id=0 , I can change "birthdate type" from OBJECT to DATE, because date is null.

                          When I use person_id=1 , I CAN'T change "birthdate type" as it defaults to "STRING".

                          Since I want to use a dateField in my Form, I changed birthdate type to "DATE". (Even though I now understand that return type IS in fact a string)

                          <fx:Script>
                               <![CDATA[
                                    import mx.controls.Alert;
                                    import mx.formatters.DateFormatter;
                                         protected function button_clickHandler(event:MouseEvent):void
                                    {
                                         getV_persontestByIDResult.token = vpersontestService.getV_persontestByID(parseInt(itemIDTextInput.text));
                                    }
                               ]]>
                          </fx:Script>
                          <fx:Declarations>
                                    <vpersontestservice:VpersontestService id="vpersontestService" fault="Alert.show(event.fault.faultString + '\n' + event.fault.faultDetail)" showBusyCursor="true"/>
                               <s:CallResponder id="getV_persontestByIDResult" result="v_persontest = getV_persontestByIDResult.lastResult as V_persontest"/>
                               <valueObjects:V_persontest id="v_persontest" person_id="{parseInt(person_idTextInput.text)}" />
                               <!-- Place non-visual elements (e.g., services, value objects) here -->
                          </fx:Declarations>
                          <mx:Form defaultButton="{button}">
                               <mx:FormItem label="ItemID">
                                    <s:TextInput id="itemIDTextInput"/>
                               </mx:FormItem>
                               <s:Button label="GetV_persontestByID" id="button" click="button_clickHandler(event)"/>
                          </mx:Form>
                          <mx:Form>
                               <mx:FormHeading label="V_persontest"/>
                               <mx:FormItem label="Birtdate">
                                    <mx:DateField id="birthdateDateField" selectedDate="@{v_persontest.birthdate}" />
                               </mx:FormItem>
                               <mx:FormItem label="Last name">
                                    <s:TextInput id="lastnameTextInput" text="@{v_persontest.lastname}"/>
                               </mx:FormItem>
                               <mx:FormItem label="First name">
                                    <s:TextInput id="nameTextInput" text="@{v_persontest.name}"/>
                               </mx:FormItem>
                               <mx:FormItem label="person_id">
                                    <s:TextInput id="person_idTextInput" text="{v_persontest.person_id}"/>
                               </mx:FormItem>
                          </mx:Form>
                          

                           

                          Works great! Except that it can't handle the date field:

                          TypeError: Error #1034: Type Coercion failed: cannot convert "10/21/1984 12:00:00 AM" to Date.
                               at com.adobe.serializers.utility::TypeUtility$/assignProperty()[C:\perforceGAURAVP01\depot\flex\ide_builder\com.adobe.flexbuilder.dcrad\serializers\src\com\adobe\serializers\utility\TypeUtility.as:534]
                               at com.adobe.serializers.utility::TypeUtility$/convertToStrongType()[C:\perforceGAURAVP01\depot\flex\ide_builder\com.adobe.flexbuilder.dcrad\serializers\src\com\adobe\serializers\utility\TypeUtility.as:497]
                               at com.adobe.serializers.utility::TypeUtility$/convertResultHandler()[C:\perforceGAURAVP01\depot\flex\ide_builder\com.adobe.flexbuilder.dcrad\serializers\src\com\adobe\serializers\utility\TypeUtility.as:371]
                               at mx.rpc.remoting::Operation/http://www.adobe.com/2006/flex/mx/internal::processResult()[E:\dev\trunk\frameworks\projects\rpc\src\mx\rpc\remoting\Operation.as:316]
                               at mx.rpc::AbstractInvoker/http://www.adobe.com/2006/flex/mx/internal::resultHandler()[E:\dev\trunk\frameworks\projects\rpc\src\mx\rpc\AbstractInvoker.as:313]
                               at mx.rpc::Responder/result()[E:\dev\trunk\frameworks\projects\rpc\src\mx\rpc\Responder.as:56]
                               at mx.rpc::AsyncRequest/acknowledge()[E:\dev\trunk\frameworks\projects\rpc\src\mx\rpc\AsyncRequest.as:84]
                               at NetConnectionMessageResponder/resultHandler()[E:\dev\trunk\frameworks\projects\rpc\src\mx\messaging\channels\NetConnectionChannel.as:547]
                               at mx.messaging::MessageResponder/result()[E:\dev\trunk\frameworks\projects\rpc\src\mx\messaging\MessageResponder.as:235]
                          

                           

                          Somehow SOMETHING wants to convert "10/21/1984 12:00:00 AM" to Date.

                          Where does it fail? I feel like I'm SO close, but making a faceplant right before the finish.

                           

                          ok.

                          I'm by no means an expert, so let me know if I'm doing something wrong, but this is how I'd expect it to work.

                           

                          Can someone tell me how I should use a dateField instead? Where should I make the stringToDate and dateToString conversion?

                          • 10. Re: Best way to retrieve/store date Flex <> PHP <> MySQL?
                            David_F57 Level 5

                            instead of assigning the returned date directly

                             

                                 <mx:FormItem label="Birtdate">
                                      <mx:DateField id="birthdateDateField" selectedDate="@{v_persontest.birthdate}" />
                                 </mx:FormItem>
                            
                            selecteddate="mydateformatfuntion(v_persontest.birthdate)"
                            mydateformatfunction(value:string):Date
                            {
                                 var df:DateFormatter= new DateFormatter();
                                     df.formatstring ="YYYY-MM-DD'   //or whatever your mysql date string is this is standard date field format
                                     return new Date(df.format(value))
                            }

                            • 11. Re: Best way to retrieve/store date Flex <> PHP <> MySQL?
                              lathoudero Level 1

                              Thanks for info!

                              It helps indeed in showing the retrieved date in my DateField... but it would be a one-way solution, as it doesn't communicate back.

                              I've finally found a SURPRISINGLY SIMPLE way to have a bi-directional dateToStrong and stringToDate conversion...

                              And I'm surpriced this function isn't located in a more OBVIOUS location, like the Date() class....

                               

                               

                              Check this out:

                              No classes, no additional functions, no dateformatters or split-string-to-array...

                              easy as pie, IF you know where it's hidden.

                               

                              DateField.dateToString(date,format)
                              DateField.stringToDate(string,format)
                              

                               

                              Just apply this to 2 locations:

                              1)  In your declarations (get value from DateField component, and send to service.
                                  Note the standard MySQL Format! 'YYYY-MM-DD'
                              <fx:Declarations>
                                   <vpersontestservice:VpersontestService id="vpersontestService"
                                                             fault="Alert.show(event.fault.faultString + '\n' + event.fault.faultDetail)"
                                                             showBusyCursor="true"/>
                                   <s:CallResponder id="getV_persontestByIDResult"
                                              result="v_persontest = getV_persontestByIDResult.lastResult as V_persontest"/>
                                   <valueObjects:V_persontest id="v_persontest"
                                                  person_id="{parseInt(person_idTextInput.text)}"
                                                  birthdate="{DateField.dateToString(birthdateDateField.selectedDate,'YYYY-MM-DD')}"/>
                                   <s:CallResponder id="updateV_persontestResult"/>
                              </fx:Declarations>

                               

                               

                              2) Convert date String to Date in the DateField. Note again the MySQL Date format
                              <mx:DateField id="birthdatedatumDateField"
                                               selectedDate="{DateField.stringToDate(v_persontest.birthdate,'YYYY-MM-DD')}"
                                               formatString="DD/MM/YYYY"/>

                               

                              Voila!!!

                              You can use DateField.stringToDate(string, format) of DateField.dateToString(date, format) anywhere, where you need to change date to string and vice versa.

                              You don't even need a DateField component! You'll have to import the class though...

                               

                              But honestly, should't these functions be under Date()?

                              Like: Date.stringToDate(string,format) or Date.dateToString(date, format)

                              • 12. Re: Best way to retrieve/store date Flex <> PHP <> MySQL?
                                hneelu Adobe Employee

                                An ECR for the same has been logged in Flash Builder [ https://bugs.adobe.com/jira/browse/FB-23566 ]

                                 

                                Regards

                                Harpreet