title: How to display a complex recordset in a simple sort-able table structure in php/mysql
body: <p>I am writing a reporting script, where reports fields are not always fixed. Admin may add/remove/modify fields. I have created below tables for that,</p>
  
  <p><strong>reports</strong></p>
  
  <pre><code>id  user_id csp_id  date
  1       1       1   2018-10-09
  2       1       1   2018-10-10
  3       2       2   2018-10-09
  4       2       2   2018-10-10
  </code></pre>
  
  <p><strong>reports_meta_fields</strong></p>
  
  <pre><code>meta_id      display_name  type  default_value
  csp_address  CSP address   text
  csp_name     CSP Name      text
  </code></pre>
  
  <p><strong>reports_meta_values</strong></p>
  
  <pre><code>report_id   user_id     meta_id         meta_value
  1           1           csp_address     Shivmandir
  1           1           csp_name        Shiv CSP
  2           1           csp_address     Matigara
  2           1           csp_name        Mat CSP
  3           2           csp_address     Darjeeling
  3           2           csp_name        Dar CSP
  4           2           csp_address     Gangtok
  4           2           csp_name        Gang CSP
  </code></pre>
  
  <p>This is how the date need to be displayed in the frontend, I also need a sorting with date, csp_name etc</p>
  
  <pre><code>id  user_id csp_id  date        csp_name    csp_adress
  1       1       1   2018-10-09  Shiv CSP    Shivmandir
  2       1       1   2018-10-10  Mat CSP     Matigara
  3       2       2   2018-10-09  Dar CSP     Darjeeling
  4       2       2   2018-10-10  Gang CSP    Gangtok
  </code></pre>
  
  <p>I have tried this sql, but this not what I am looking for</p>
  
  <pre><code>SELECT reports.*, reports_meta_values.meta_id, reports_meta_values.meta_value  FROM reports
  INNER JOIN reports_meta_values WHERE reports.id = reports_meta_values.report_id
  
  
  id  user_id     csp_id      date        meta_id         meta_value
  1   1           1           2018-10-09  csp_address     Shivmandir
  1   1           1           2018-10-09  csp_name        Shiv CSP
  2   1           1           2018-10-10  csp_address     Matigara
  2   1           1           2018-10-10  csp_name        Mat CSP
  3   2           2           2018-10-09  csp_address     Darjeeling
  3   2           2           2018-10-09  csp_name        Dar CSP
  4   2           2           2018-10-09  csp_address     Gangtok
  4   2           2           2018-10-09  csp_name        Gang CSP
  </code></pre>
  
  <p>I am looking for a suggestion in mysql or in php how to solve this issue. Thanks!</p>