How can I select values from dedicated 'fields' table?

Question

I have two tables in my MySQL database, here they are:

I want to have some records with fields that are not predefined at the table creation stage, so I can use that pair of tables to store records with different fields set.

I fill those tables with the following records:

And I want to write select query that would return following result:

Is there any way to do that in MySQL, or should I select both tables and process them in PHP to achieve the result I need?

UPD: Please mention that I don't know what will be the names of the fields beforehand, so I can't hardcode 'name's of the fields into the select query.

Answer 1

Try this:

select t.record_id, t.tim, t.source
     , sum(case when field_name = 'length' then field_value end) length
     , sum(case when field_name = 'width' then field_value end) width
     , sum(case when field_name = 'height' then field_value end) height
  from records t, fields tt
 where t.record_id = tt.record_id
 group by t.record_id, t.tim, t.source
 order by t.record_id

It is impossible to make unlimited set of field_names in MySQL, Oracle provides the PIVOT functionality that allows you to receive unlimited set of fields but it returns XML.

Answer 2

You can do it with dynamic SQL by retrieving the column names and generating the query (eg: How To have Dynamic SQL in MySQL Stored Procedure)

However I'd definitely go down the PHP route for this. It's heavily string manipulation intensive and that's not really something that SQL is designed for.

The final query will look something like (pseudocode):

SELECT 
  ID
  ,Time
  ,Source
  ,SUM(CASE WHEN field_name = 'Length' THEN field_value END) AS [Length]
  ,etc
FROM records
  INNER JOIN fields
GROUP BY
  ID

With a different CASE statement for each distinct field name found.