Manage SQL query multidimensional array output as stdClass Object

Question

I using an MVC framework with controllers and routes on PHP 5.6.

I am trying to define some PHP variables from the database.

+---------+---------+-------------+---------------+
| meta_id | post_id |  meta_key   |  meta_value   |
+---------+---------+-------------+---------------+
|    3506 |     147 | event_flag  | 1             |
|    3507 |     147 | event_year  | 2019          |
|    3508 |     147 | event_title | Soccer Encore |
+---------+---------+-------------+---------------+

Here I call the query.

// Controller:
public function __construct()
    {

        parent::__construct();

        $this->_data['checklist_content'] = $this->_models['wp_stream_settings']->getChecklistMeta(147);
    }

Here is the query:

// Model:
function getChecklistMeta($item_id){ // passed $item_id = 147
     $sql = '
        SELECT *
        FROM '.PREFIX.'wp_postmeta
        where post_id = '.$item_id.' and meta_key IN ( "event_flag", "event_year", "event_title" );
    ';
    return $this->db->select($sql, array(':ID' => $item_id));
}

This query will give all the information needed and put the variables in an stdClass object:

// Data
[checklist_content] => Array
    (
        [0] => stdClass Object
            (
                [meta_id] => 3506
                [post_id] => 147
                [meta_key] => event_flag
                [meta_value] => 1
            )

        [1] => stdClass Object
            (
                [meta_id] => 3507
                [post_id] => 147
                [meta_key] => event_year
                [meta_value] => 2019
            )

        [2] => stdClass Object
            (
                [meta_id] => 3508
                [post_id] => 147
                [meta_key] => event_title
                [meta_value] => Soccer Encore
            )
    )

But I can not manage the array as I want:

echo $data['checklist_content']->{'event_title'};

I tried to use array_search:

print_r(array_search("event_title",$this->_data['checklist_content']));

with no avail.

I could modify the SQL query in order to have more manageble stdClass object, or maybe remapping it in a JSON format, or else.

How can I modify the query or else, in order to get something like this:

// Data
[checklist_content] => Array
    (
        [event_flag] => 1

        [event_year] => 2019

        [event_title] => Soccer Encore

    )

So that I could easily display the value "Soccer Encore" like this:

echo $data['checklist_content']->{'event_title'};

for now I am forced to get the variables like this:

echo $data['checklist_content'][2]->{'meta_value'}; // Outputs "Soccer Encore"

I am open to redesign the query or even to use a JSON method or a serialisation to be able to mange better the variables.

As advice for general system design, you should split that table into three different tables or hold all the data in one row. Holding all the information for a certain post in one table with multiple rows is _very_ costly. — shn, Jun 17 '19 at 18:30

score 1 · Answer 1 · answered Jun 17 '19 at 18:53

1

For PHP >= 7.0.0 get meta_value and index it by meta_key:

$result = array_column($data['checklist_content'], 'meta_value', 'meta_key');

Then:

echo $result['event_title'];  //displays Soccer Encore

If you need to preserve other properties:

$result = array_column($data['checklist_content'], null, 'meta_key');

Then:

echo $result['event_title']['meta_value'];  //displays Soccer Encore

answered Jun 17 '19 at 18:53

AbraCadaver

78,200
7
66
87

This is exactly what I was looking for, but for PHP 5.6. I updated the question accordingly. – Mau Jun 18 '19 at 01:55

score 0 · Answer 2 · answered Jun 24 '19 at 04:14

The accepted answer is correct but only working for PHP70, for PHP56 I solved the problem creating an additional array using a 'for loop' inserting meta_key and meta_value pairs from the messy array $this->_data['checklist_content'] :

// Data
[checklist_content] => Array
    (
        [0] => stdClass Object
            (
                [meta_id] => 3506
                [post_id] => 147
                [meta_key] => event_flag
                [meta_value] => 1
            )

        [1] => stdClass Object
            (
                [meta_id] => 3507
                [post_id] => 147
                [meta_key] => event_year
                [meta_value] => 2019
            )

        [2] => stdClass Object
            (
                [meta_id] => 3508
                [post_id] => 147
                [meta_key] => event_title
                [meta_value] => Soccer Encore
            )
    )

and added them to a new stdClass array: $this->_data['campaign_settings'].

<?php
// order stdClass data. parse data pairs (meta_key and and meta_value) 
// as new objects in the $this->_data['campaign_settings'] array
for ($i = 0; $i < count($this->_data['checklist_content']); $i++) {
    // create another array pair
    $nameKey = $this->_data['checklist_content'][$i]->{'meta_key'};
    $valueKey = $this->_data['checklist_content'][$i]->{'meta_value'};
    $this->_data['campaign_settings']->$nameKey = $valueKey;
}
?>

the new array shows as follows:

Array
(
    [campaign_settings] => stdClass Object
        (
            [event_flag] => 1
            [event_year] => 2019
            [event_title] => Soccer Encore
        )
)

and these are the desired outputs from this query:

echo $this->_data['campaign_settings']->{'event_title'}; // outputs "Soccer Encore"
echo $this->_data['campaign_settings']->{'event_year'}; // outputs "2019"
echo $this->_data['campaign_settings']->{'event_title'}; // outputs "1"

score -1 · Answer 3 · answered Jun 17 '19 at 18:25

-1

To convert stdobject of database to json use json_decode It convert object into2d array

answered Jun 17 '19 at 18:25

Nitesh Mangla

109
5

`json_decode` decodes (not encodes) object and the result of `json_encode` is not "2d array", it is JSON string – user1597430 Jun 17 '19 at 18:50

Manage SQL query multidimensional array output as stdClass Object

3 Answers3