Forums

Hi,

How would I link multiple database tables based on a few key columns and display them in one table view?

So for example I may have a table of men and a table of women and on my website I want a table of all men and women ordered by age.

Kind Regards

Hi ctrlmedia,

But there is no table to connect these two tables together ?

Best regards,
Max

Hi Max,
Yes there is a table that connects them.

Table 1 has one record.

Table 2 has multiple records related to the record in table one.

Table 3 has multiple records related to the record in table one.

I want to view all records from tables 2 & 3 that relate to the record in table one. There are keys, so there is the DOB and ID number which will be the same in all tables.

Does that help explain??
Kind regards
Mark

Hi Max,
I suppose I would want to do a UNION ALL is that possible with CCv6?

If it is, would I be able to update/save the records back to the database when doing this?

Probably difficult to answer without knowing what it is we are doing but, I have separated all of this data into their own tables thinking it would be easier to manage (each table (table 2&3) has the exact same columns), but in your experience do you think it would be easier to have all of this data in the same table?

There could be a huge amount of records in the table if not separated that's all.

Kind Regards

Hi ctrlmedia,

Yes, a Union is needed here, Connectivity does not have an interface for this but you can use some custom code to load the records then list them, so instead of a "Read data" function you will use a PHP function and return the results.

I think its better to have the data in one table, because if you are going to union them then its the same story, I believe the performance will be better if the data is in one table and correctly "indexed".

Best regards,
Max

Thanks for your advice Max,
I have decided to put it all in one table.

I had an array saving to each table so multiple records were being saved to each table. For now I am going to keep that but also save it all in to one table (just until I work out how the data is going to be used in the future). Will this be a problem as I cant seem to get it to work? ( I have cleared the cache multiple times )

To do this I have merged all of the arrays in to one and give it a model id. So the new array is called allarrays. Looking at the debug this has worked I see all of the records within the new array called allarrays using the debug.
(To merge the arrays I did "$allissues = array_merge_recursive($array1, $array2)" and then "$form->data["allissues"] = $allissues;")

This prints somethign like this:
[allissues] => Array
(
[0] => Array
(
[column1] => value1
[column2] => value2
)
[1] => Array
(
[column1] => value1
[column2] => value2
)




I have a "database save" which has the model id allarrays and is set to save multiple records in to the correct database table. This is not working?

The debug shows that the SQL save array has nothing in it, I cannot understand it as the other DB saves are all still working but this new one which is pretty much the same is not.

I just see the below:

[90] => Array
(
[DB Save] => Array
(
[Queries] => Array
(
)

)

)

Thanks again for any help.
Regards

Hi ctrlmedia,

This is v5 or v6 ?

I think you have v5, with this setup you must have "allissues" in the model id field of the "db save" and it must have save multiple enabled.

Best regards,
Max

Yeah its CFv5 I didn't realize that you had a CFv6.

I just realized that this is in the CCv6 thread so I don't mind making a new thread as this line of questioning is now a CFv5 question.

The "allissues" is in the model id field of the DB save and multiple records is set to "yes" that's what is confusing me - all looks set up OK, I was wondering whether the way I was putting the array together may have caused issues?

Thanks again.

Hi ctrlmedia,

I have updated the topic, no problems!

What about "Save data under model id", is it enabled ? the columns names match the table fields names ? and update conditions set ?

Best regards,
Max

Thanks Max,
All settings were fine - I got it working on its own in a more simple form.

I then moved the "allissues" DB save towards the top above all other DB saves and it seems to be working now. Have you any ideas why that might be?

Kind Regards

Hi ctrlmedia,

No, not without some debugging of the form.

Best regards,
Max