Tuesday, February 3, 2009

How to display any database table in a Flex 3 Datagrid

The project described in this post is made to display any table from a PostgreSQL or MySQL database in Adobe Flex 3.
(note: only text based database info will work, so it won’t work with e.g. pictures stored in a database)

Here’s an overview of the technologies used in this project:

database2flex.jpg


It works as follows:

1. The Flex 3 application sends out a HttpRequest to a php webpage.
2. The php script queries the database and selects all elements from a table
3. The same php script outputs the table as a XML-formatted page and returns this to the Flex application
4. The Flex application interprets the XML page and creates a table output using a Datagrid.

Download here all the source files. In order to make it work with your site do the following:

Make sure you have a webserver running PHP and MySQL or PostgreSQL

- Copy DataSmart.swf and DataSmart.html to your webdirectory
- Copy either GetDataSmart_MySQL.php or GetDataSmart_PostgreSQL.php to your webdirectory, depending on the database technology you’re using.
- Rename the .php to GetDataSmart.php
- Edit the .php page to connect to your database and to query the table you want to display.

open DataSmart.html in your webbrowser and everything should work (Adobe Flash plugin needed!)

  1. mac

    Tuesday, March 29, 2011 - 06:28:13

    thanks for the code. I got this page just when it is required. I will try now and see what happens. Between, will the flex reflect any changes made in the database ?

  2. mac

    Tuesday, March 29, 2011 - 09:47:48

    hey.. I tried to implement this in my ubuntu system. But when I open the html page in firefox I am getting blank page. nothing is displaying. and when I open the swf file I am getting a error :2148 security error. Between I copied the files to /var/www/

    please help me on this.
    It would be nice if you make a video tutorial of this and upload in youtube :)

    thanks

  3. Flip

    Tuesday, March 29, 2011 - 13:44:42

    Hi Mac, did you do all the 4 necessary steps described above?

    - Copy DataSmart.swf and DataSmart.html to your webdirectory
    - Copy either GetDataSmart_MySQL.php or GetDataSmart_PostgreSQL.php to your webdirectory, depending on the database technology you’re using.
    - Rename the .php to GetDataSmart.php
    - Edit the .php page to connect to your database and to query the table you want to display.

    I tested this code again and can confirm it works for me.

    Flip

  4. mac

    Wednesday, March 30, 2011 - 09:51:03

    hii,
    here is what I did,

    I have windows machine SP3 and firefox.

    I installed WAMP.
    I created a small database and table in sql
    I checked my phpadmin file and is working great.
    I copied your files to C:wampwww
    - I edited the php file and since I dont have a sql password set, I kept the password field as “” .

    -I changed everything username, db, table etc.

    - I changed the php file name as u mentioned.

    Then I open the html page and nothing is displaying just blank.

    and when I open the swf file in firefox it is showing Error: 2148 which is security error.

    any help is highly appreciated.

  5. Flip

    Wednesday, March 30, 2011 - 11:12:20

    Hi mac,

    what happens when you open the GetDataSmart.php from your browser? (I suggest you do this in Internet explorer, as this browser doesn’t parse the xml, I know chrome does parse the xml tags) - the url on your machine for this is probably http://localhost/GetDataSmart.php (make sure WAMP server is fully up and running)

    the xml should start with:

    <?xml version=”1.0” encoding=”ISO-8859-1”?>
    <entries><entry>

    if you don’t get this xml data, you’ll probably see an error message which could help to determine what goes wrong.

    Best,
    Flip

  6. mac

    Wednesday, March 30, 2011 - 11:23:42

    –> When I goto http://localhost/GetDataSmart.php in firefox, my database table is displaying just like a text in one line.

    But now I am confused when you said the xml should start with:

    <?xml version=”1.0” encoding=”ISO-8859-1”?>
    <entries><entry>

    I have to write this xml code ?

    –When I open the html file in firefox I am getting a blank white screen
    but when I open same file in MS word, it is displaying the GUI but with a error:2148 . But no data are displaying.

    When I open the swf file in firefox I am getting the same GUI and with same error, But no data is displaying.

    I did not yet install a flex builder.

    Please let me know what is the xml thing you have mentioned ?

  7. Flip

    Wednesday, March 30, 2011 - 13:18:02

    Hi Mac,

    what happens when you open http://localhost/GetDataSmart.php is that firefox parses the xml output from the php site.. if you have internet explorer then it won’t be parsed. Alernatively you can view the source of the php generated page in firefox, that might also give you the unparsed xml.(I don’t have firefox installed here so I can’t try that - it works for chrome)

    If this doesn’t get you a step closer, please try the following

    Edit GetDataSmart.php file to have only the following code:

    —————————-

    <?php
    echo “<?xml version=”1.0” encoding=”iso-8859-1”?><entries><entry><testcolumn_1>test1</testcolumn_1></entry><entry><testcolumn_1>test2</testcolumn_1></entry></entries>”
    ?>

    —————————-

    If you run the html then, it should display a very simple test-table. If not, then we know your problem lies in the php to flex communication (could be due to file permissions or other WAMP configurations)

    If this does work, then the connection between php and your database is what is most likely to be causing the problem.

    -Flip

  8. mac

    Thursday, March 31, 2011 - 07:31:31

    ok I have done as you said, But its just not able to bring it up.

    So are you sure its “connection between php and your database is what is most likely to be causing the problem. ” ?

    Becuause one I open the html page in browser and when I check my browser error console, there is 1 error caught called “DetectFlashver is not defined” and is showing line 50. any idea ?

  9. Flip

    Thursday, March 31, 2011 - 09:35:35

    Hi mac,

    The ‘DetectFlashVer’ error you are getting seems to be related to the automatic flash-version check (+update) usually distributed with Flex. I added these 2 files (AC_OETags.js and playerProductInstall.swf) to the project zip file (http://blog.flipwork.nl/fp-content/attachs/database2flex.zip)

    If this doesn’t fix your issues, please check whether you have the latest Flash player installed or not.

    -Flip

  10. mac

    Thursday, March 31, 2011 - 14:22:46

    hey, It fixed my DetectFlashVer error. Thanks for that.

    I am also able to open html page and able to see the flash GUI. But no data is displaying, Instead a pop-up window will appear( a flash window) and says error # 2148.Do you know this error? I know this is some security issue of flash accessing the local content. I read in some forum “On installing DreamWeaver CS5 and Flash CS5, I got rid of the SecurityError: Error #2148.”

    Is this right ? did you do this ? Or do you have any other better solution ?
    I am using win XP

  11. Flip

    Thursday, March 31, 2011 - 16:43:16

    Hi mac,

    your issues were so unclear to me that I just downloaded and installed WAMP myself.. And I am not able to replicate your issues.. It all works perfectly fine for me.. So please verify the following:

    - WAMP server is properly installed and running (both apache and mysql)
    - you renamed GetDataSmart_mysql.php to GetDataSmart.php
    - you edited GetDataSmart.php (setting the correct database, table, user and password) and placed it in your www root folder
    - if you open http://localhost/GetdataSmart.php you see the content of your table on 1 line (firefox/chrome, in internet explorer you’ll see it as unparsed xml) –> if you are able to see this, the access to your database from php is set up correctly… if not, then you probably haven’t set the permissions for accessing the database correctly

    - have the DataSmart.html and the DataSmart.swf file in your www root folder and http://localhost/DataSmart.html should display the table.

    finally - did you check if you have the latest flash player installed or not?

    Flip

  12. mac

    Friday, April 1, 2011 - 05:16:59

    Thanks!! Everything working fine. I am able to display all the data in the front end.
    Actually I was not trying “http://localhost/DataSmart.html ” I was trying directly to open the file from the browser which gave me the error. i was trying “file:///C:/wamp/www/DataSmart.html ” which gave me the error 2148.

    Thanks for your help, I highly appreciate it.
    c u around. :)