Hello,
I mostly work with SQL,
in a SQL statement you can
‘join’ multiple tables before outputting… http://www.sql-tutorials.net/Inner-Join-Basics.php
<code>
SELECT A.ArtistName,
R.RecordingTitle
FROM Artists A INNER JOIN Recordings R ON
R.FKArtistID = A.ArtistID
</code>
Or you could make new
View, which is a view on different database which are joined to one database.. (same
as above only jou just select on the viewas a table)
Or use stored procedures
to format your output
I don’t know if this
is the same in mysql, but maybe this helps,
Success,
nyls
Van:
[EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Namens Marcelo de Moraes Serpa
Verzonden: donderdag 4 mei 2006
15:18
Aan: [email protected]
Onderwerp: Re: [osflash]
[REMOTING] Returning data to flash question
Well, let me try to
explain this better...
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 Cake for the sake of simplicity):
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:
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 and I wouldn´t be able to use all the features of the recordset
class. 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.
On 5/3/06, Marcelo
de Moraes Serpa <
[EMAIL PROTECTED]> wrote:
Hello all!
I´m working in a RIA that uses arp/arpx on the client side and CakePHP ( www.cakephp.org) and AMFPHP ( www.amfphp.org) (through
CakeAMFPHP) on the server side. I´m very happy with the results but there´s
something that really bothers me and that I would be very happy if anyone could
clear up my mind on this subject.
Sometimes, I find myself doing the following:
[PHP]
function getAgenda($offset = '0', $limit = '10') {
$this->autoRender = false;
$this->constructClasses();
//primeiro convertemos a data de hj para
Unix Timestamp!
$hoje = getDate(); //pega a data de hj...
$hoje_timestamp =
mktime(0,0,0,$hoje["mon"],$hoje["yday"],$hoje["year"]);
$this->countQuery = "SELECT
COUNT(*) AS recordCount FROM Events WHERE Event.event_date >
$hoje_timestamp";
$agenda =
$this->Event->findAll("event_date >
$hoje_timestamp","event_date"," Event.event_date
desc",$limit,$page,false);
$returned_events = Array();
//In this foreach, I get the array fetched by
the findAll() method and I then augment some "properties" from other
tables...
foreach($events as $ev) {
$casa_id =
$ev['Event']['casa_id'];
$ev['Event']['Casa_Name']
= $this->Casa->field("name","id = '$casa_id'");
$ev['Event']['event_date'] =
$this->Event->timetostr($ev['Event']['event_date']);
$returned_events[] = $ev;
}
return $returned_events;
}
[/PHP]
By using this technique, I save a lot of work and bandwidth as related data is
directly fetched in this method. However, I feel this isn´t the best way to do
that. Firstly, I can´t use the RecordSet class as I´m returning fetched data
and not the mysql resource id, second, I think this kind of thing could be done
entirely on the DMBS layer, through SQL. And finally, for CakePHP experts,
maybe Cake has a easy way to do such things?
Thanks in advance,
|
