NotionCommotion Posted August 6, 2016 Share Posted August 6, 2016 (edited) EDIT. Never mind. I should be able to figure this out using GROUP_CONCAT and a loop. Can't seem to delete the post, however. I would like a generic approach to store and display matrices such as the following. To store the data, I am thinking of the following schema. While this is a PHP question, if you feel I haven't modeled it correctly, please advise. Given the chart ID, how would it be best to retrieve the data and create the table? Edited August 6, 2016 by NotionCommotion Quote Link to comment Share on other sites More sharing options...
Barand Posted August 6, 2016 Share Posted August 6, 2016 According to that model, a chart can have many categories but a category can belong to only one chart. Quote Link to comment Share on other sites More sharing options...
NotionCommotion Posted August 6, 2016 Author Share Posted August 6, 2016 According to that model, a chart can have many categories but a category can belong to only one chart.Agreed, however this is acceptable. Thanks Quote Link to comment Share on other sites More sharing options...
Barand Posted August 6, 2016 Share Posted August 6, 2016 So if your categories are the continents then you could have a chart showing population growth, say, but you could not then have another chart for those continents showing economic growth. Quote Link to comment Share on other sites More sharing options...
NotionCommotion Posted August 6, 2016 Author Share Posted August 6, 2016 Data is user inputted and if they want, they will have to re-input data for the next chart, however they will most likely not want to. Actually I am still stumped as the cross table may not have a record for a given series/category and I therefore can't always join series and.categories. Quote Link to comment Share on other sites More sharing options...
Barand Posted August 6, 2016 Share Posted August 6, 2016 perhaps +-------------+ | chart | +-------------+ +-------------+ | +---------------+ | series | | | category | +-------------+ | +---------------+ | | | | /|\ | | +-------------+ | +-------------<| value |>--------------+ +-------------+ Quote Link to comment Share on other sites More sharing options...
NotionCommotion Posted August 6, 2016 Author Share Posted August 6, 2016 Thanks Barand, but this would require a value for each series or category. Guess I will just do it in two queries: one to get say the series with a left outer join for values, and group_concate the values, and a second query for the categories. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.