Pre-fetched data vs MySql query resource id (RecordSet Class)

[FFighter]

Hello,

It has been some time since I started developing RIA in the flash platform using Flash Remoting for the client/server communication. I use the CakePHP framework (www.cakephp.org) and AMFPHP alltogether (through the CakeAMFPHP Cake add-on) and I´m quite happy with it...

I know about Pageable RecordSets and I would like to use it as much I as I can. However, PageAble recordsets mean that I need to return the mysql query resource id. Here is an example (not using CakePHP for the sake of simplicity):

PHP Code:

function getEvents() {
     $query = "SELECT * FROM events";
     $resource_id = mysql_query($query);
     return $resource_id;
} 


This would be enough for AMFPHP, and at the client-side I would use the RecordSet class to fetch the data on demand (or not). However, lets say an event record has this structure (columns/fields)

id,name,city_id,event_date

Where "city_id" being a FK to the Cities table and event_date being a int storing a unix timestamp.

In this case, if I would like to return only the city name and the formated date string (dd/mm/aaaa), I would do this way:

PHP Code:

function getEvents() {
     $query = "SELECT * FROM events";
     $resource_id = mysql_query($query);
  
   
     foreach($arr = mysql_fetch_array($resource_id)) {
             
             $arr['event_date'] = $this->convertToString($arr['event_date']); //Converts the unix timestamp to dd/mm/aaaa
             $arr['city_name'] = $this->getCityName($arr['city_id']); //query to the cities table returning the name where id = $arr['city_id']

     }

     return $arr;

} 


This way, I would not be returning a mysql query resource id but already fetched data instead, and I wouldn´t be able to use all the features of the recordset class, but, I could do some processing in the array (get the city name of the event´s city and convert the unix timestamp to dd/mm/aaaa string).

This foreach strategy is the only way I know to pre-process the data this way before sending it to flash... I would be grateful if someone could enlight me and maybe show me a better way (maybe direct through sql in the dbms layer?) to do such thing!

Thanks, and if you need more information please ask! I really need a hand here!

- Marcelo.

[Paerez]

ok so how about making a new PHP function that takes the resource ID as a parameter, and then grabs the data, parses it, and returns it? Then you would call one function to grab the resource ID, then another function to get the pages.

The other alternative is to do the parsing in ActionScript.

Or you could make your SQL query smarter and do the City linking and parsing inside the SQL query (not sure if the parsing is possible, but the city linking definitely is - its what sql is made for).

good luck

[FFighter]

Thanks a lot for the tips Paerez. I will try´em and let you know if they worked

[FFighter]

Sorry Paarez, but I didn´t understand clearly the procedure you described. Could you give some code examples?

Thanks a lot!

[Paerez]

i will try to cut up your code to explain what I mean:

PHP Code:

function getEventHandle() {
     $query = "SELECT * FROM events";
     $resource_id = mysql_query($query);
     return $resource_id;
}

function getNext($resource_id) {
  foreach($arr = mysql_fetch_array($resource_id)) {
    //Converts the unix timestamp to dd/mm/aaaa         
    $arr['event_date'] = $this->convertToString($arr['event_date']);
    //query to the cities table returning the name where id = $arr['city_id']
    $arr['city_name'] = $this->getCityName($arr['city_id']);
     }
     return $arr;
} 


So you call getEventHandle to grab the resource ID, then whenever you need the data you run getNext and pass it the resource ID.

I dont really know much about php, so excuse me if the syntax is wrong.

I also dont know anything about Pageable Recordsets, so excuse me if this doesn't do anything useful