This has probably been asked before, but it's such a complicated topic I'm having trouble getting my head around it. So thought I'd ask specifics.

I have the following "CampaignCalls" table, e.g.:

```
ID | contactName | contactNumber
1  | Joe Bloggs  | 123456789
2  | Simon Smith | 456987321
3  | Jane Doe    | 852936414

```

In addition I have the one-to-many table of "custom columns" that are user-defined, "CampaignCall\_Fields", e.g.:

```
ID | fieldName
1  | Company
2  | Alternative Number
3  | Address 1 
4  | Address 2

```

And an intersect table that defines each corresponding value for the custom column ("CampaignCall\_Field\_values"), e.g.:

```
CampaignCall_ID | field_ID | value
1               | 1        | ACME
1               | 2        | 789456123
1               | 3        | 123 Fake St
1               | 4        | London
2               | 1        | Initech
2               | 2        | 789456123
2               | 3        | 456 Fake St
2               | 4        | Paris
3               | 1        | Greendale
3               | 2        | 789456123
3               | 3        | 789 Fake St
3               | 4        | New York City

```

I have an application that is supposed to be able to display, to users, a report of all the rows, such as in the format below:

```
Name        | Number    | Company | Address 1   | Address 2
Joe Bloggs  | 123456789 | ACME    | 123 Fake St | London
Simon Smith | 456987321 | Initech | 456 Fake St | Paris

```

But I also want to give users the capability to create filters, on any of the columns specified in this example, e.g. a user can say "only return rows where Name = Joe Bloggs AND Company = ACME".

**CURRENTLY** I am doing this by pulling through all data (filtered accordingly) from the "CampaignCalls" table, and then, in PHP, iterating through all returned rows and fetching the data from the "CampaignCall\_Field\_values" table (filtered accordingly), and then pivoting the data into the main array (if not all the data is returned, I know that a filter has "filtered out" that row and the row is removed from the array).

This is very inefficient as it takes a long time and opens a different database connection for each row. So I am looking to see if there is a way of reducing the number of database connections and/or the complexity of the algorithm.

I am looking to do this ideally by somehow create some database view that contains all the dynamic fields, with appropriate indexes etc. and then running the report against that. This seems the cleanest way, but not sure how I would dynamically create such a view. Also not sure as the performance implications of this.

Could someone provide any insight or opinion on how to implement this solution, or an alternative, better solution? I've been pulling my hair out here to create a stable, efficient solution, and I can't believe it's never been done before. Thanks in advance!