Interacting with a Database

This recipe shows how you can use the Dynamic Image Server to pull data from a database which is being updated as the show runs. The illustration below shows the overall flow of data. A visitor enters a comment using a web browser. This comment is logged into a database by the web server. As part of a presentation, WATCHOUT uses the Dynamic Image Server to load the most recent comments form the database, displaying them as part of a presentation.

Database1.jpgAs usual, this example is kept as simple as possible. In a real world scenario, you probably want to add some “filtering” of what goes into the database, to avoid displaying unwanted comments on-screen.

Web Server Configuration

This recipe uses the same web server as the one titled “Uploading with a Browser”, but adds some server configuration files to handle the comment submissions and database interaction. Download the attached “database.zip” file, unpack it, and copy the entire resulting “database” folder to c:\wamp\www, so that its index.html file can be found at:

c:\wamp\www\database\index.html. Here’s a brief description of each file in this folder:

  • index.html presents a very simple form to the visitor, allowing a name and comment to be entered (see “Entering New Comments” below).
  • insert.php receives the comment from the form and stores it in the database.
  • read.php reads a comment from the database and provides it in JSON format, which is subsequently rendered by a Flash file in the image server.
  • dbConnect.php provides some common configuration settings used by insert.php and read.php to connect to the database.

CAUTION: The PHP code used in this example is deliberately kept as simple as possible. It doesn’t perform any error handling, or verification of the data supplied by the user.

Database Configuration

This recipe takes advantage of the MySQL database that comes with the WAMP web server. A database table receives all comments as they are entered. In this recipe, WATCHOUT is configured to show only the three most recent comments, although all comments are kept in the database, and can be used for other purposes.

The attached “quotes.zip” file contains the database configuration and some sample data used by this recipe. Download this file and unpack it. It contains a single file, named “quotes.sql”. This file needs to be added to the WAMP database. To do so, open a web browser on the computer running the WAMP web server and go to:

This displays an administration page for the database built into the WAMP web server.

NOTE: This address is only accessible from the computer running the WAMP server, and therefore can’t be accessed from another computer on your network – even if it has access to other pages in the web server. This is for security reasons.

If you get no response, make sure the WAMP server is running, as indicated by a green W logotype shown in the activity area in the lower right corner of the screen. If not, start the server by choosing Start > All Programs > WampServer > start.

phpmyadmin1.png

The phpMyAdmin database administration interface provided by the WAMP server.

Click the Databases tab at the top of the window, enter “quotes” into the “Create new database” field, and select “utf8_unicode_ci” on the Collation menu (see above), then click Create.

Select the newly created “quotes” database by clicking its name in the left column of the phpmyadmin window, then click the “Import” tab. Click the “Choose File” button and select the “quotes.sql” file you unpacked above. Scroll down to the bottom of the window and click “Go”.

phpmyadmin2.png

A green message should appear at the top of the phpmyadmin window, stating that the import was successful.

IMPORTANT: On installation, the MySQL database has no password set. If you want to use your WAMP server in a real application, you’re strongly advised to set a password for the “root” user. If you do, remember to update the dbConnect.php file accordingly, by entering your password as the last parameter to the mysql_connect function call. 

Entering New Comments

Database-Form3.pngOnce the web server and database have been configured as shown above, you can use the form to enter new comments by browsing to this URL:

Substitute the IP number of your web server, as appropriate (or use “localhost” if you run the web browser on the same computer as the web server). This should display the form specified in the index.html file, as shown to the right.

Enter a name and a comment, then click “Send Comment”. A brief “thank you” message will be displayed, and then the form will re-appear. Using the phpmyadmin interface on the web server, as shown above, you can now see the new comment added to the database by selecting “quotes”  in the list of databases shown on the left, then clicking the “Browse” action for the “comment” table and finally scrolling down to the bottom of the list of comments.

Configuring the Dynamic Image Server

Before you can display the images in WATCHOUT, the Dynamic Image Server needs to be configured in the same way as in the “Uploading with a Browser” recipe. Then download the attached “JSONRenderer.zip” file, unpack it and copy the “JSONRenderer.swf” file to the C:\DynImages folder. This file is subsequently used by the image server to render the data obtained from the database. Make sure the image server is running before proceeding. 

NOTE: The attached “JSONRenderer.zip” file also contains the Flash source file (JSONRenderer.fla), in case you want to make any changes. Doing so requires Adobe Flash Professional CS5 or later.

This Flash project uses the JSON decoder of the “as3corelib”, which you have to download and make available to Flash Professional if you want to change the Flash file. It’s available here:

NOTE: The  latest version of Flash has a JSON decoder built in, in which case you may not need the “as3corelib”. See here for more details.

WATCHOUT Show

Download  the attached “DatabaseShow.zip” file, unpack it, and open the “DatabaseReader.watch” file in WATCHOUT. Open the Preferences dialog box on the File menu, select the “Control” tab, and make sure that the “Default Dynamic Image Server Address” is set to the address of your image server. Then run the main timeline to display the comments. The last three database records will float across the screen, one after the other. Go online to a display computer to see the final result if you wish.

NOTE: The WATCHOUT production software can not preview dynamic images if the Dynamic Image Server is running on the same computer. To preview dynamic images, run the Dynamic Image Server on a separate computer.

Using Dynamic Image Parameters

DynImgParam.PNGThe “DatabaseReader.watch” show provides three dynamic images, named “Record 0”, “Record 1” and “Record 2”, corresponding to the last three records in the database. Note that those dynamic images all refer to the same image file (“JSONRenderer.swf”). They take advantage of the ability to pass a parameter to the image server, where the parameter, in this case, specifies the offset from the end of the comments table. This parameter flows in the opposite direction of the comment:

DatabaseParamFlow.jpgThe “ord” parameter is sent to the image server, which passes it on to “JSONRenderer.swf”, which in turn passes all parameters to the “read.php” server script, which finally uses the “ord” parameter to pick the desired database record. Take a look at the ActionScript found in the JSONRenderer.fla file to learn how to pass parameters received from the Dynamic Image Server to a server-side script.

Files attached to this recipe: