[FX.php List] Workaround for Portal Bug in Recent Versions of FMS

[BEVERLY VOTH]

How do you make one query with one connection? :) 


Just push all the queries into one connection. YES, you need to save the results to different variable names....

// start the connection
// query1 - save to $main
// query2 - save to $child1
// ...
// end the connection

In the case of using the parent record to get the foreign key, you have the option of making the child queries within the loop of the parent results. OR you can just display based on the keys as you loop through the parent:

/* EXAMPLE 1 */
// open connection
// query1
for ...
// display main record
// query2
// display children records
... (end loop)
// close connection


/* EXAMPLE 2 */
// open connection
// query1
// query2
// close connection
for (loop main)
display main record
for (loop children)
if (keys match)
display children records
... (end loop)

You can see that the first example throws the main & child displays while you still have the connection open. The second example saves all the children of all the parents and only displays after the connection is closed. Yours to test and see. I may use both methods and it depends upon the number of main results called and the number of children needed to be called.

Beverly

p.s. you might look at examples of these with MySQL queries and see what others do. then apply the same logic (different class calls, of course!) to FM queries.


On Jan 27, 2015, at 12:20 AM, Kevin Futter <KFutter at sbc.vic.edu.au> wrote:

> But how do you make multiple queries using only a single connection? This
> element has me puzzled, so I¹d love some clarification, especially given
> the performance issues we¹ve been suffering with FM web output lately.
> 
> Kev
> 
> On 25/01/2015 2:32 pm, "Beverly" <beverlyvoth at gmail.com> wrote:
> 
>> I didn't say multiple connections I said multiple queries. Does that help?
>> This is the same method I use to SQL dbs.
>> 
>> -- sent from myPhone --
>> Beverly Voth
>> --
>> 
>>> On Jan 24, 2015, at 6:54 PM, Malcolm Fitzgerald
>>> <malcolm at notyourhomework.net> wrote:
>>> 
>>> Beverly,
>>> 
>>> Don't you find that the need to make multiple connections imposes time
>>> penalties?
>>> 
>>> I began doing all the work, ie, query parent, query related tables, but
>>> I'm now putting portals onto layouts and getting better performance.
>>> Admittedly, this is done on small related record sets.
>>> 
>>> Malcolm
>>> 
>>>> On 25/01/2015 12:41 pm, Beverly wrote:
>>>> Yes. Query and display the parent. Query and display as many "portals"
>>>> as needed by taking the parent primary key to match the child foreign
>>>> key.
>>>> 
>>>> -- sent from myPhone --
>>>> Beverly Voth
>>>> --
>>>> 
>>>>> On Jan 24, 2015, at 2:10 PM, Philip Lamb <phil at eden.net.nz> wrote:
>>>>> 
>>>>> Beverly,
>>>>> 
>>>>> Can you explain a little more about this technique? I used portal
>>>>> fields as a convenient way to query multiple tables with a single
>>>>> query. Do you make multiple requests using the foreign key technqiue?
>>>>> 
>>>>> Regards,
>>>>> Phil.
>>>>> 
>>>>>> On 24/01/2015, at 5:47 AM, BEVERLY VOTH <beverlyvoth at gmail.com>
>>>>>> wrote:
>>>>>> 
>>>>>> I tend to skip portals altogether on the web and just get the data
>>>>>> using the foreign key. Which is another option for those who hate the
>>>>>> portal-bug. :)
>