Forums

Hi Bob,

I am looking for some advice and directions. I need to run an sql against some tables and UNION the data like in:

SELECT id from ...
UNION
SELECT id from ...
UNION
SELECT id from ...

The simplest way to do it would be to use a DB multi-record loader for each SELECT. The problem is that the number of tables to query is not fixed. So ideally I would like to be able to select the tables (with another SELECT statement) that have to be queried first and than run the SELECT statements.
I see two alternatives:
a) I could write some php code to extract the data but in this case I am not sure how to merge this code into CF so that I do not loose the benefits of this component;
b) I could write a stored procedure in MySQL and call it from the load event. But in this case I do not know if it is possible and how I should make call.

Your advice would be appreciated.

Thanks,
adop

Hi adop,

Once it gets this complicated I'd hand-code the query and put the result into the $form->data array to make it accessible to other CF actions.

Bob

Hi Bob,

I have done as you suggested and hand-coded the query. Now the point is how to get the data out with the minimum effort, ie benefiting from CF features.
Is there a way for me to use the Data Displayer in DB multi loader in CF V4? If it was possible I could still benefit from using pagination for example. I have set up a Model ID (MyModel) and have data in $form->data['MyModel'] but I get a message saying

Undefined index: MyModel

The alternative is to hand-code a table and use a Custom Element (HTML/PHP) in the front-end.
Thanks.


Regards,
adop

Hi adop,

In CFv4 you can either use the DB Multi-Record loader or the ChronoConenctivity extension to display a listing. Both seem to work Ok but I have very little experience with the new versions of CC so far.

I'm not sure what is causing your error message but I suspect that the index you use needs to be a column in the table and not the Model ID.

Bob