I have a custom form builder where admins can create fields and forms separately so that the fields are reusable on multiple forms. The fields can be reordered by admins at any time and have new fields added or existing ones removed, even after submissions have been made. I have 5 tables for this: forms, fields, a form_fields linking table, submissions, and a submission_fields table which is both a linking table and also contains the values of the submitted fields.

The problem is with the ordering of data with existing submissions if a field’s position has been moved. In this SQLFiddle, I have simulated a submission for a form where the order of the fields was numerical (1 (first name), 2 (last name), 3 (email address), 4 (address), 5 (city), 6 (state), 7 (zip), and 8 (phone)), but then the phone number field (#8) was moved up under the email address field, changing the order to 1, 2, 3, 8, 4, 5, 6, 7.

In the main query, everything comes back correctly. In the subqueries for field_ids and field_values, however, they come back in the original order. So I need to get those into the same order as they come back in the main query.