I am writing a module for drupal6/PHP5 using MySQL (and optionally PostgreSQL).
I have a MySQL table of fields (fid,name, type,class...) and a table of parameter values (lid, pid, fid, numval,stringval) which reference those fields (via "fid"). A parameter will only a numeric value(FLOAT/REAL) or a string value (TEXT), but not both (according to field "type").
For a given "lid" there are multiple/variable numbers of parameters/rows which I can easily retrieve as rows in a loop. What I would like to do is retrieve a single row of "values", where each column has the associated field name as its heading. In drupal such a query would return all field:value pairs as object attribute:value pairs in a single database call, which I figure would be more efficient.
Google has found me a few SQL examples "combining multiple rows into a single row", but they seem to assume pre-ordained labelling of a fixed number of columns/fields in the result set, and/or build column entries as single composite "name:value" strings. I'm hoping there might be a more general approach for arbitrary numbers of fields, that works for both MySQL and PostgreSQL?
I only have very basic SQL experience, so I don't know whats possible regarding views or temporary tables or defining and executing procedures on the fly.
Thanks.
By way of example
FIELDS
FID NAME TYPE CLASS WIDGET SIZE UNITS DESCRIPTION .....
1 voltage float instrument text 10
2 current float instrument text 10
3 size float sample text 10
4 colour text sample text 30
...
PARAMS
PID LID FID NUMVAL STRINGVAL
1 1 1 2.5 NULL
2 1 4 NULL 'blue'
3 2 1 2.7 NULL
4 2 2 13 NULL
5 2 3 27 NULL
6 2 4 NULL 'greenish blue'
with some select LID=1 expression returning a single row:
| voltage | colour |
-----------------------
| 2.5 | 'blue' |
and select LID=2
| voltage | current | size | colour |
------------------------------------------------
| 2.7 | 13 | 27 | 'greenish blue' |
This displays the data vertically rather than horizontally.
SELECT
FIELDS.NAME AS PROPERTYNAME,
CASE WHEN PARAMS.NUMVAL IS NULL THEN STRINGVAL ELSE CAST(PARAMS.NUMVAL AS VARCHAR(100)) END AS PROPERTYVALUE
FROM
PARAMS, FIELDS
WHERE
PARAMS.FID = FIELDS.FID
AND
PARAMS.LID = 1
ORDER BY
PARAMS.FID
The SQL may need some tweaking, but it is the best I can do without access to your DB schema.
Pivot table stuff seems just too messy. I am thinking to just go with something simple like this (Thanks John Pick for showing me how to use CASE!):
SELECT p.l_id,
GROUP_CONCAT(
CONCAT(f.name,'=',
CASE WHEN p.stringval IS NULL
THEN p.numval
ELSE p.stringval
END
) SEPARATOR '||'
) AS vals
FROM
params AS p
LEFT JOIN fields AS f
ON p.f_id=f.f_id
WHERE
p.l_id=1
GROUP BY
p.l_id;
and just post-process the "vals" string with explode() in PHP.
+------+---------------------------------------------------------- ... -+
| l_id | vals |
+------+---------------------------------------------------------- ... -+
| 1 | kv=50||ma=50||pressure=0||temp_ccd=-58||temp_sample=150|| ... |
+------+--------------------------------------------------------- ... +
What a cop out! I would've expected a much more elegant solution given that all the information required to return columns in correct format exists in the PARAMS AND FIELDS tables. And now I have to worry about stringvals with SEPARATORs in them :-/