Interacting With and MySQL

This article outlines an approach for getting shockwave to read and write data to a mySQL data. Although there are some Xtras that let Director connect directly to a MySQL data base, this article will focus on using to connect to connect with the database, and Shockwave will interact with using some netLingo commands.

diagram

Requirements

This article assumes you have access to a server (even if just your local machine) with (4.3 or later) and MySQL 4 (or later?). If you don't, and you are using MacOSX, then have a look at the packages provided by Server Logistics - although note that they haven't been updated for Tiger yet (they run a treat with OS 10.2 and 10.3). Also consider some (free) MySQL tools like CocoaMySQL, an Open Source MySQL Database Manager that is very easy to use.

The Demo Application

The demo application we are going to build is a simple 'guestbook'. The first step is to create a table to store the data. Next we will create some pages to add to and get data from the database. Finally, we'll create the Shockwave movie that interacts with the pages.

The MySQL Database

The first step is to create a MySQL database and add the following table:

# SQL to create table

CREATE TABLE `comments` (
  `id` int(11) NOT NULL auto_increment,
  `who` varchar(128) NOT NULL default '',
  `fromURL` varchar(255) default '',
  `postedOn` datetime NOT NULL default '2014-01-01 12:00:00',
  `comment` text NOT NULL,
  PRIMARY KEY  (`id`)
) TYPE=MyISAM

If you are usign CocoaMySQL or a similar GUI tool for working with MySQL, you can use the comments.SQL file (included with the downloads) to create the table (just be sure you have the correct database selected first).

The Pages

The next step is to create some pages - one to get data from the database, and another to add a record to the database. To do this, we need to get to create a database connection and execute a SQL query.

Rather than having to write out the code to connect and query the database with each file, I have chosen to use a standard class which is included in each page. Below is an edited version of this class to handle a connection to a MySQL database. Put this script into a file called "db.inc" (or name it whatever you like). 

//db.inc

class DB {
 
    /*
   	  Edited version of Luke's MySQL DB class (v.1.0.0)
      Note - you will need to edit some properties below,
      inserting your username and password, database name
      and host (if not localHost).
	*/
    
    

    function DB() {
         $this->host = "localhost";       // EDIT THIS
         $this->db   = "test";            // EDIT THIS
         $this->user = "usernamehere";    // EDIT THIS
         $this->pass = "dbpasswordhere";  // EDIT THIS
         $this->link = mysql_connect($this->host, $this->user, $this->pass);
         if (!$this->link) die;
         mysql_select_db($this->db);
    } 
     
  function GetXML($q='') {
    // return a recordset as XML
    // returns an empty string if there is an error
    $errResult ="";
    if (!$q) return $errResult;
    $rs = mysql_query($q, $this->link);
    if(!$rs) return $errResult;
    if (mysql_num_rows($rs) > 0) {
      $doc = domxml_new_doc("1.0"); // new XML doc
      $root = $doc->add_root("recordset");
      while ($a = mysql_fetch_assoc($rs)) {
        $record = $root->new_child("record","");
        foreach ($a as $k => $v) $record->new_child("$k","$v");       
      }
      return $doc->dumpmem();
    }
    return $errResult;
  } 
  
  function Query($q='') {
    // Make a query, returning false if there is no result;
    if (!$q) return false;
    $result = mysql_query($q,$this->link);
    if(!$result) return false;
    return $result;
  }
}

Note this script uses the XML Extension for 4.3. Included in the downloaded files is a version that manually constructs the XML (no extensions needed). The version of the GetXML function that does not rely on the domxml extension looks like this:

";
	$doc .="<recordset>";
	while ($a = mysql_fetch_assoc($rs)) {
		$record = "";
		foreach ($a as $k => $v) {
			$v2 = $this->_xmlentities($v);
			$record .="<$k>$v2</$k>";
		}
		$doc .="<record>$record</record>";
	}
	$doc .= "</recordset>";
	return $doc;
}

function _xmlentities($string, $quote_style=ENT_QUOTES) {
	// function from au.net user comments
   static $trans;
   if (!isset($trans)) {
	   $trans = get_html_translation_table(HTML_ENTITIES, $quote_style);
	   foreach ($trans as $key => $value)
		   $trans[$key] = '&#'.ord($key).';';
	   // dont translate the '&' in case it is part of &xxx;
	   $trans[chr(38)] = '&';
   }
   // after the initial translation, _do_ map standalone '&' into '&#38;'
   return preg_replace("/&(?![A-Za-z]{0,4}\w{2,3};|#[0-9]{2,3};)/","&#38;",
     strtr($string, $trans));
}

This script can be used to create a "DB" object with methods for executing a SQL statement (such as an INSERT or UPDATE), and another for returning an XML representation of a recordset.

Getting Data - the Script

To create a page that returns all the records in the comments table we created above, you would write a page like this:

// getcomments

require_once("db.inc");
$DB = new DB;
$SQL = "SELECT * FROM Comments ORDER BY postedOn DESC"
echo $DB->GetXML($SQL );

The data returned from a MySQL database to will be text which is written to the browser's output. In this case, I have chosen to format the output data as XML. I could have chosen some other format (a combination of commas and linebreaks to indicate fields and records), or even format so that lingo's value() function might be able to interpret it as a list. However, since the comments might include commas and returns, XML would be the safest format (and beside which, we can use the XML parser to parse the data without having to write our own parser).

Inserting Data - the Script

To insert data into the database, we would want the page to do some validation on the data. The following page checks that a user name and comment string has been provided before attempting to add it to the database:

// addcomment


require_once("db.inc");
// validate the provided data
$who = $_POST['who'];
if ((!$who) || (strlen($who) < 2)) {
  echo "Error: invalid name '$who'";
  exit;
}
$comment = $_POST['comment'];
if ((!$comment) || (strlen($comment) < 2)) {
  echo "Error: invalid comment";
  exit;
}
$from = $_POST['from'];
$sql = "INSERT INTO comments (who, fromURL, postedOn, comment) 
	VALUES ('$who', '$from', NOW(), '$comment')";
$DB = new DB;
$result = $DB->QUERY($sql);
if ($result) echo "OK";
else echo "Error";

Checking Everything is working.

If you open getcomments in a browser, you should see the data returned by the script as XML (you might need to select 'View Source' in your browser).

To test that the addcomment function is working as expected, create a simple HTML page with this form:

<form name="form1" id="form1" method="post" action="setcomment">
	<p>Name:<input type="text" name="who" /></p>
	<p>URL:<input type="text" name="fromURL" /></p>
	<p>Comment:<textarea name="comment"></textarea></p>
	<p><input type="submit" name="Submit" value="Submit" /></p>
</form>

This form posts data to the setcomment page created earlier. The setcomment page looks for the data in the $_POST 'superglobal' (which requires 4.1 or later; for older versions of use $HTTP_POST_VARS).

If this is working as expected, then we can be confident that the - MySQL side of things are working. The next step is to get Shockwave to play nice with

The Shockwave

Rather than using the HTML form, in Shockwave we are going to post the data using the postnettext function.

Using the NetOp.transaction scripts discussed elsewhere, we can create a simple behaviour to test that everything is working:

LingoBehaviour
on exitFrame me
  go to the frame
end

on beginSprite (me)
  FormData = ["who": "Luke", "url": "", "comment": "This is a test comment"]
  theURL = "http://www.lingoworkshop.com/articles/mySQL/setcomment" 
  NetOp = script("NetOp.transaction").new(theURL, FormData)
  NetOp.AddListener(me)
  NetOp.Start()
end

on NetTransactionComplete (me, sender,theData)
  if theData.error = 0 then
    put "SERVER RESPONSE: " & thedata.text -- should be "OK"
  else
    put "NETWORK ERROR: " & sender.GetErrorDescription(theData.error)
  end if
end

Create a new empty movie and add this behaviour to a frame script, and hit play. If the data was successfully posted, you should see "SERVER RESPONSE: OK" in the message window.

To retrieve the data, we can do one of two things: Use network lingo get get the XML string printed by the getcomments page and then parse this as a string, or (2) use the XML Xtra to parse the URL. Here is an example of the first approach:

Lingo Behaviour

on beginSprite (me)
  theURL ="http://www.lingoworkshop.com/articles/mySQL/getcomments" 
  NetOp = script("NetOp.transaction").new(theURL)
  NetOp.AddListener(me)
  NetOp.Start()
end

on NetTransactionComplete (me,sender,data)
  if data.error = 0 then
    XMLStr = data.text
    XMLParser =script("XMLParser").new()
    put "THE DATA: " & XMLParser.ParseBuffer(XMLStr)
  else
    put "ERROR: "&sender.GetErrorDescription(data.error)
  end if
  go to the frame + 1
end

And here is an example of the second approach:

Lingo Behaviour

on beginSprite (me)
  XMLParser =script("XMLParser").new()
  XMLParser.AddListener(me)
  theURL = "http://www.lingoworkshop.com/articles/mySQL/getcomments"
  XMLParser.Parse(theURL)
end


on XMLParsed (me, aList, anError)
  if voidP(anError) then
    put "THE DATA (METHOD 2): " & aList
  else
    put "ERROR PARSING XML URL: " & anError
  end if
end

Both these behaviours use an "XMLParser" which is a simple script providing a wrapper for the XML Xtra. Here is the XML Parser script:

Script "XMLParser"

-- XML Parser Script 1.0
-- Note: this uses the newer XML Xtra that came with DMX2004
-- If you are using Director MX, you can get the xtra from your
-- shockwave installation (the Xtra seems to work fine with this
-- older version of Director)

property xmlparser, err
property Listeners

on new (me)
  me.err = ""
  me.Listeners = []
  return me
end

on Destroy (this)
  me.Listeners.deleteAll()
end


on AddListener (this, obj)
  -- Any object that wants to receive callback messages from this 
  -- widget needs to add itself as a listner object.
  if not(me.Listeners.getOne(obj)) then
    me.Listeners.add(obj)
  end if
end

on RemoveListener (this, obj)
  -- Any object that wants to stop receiving callback messages from 
  -- this widget can remove itself from this widget's list of listeners.
  if me.Listeners.getOne(obj) then
    me.Listeners.deleteOne(obj)
  end if
end

on Parse me, thisURL
  -- Start parsing. A XMLParsed message will be sent to all listeners
  -- when the parsing is finished.
  me.xmlparser = new(xtra "xmlparser")
  err = me.xmlparser.parseURL(thisURL, #parseDone, me)
  return err
end

on ParseBuffer me, thisStr
  -- parse the string buffer, returning the result immediately.
  me.xmlparser = new(xtra "xmlparser")
  me.xmlparser.parseString(thisStr)
  return xmlparser.makeProplist()
end

on parseDone me
  tmp = timeOut(me.string).new(1, #MakeCallback, me)
end

on MakeCallback (me, aTimer)
  me.err = xmlparser.getError()
  aTimer.forget()
  theList = xmlparser.makeProplist()
  call(#XMLParsed, me.Listeners, me, theList,  me.err )
end

The advantage of using a network operation outlined in the first approach to download the XML is that you can provide some feedback on how the network operation is going (and the NetLingo seems to give better errors if things go wrong). On the other hand, using theXML Parser involves writing less code.

First published 09/08/2005