Hi Duane,
Now, if you have mySQL 4.1+, then this may not be too bad ... bearing in mind I haven’t used FormProcessor so I’m going on my interpretation of the description you’ve given above.
If I understand the problem correctly, then you have a table with at least three fields (form ID, field, value) and you need to transpose the name field into a column so you can sort on each of the fields.
In this case, here is an example query that illustrates one way in which you can do the transposition (with some made-up dummy values):
SELECT id,
group_concat( if( field = 'month', value, NULL ) ) AS Month,
group_concat( if( field = 'number of children', value, NULL ) ) AS NumberOfChildren
FROM `submissions`
GROUP BY id
ORDER BY NumberOfChildren;
Essentially, the ’group_concat’ statements does all the work in transposing the values, the IF statement either returns the value if it’s available or a null if it’s not, and the ’AS ...’ bit is the name of the column you wish to create. You could add as many of these as you have possible field names (I don’t know how fast performance degrades doing this type of manipulation but it’s something to watch for). To sort the values, you just need to change the ORDER BY clause as required.
(something to watch out for is that mySQL could turn the values into BLOBs if the query reaches a certain length, so if you’re using phpMyAdmin you will need to export the data to visually see the values being returned in the query - the values will display fine through MODx as well)
Obviously, if you’re not running mySQL 4.1+, then it’s back to drawing board
MODX Staff
☆ A M B ☆
MODX Staff
