Dynamically retrieving all the data from a mySQL database

[iplaywithfire]

I'm using the Zend AMF framework + php + as3 document classes to connect to a mySQL database.  Everything is fine, I have an established connection and can retrieve data from the database.  But my problem is retrieving it all AND retrieving it dynamically.

 

For example, I want to query mySQL for a list of databases (this step isn't so important - I'm only doing this in case there are multiple users storing data in their own database - I can use php to determine which database to connect to based on their login etc...).  Then query a particular database for a list of tables (store them in an array).  Retrieve the array into flash and retrieve all of the fields from each table.

 

Here's how I see the solution (just having trouble wrapping my head around it):

php class called on by flash that queries the database.  The php class queries for a list of tables within the database that is passed in.  This class returns an array to flash.  Flash uses a for loop to go through each table name and pass it into a php class that returns an array of fields based on each table name.

 

I'll just post all of my code so that it may spark some ideas.  I need to convert a lot of the hardcoded names (i.e. the database name) so that I can dynamically call and retrieve all of the information on a mySQL database and store it in flash so that I can easily use the data.

 

my PHP class:

<?php

class SAFE
{	
public function __construct()
{
	mysql_connect("localhost", "root", "root");
	mysql_select_db("CLEVELAND_SCHERER");
}

public function getFields($table)
{
	$result = mysql_query("SELECT * FROM `$table`");
	$info = array();

	while($row = mysql_fetch_assoc($result))
	{
		array_push($info, $row);
	}

	return $info;
}
public function getTables()
{
	$tableResults = mysql_query("SHOW TABLES FROM CLEVELAND_SCHERER");
	$tableArr = array();

	while($row = mysql_fetch_assoc($tableResults))
	{
		array_push($tableArr, $row);
	}

	return $tableArr;
}
}

 

my AS3 code:

package com.SAFE
{
import flash.display.MovieClip;
import flash.net.NetConnection;
import flash.net.Responder;

public class SAFE extends MovieClip
{
	var nc:NetConnection = new NetConnection();
	var tables:Array = []; //stores the names of the tables in a given database

	public function SAFE():void
	{
		nc.connect("http://localhost/SAFE/");

		//gets all the tables in the database and stores them in the tables[] array
		var tableRes:Responder = new Responder(getTables, onError);
		nc.call("SAFE.getTables", tableRes)


		//trace(tables[0]);
	}

	function getTables(e:Object):void
	{
		for(var i:int=0; i<e.length; i++)
		{
			//pushes the names of tables in a database to the tables[] array
			//this is not dynamic (CLEVELAND_SCHERER is the database name hardcoded into this script)
			tables.push(e[i].Tables_in_CLEVELAND_SCHERER);
			//trace(tables[0]);
		}

		var fieldRes:Responder = new Responder(getFields, onError);
		nc.call("SAFE.getFields", fieldRes, tables[0]);
	}

	function getFields(e:Object):void
	{
		for(var i:int=0; i<e.length; i++)
		{
			trace(e[i].DATE);
		}
	}

	function onError(e:Object):void
	{
		trace(e);
	}
}
}

 

Right now it works 100% but it's not what I'm looking for.  I just need some help wrapping my head around some of the for loops with multiple arrays and actually using them in flash (i.e. calling on them after they're in flash - array.tablename[j].field[k] = etc....)

 

What's the best way of retrieving all the data from a database dynamically and using it in flash?

 

[fenway]

That's a lot of code -- i see 2 select statements; what's the problem.