I need assistance in building this custom query. Given this data:
f_id | sub_id | field_name | field_val |
---|---|---|---|
79 | 263 | track_id | 57162 |
80 | 263 | track_title | Antoniomontes |
81 | 263 | info_2 | green |
82 | 263 | others | older |
83 | 264 | track_id | 37164 |
84 | 264 | track_title | veinticuatro treintado |
85 | 264 | info_2 | brown |
86 | 264 | others | young |
87 | 265 | track_id | 67165 |
88 | 265 | track_title | tartado |
89 | 265 | info_2 | red |
90 | 265 | others | custom |
I need to obtain an array with this structure:
Array (
[0] => stdClass Object
(
[sub_id] => 263
[track_title] => Antoniomontes
[track_id] => 57162
)
[1] => stdClass Object
(
[sub_id] => 264
[track_title] => veinticuatro treintado
[track_id] => 37164
)
[2] => stdClass Object
(
[sub_id] => 265
[track_title] => tartado
[track_id] => 67165
)
I previously only need to obtain two values (sub_id and track_title) for every index’s array, and I was using this query:
$result = $wpdb->get_results ( "
SELECT DISTINCT sub_id,field_val
FROM wp_cformsdata
WHERE field_name = 'track_title'
GROUP BY field_val
ORDER BY sub_id DESC
" );
But now I’m lost in how to add the "track_id" value to every array.
It’s important to note that this DB is saving form submissions, where "sub_id" is saving a given form submission, and the different data sent through the form is registered in the columns "field_name" and "field_val". In the resulting array I want to group the results by equal "track_title", because in the same "track_title" we can get different form submissions (that is, different sub_id). This is the reason why I’m using:
SELECT DISTINCT sub_id,field_val
2
Answers
You can use the following MySQL query (conditional aggregation) to get the desired result:
It seems that you’re using PHP? You could use that, to correct for the way you stored the data in the database. A basic approach could look like this:
First get the data from the database:
The trick here is JSON_OBJECTAGG() which works similar to how you have stored your form data.
Then when you process these results in PHP, you can decode the json and create the array you need.