My goal is to store a form.
Form structure:
Section1
section1 table
Section2
section2 table
....
Section S
sectionS table
The tables look like these except i need sum (totals) for both rows and columns. Tables are dynamic in size M by N
I need to generate 2D table values (with slight variations) on many pages so I want it to be done by the mysql sever instead of with PHP. I’m not sure if I should use views, prepared queries/pivot table, or PHP for this.
I believe this is the correct database schema for storing the form (corrections / suggestions wanted).
I like this idea stackoverflow post, but It's too far over my head. This is what i have finished before stopping to make sure there is not a better solution.
set @sql = 'COSID ';
set @sql = concat('select CommitSources.StandardSource, ', @sql, "from CommitSources where CommitSources.CSID = '20' group by CommitSources.COSID;");
PREPARE stmt FROM @sql;
EXECUTE stmt;
Question:
I would like a solution that allows me to send a form PK and section PK and return rows like this so that I can easily manipulate/output them. (*Note this output is missing a row with column totals)
- What is the best way to do this? (PHP, mysql view, mysql prepared, other)
- Is my database schema correct and normalized correctly for storing this data?
