Wordpress Question posted in
The official Wordpress documentation can be found here.

WordPress – MySQL query to obtain an array of results

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

Tags:

2

Answers


  1. 0

    You can use the following MySQL query (conditional aggregation) to get the desired result:

    select
    sub_id,
    max(case when field_name = 'track_id' then field_val end) as track_id,
    max(case when field_name = 'track_title' then field_val end) as track_title
from t
group by sub_id
    Login or Signup to reply.
  2. 0

    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:

    $result = $wpdb->get_results("SELECT
                                sub_id,
                                JSON_OBJECTAGG(field_name, field_val) AS sub_json
                              FROM  wp_cformsdata
                              GROUP BY sub_id 
                              ORDER BY sub_id DESC");

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search