For Newbies: Getting query data using MySql, PHP, AS3.0, XML
The below example is in 3 parts:
1. the MySql table creation and data insert (just for reference)
2. the PHP code to retrieve the data in the table and create pseudo xml,
3. the AS3.0 code to loop thru the data.
I've tried to make this example as compact as possible, using descriptive names but keeping comments to a minimum. The mysql/php portion is very skimpy by design (php.net has most of the answers you'd need for that portion of this example).
Finally, the interesting point is that ActionScript 3.0 seems to have no problem using data that is simply FORMATTED as XML. The below PHP code does NOT create a file. It simply "echos" the data in xml format.
In MYSQL:
DROP TABLE IF EXISTS TEST_TABLE;
CREATE TABLE TEST_TABLE (
tt_id int(10) unsigned NOT NULL auto_increment,
tt_fname varchar(32) collate utf8_bin NOT NULL default '',
tt_lname varchar(32) collate utf8_bin NOT NULL default '',
PRIMARY KEY (tt_id)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
--
-- Dumping data for table 'TEST_TABLE'
--
INSERT INTO TEST_TABLE VALUES (1, 'Bob', 'Smith');
INSERT INTO TEST_TABLE VALUES (2, 'John', 'Jones');
----------------------------------------------------------------------------
In PHP:
//Return results in XML form (error checking removed for clarity)
$sql = "SELECT * from TEST_TABLE;"
// Connect to the database
$mysql_link = mysql_connect($DB_server, $DB_user, $DB_password);
mysql_select_db ($DB_name);
// Perform query
$result = mysql_query($sql);
$num_of_cols = mysql_num_fields ($result);
$num_of_rows = mysql_affected_rows();
$returnXML = "";
while ($col = mysql_fetch_array ($result))
{
$returnXML .= "";
for ($j = 0; $j < $num_of_cols; ++$j)
{
$returnXML .= "" . $col[$j] . "";
}
$returnXML .= "";
$i = $i + 1;
}
$returnXML .= "";
echo $returnXML;
----------------------------------------------------------------------------
In AS 3.0:
public function do_xml_data_import()
{
var xmlURL:URLRequest = new URLRequest("my_file.php");
var xmlLoader:URLLoader = new URLLoader(xmlURL);
xmlLoader.addEventListener(Event.COMPLETE, xmlLoaded);
xmlLoader.addEventListener(IOErrorEvent.IO_ERROR,xmlLoadError);
}
function xmlLoaded(event:Event)
{
var numRows:int;
var numCols:int;
var i:int;
var j:int;
holdXml = XML(event.target.data);
//number of rows in query
numRows = holdXml.child("*").length();
//number of columns in query
//(Note: all rows assumed to have the same number of columns)
numCols = holdXml.XMLROW[0].child("*").length();
//get data from query (held in xml)
for (i=0; i < numRows; i++)
{
for (j=0; j < numCols; j++)
{
trace("[" + i + "][" + j + "]: " + holdXml.XMLROW[i].XMLCOL[j]);
}
}
}
Copyright Gary Rosenzweig