4. Loading data from databases using name/value pairs (php-asp code)

When using databases, there are SO considerations that you need to take in account. Not all databases are available for different platforms and some of them are difficult to manage for non-technical users. So we will a couple of examples, the first using Access and asp, the second using PHP and MySQL. While the first works only in Microsoft Servers, the other could work in many platform. We don't give details about how to setup the environment, but you can read this note about installing Apache, PHP and MySQL. The Flash-back-end talk is very similar to the txt technique, that's the back-end language query the database and output name/value pairs that Flash reads. So steps are:

1. Setup database table(s)
2. Connect back-end language with database, query database and output as name/value pairs
3. Read name/value pairs in Flash

Examples differs in back-end side, but Flash side is the same. Our table need to hold a table with 4 columns:

a. Id, usually an autonumber as unique identifier of a row
b. Title, text
c. Comments, text
d. Image, text

Access and ASP setup

This example will work only in Windows environments, so skip it if you're running other platforms. As exposed, I assume that you have a web server, Access and ASP support.
Open Access and create a new database, name it anastasio.mdb. Then, create a new table named titles following this screenshot

Now add data to your database

ID   Title          Comments                  Image
-------------------------------------------------------------------
      Anastasio    This is a nice guy            an1.jpg
      In the car    Here's Anastasio's car     an2.jpg
-------------------------------------------------------------------

Now you need to create a DSN for your database, so the script could locate the source. Go to Windows - Control Panel - OBDC data sources and add a new DSN entry. Select Microsoft Access Driver, select the anastasio.mdb file from the browser button and give a name of "anastasio", leave description empty. Now you can Access the database trough the web server
Now we need to read data and output like name/value pairs, so our two entries should look like this:

Title0=Anastasio&Coments0=This is a nice guy&Image0=anastasio.jpg&Title1=In the car&Coments1=Here's Anastasio's car&Image1=anastasio_car.jpg&cant=2

As you can see, is the same as the txt file. But how can we get this string from our database? We need to use some back-end script, ASP in this first example. Let's go to our asp code


    <%
    Option Explicit
    
    Dim cn
    Dim cmd
    Dim rs
    Dim cont
    
    Set cn = Server.CreateObject("ADODB.Connection")
    cn.ConnectionString = "anastasio"
    cn.Open
    
    Set cmd = Server.CreateObject("ADODB.Command")
    cmd.ActiveConnection = cn
    cmd.CommandType = 1
    cmd.CommandText = "SELECT Title, Comments, Image FROM titles"
    
    Set rs = cmd.Execute
    cont=0
    
    Do Until rs.EOF
        Response.Write "Title"&cont&"="& rs("Title") & "&" &"Comments"&cont&"="& rs("Comments") &"&" &"Image"&cont&"="& rs("Image")& "&"
        cont = cont+1
        rs.MoveNext
    Loop
    
    Response.Write "cant="&cont
    
    rs.Close
    cn.Close
    %>


This page have only ASP code, and should be readed and parsed by the web server (IIS in our example) It makes a database connection, execute the SQL query and ouputs variables as name/value pairs. There's no need of HTML content inside this page.

Name this file anastasio.asp and put in a server readable folder. Put there also the Flash Movie. Now our Flash movie will use this asp script as the data source. If you're confident with ASP language and .Net framework, feel free of change this basic script as far as the output remains the same.
Since the Flash side is the same for both examples, let's move to the PHP-MySQL back-end code before explaining the Flash side.

----------------------------------------------------------------------------------------------------------------------------------------
TIP
A common technique for debugging the back-end script is to open it directly into the browser to see if the output is as expected. So open the file trough your server, something like: http://localhost/anastasio.asp (modify to match your own path) and see that the output is as the one previous exposed. Remember to always do this prior to test your Flash movie: unexpected errors could break your output!
----------------------------------------------------------------------------------------------------------------------------------------