MySQL & DataTables implementation to combine data from multiple tables OR filter data from one table

avasmat

Hello,

I'm hoping to get some help with how to implement the example described below. For reference, I'm not a coder; a 'beginner PHP hacker' is how I'd describe myself as.

Goal:

• Build an internal site, to display metrics for multiple internal teams.
• The home page needs to display the metrics for ALL teams, in one DataTables table.
• Each team would have its own page, where team-specific metrics would need to be displayed in a DataTables table.

Technical:

• Using the server-side implementation, as detailed here: https://datatables.net/examples/data_sources/server_side
• The database would contain either one MySQL table, or a MySQL table for each team.

Implementation Issue:

Option 1: Create one MySQL table containing metrics for ALL teams. This is great for displaying all the metrics on the homepage in one DataTables table. However:

• How would I separate the data from the MySQL tables to display metrics for each individual team, on their individual pages?

Option 2: Create separate MySQL tables containing metrics for each individual team. This would be great for displaying the metrics in one DataTables table for each individual team page. However:

• How would I combine the data from the MySQL tables to display metrics for ALL teams, on the homepage?

Please let me know if more details are required.

Any help is much appreciated! Thank you.

rf1234

I think you should get started learning about data modeling before you start coding. In what you mention there are a number of entities that may or may not be represented as a database table. These are at the minimum:
- team (name and other attributes that describe a team, e.g. sport they are playing, e.g. football, basketball etc.)
- metric (whatever that is ...)

Let's assume that "metric" is a synonym for "result". If so there is an 1:N relationship between "team" and "result" which means that the id of "team" is a foreign key in "result" - just to shortcut the entire process of data modelling. "team" is the parent and "result" is the child.

I would seriously consider using Editor as well. Using Editor you can:
- edit the "team" table: enter all your teams and describe them
- edit the metrics table: select a "team" and enter its "metrics"

Just take a look a the Editor examples in the docs. This one is good to get started:
https://editor.datatables.net/examples/simple/join.html

The example has "users" and those users can be located in "sites". One "site" can have many "users" but one "user" can only have one "site". That is 1:N or parent - child with "site" being the parent and "user" being the child.

I would definitely dismiss your option 2. Option 1 is good enough and you can filter your data tables by team, no problem.

rf1234

It all depends on how you model your logical entities. Let's assume that "metrics" is stuff like "ball possession percentage" and other things like that. Then you could easily have the following data model:
- team
- metric (name of the metric, description of the metric etc.)
- result

"result" would be the score a "team" has in a certain "metric". It would be a link between "team" and "metric". One team can have "results" in many "metrics" and one "metric" can occur in many results of many different teams.

This relationship between "team" and "metric" would be N:M. The results table would resolve this relationship. "team" : "result" is 1:N and "Metric" : "result" is 1:N too.

You might end up having three tables:
- team: (id, name of team, bla bla bla)
- metric: (id, name of metric, bla bla bla)
- result: (id, id of team as foreign key, id of metric as foreign key, achieved result, bla)

"result" wouldn't need its own id but using "data tables" you would probably prefer it having its own id because this table has attribute(s) of its own: e.g. "achieved result". Hence it is more than just a link table that only consists of 2 foreign keys.

avasmat

Thank you so much for the feedback. Will have to dig into it deeper to understand it. Cheers!