Sending back recordsets

One of the most impressive features of Flash remoting is the ease at which you can send back resultsets. AMFPHP is preloaded with a set of adapters for a variety of databases and database drivers, in particular:

  • MySQL
  • PostgreSQL
  • SQLite
  • MySQLi
  • MsSQL
  • ADODB
  • ODBC
  • Oracle (oci8 for PHP5)
  • PDO
  • PEAR::DB

Resources and auto-negotiation

When you return a value of type resource, AMFPHP automatically investigates the particular resource type and tries to apply the appropriate adapter. Hence there’s no need to set the return type. Here’s a typical example of a method using mysql: 

function returnResultset($id)
{
    mysql_connect('localhost', 'user', 'pass');
    mysql_select_db('mydb');
    return mysql_query(sprintf("SELECT * FROM table where sid = %d", $id));
}

mx.remoting.RecordSet

On the Flash side of things, your SQL resultsets will be mapped to an object of type mx.remoting.RecordSet. You should look up this class in the Remoting documentation for details on how to use. Here's a very quick overview of the class.

Instances of mx.remoting.RecordSet have a length property indicating the number of records (also called rows or items). To get one record out of the object, use the getItemAt(i) method. What you will get is an instance of the object class with several keys. These keys will have the same name as the SQL table where it came from. For example, with a users table that has the columns username and userid:

 return mysql_query("SELECT * FROM users");

You can get the username of the second user in the table by using recordsetInstance.getItemAt(1).username.

RecordSets can be used as dataProviders for datagrids directly. They can also used as a dataProvider for a ComboBox, although in that case you will want to create two aliases in SQL, 'data' and 'label' to make your life easier on the Flash end. For example, with the example above, you could send back to Flash 

return mysql_query("SELECT userid AS data, username AS label FROM users);

You can then use that RecordSet as a dataProvider for a ComboBox directly in Flash directly.

RecordSets can be filtered based on certain criteria and also reordered on the Flash. This makes much more useful and flexible than regular arrays. Look up the documentation for Flash Remoting for information on how that works.

Objects and auto-negotiation

Some types of objects are also detected as recordset types by AMFPHP. For example, PEAR::DB is recognized that way. get_class is called and matched against a list of adapterMappings. To get your own esoteric database abstraction layer working, you can copy and modify the peardbAdapter and then add the adapter mapping in gateway.php, like so: 

$gateway->addAdapterMapping('the_get_class_name', 'the_friendly_name');

By default, PEAR::DB, oo-style MySQLi and pdo are automatically detected.

Nested resultsets and complex data types

Since data-type negotiation occurs recursively in AMFPHP, you can nest recordsets in arrays or objects without a problem. Here’s a typical example of this: 

function returnResultset($id)
{
    mysql_connect('localhost', 'user', 'pass');
    mysql_select_db('mydb');
    $rs1 = mysql_query(sprintf("SELECT * FROM table where sid = %d AND cat = 1", $id));
    $rs2 = mysql_query(sprintf("SELECT * FROM table where sid = %d AND cat = 2", $id));
    return array($rs1, $rs2);
}

Making custom adapters

Adapters are loaded dynamically from the flashservices/sql folder. They are named %type%Adapter.php, where %type% is the lowercase name of the resource type. Tale a look at odbcAdapter.php for an example on how you can implement the API for your particular database type. mysqlAdapter.php is not a good adapter to start from since it includes a terrible hack for correctly typing numbers that will leave your head scratching.

Building recordsets manually

See the section on manual recordsets on how to build recordsets manually.


© amfphp.org | Disclaimer | Conditions of use