Manipulate the result of MySQL query into the desired JSON structure in PHP

Question

I have this table containing data: JSON result from query:

[{
    "cnt": "1",
    "category_name": "Entertainment",
    "event_name": "Typhoon Sample",
    "year_of_event": "2000"
}, {
    "cnt": "1",
    "category_name": "Heavy Rainfall Warning and Advisory",
    "event_name": "Typhoon Abra",
    "year_of_event": "2015"
}, {
    "cnt": "1",
    "category_name": "Daily Post",
    "event_name": "No Event",
    "year_of_event": " "
}, {
    "cnt": "1",
    "category_name": "Weather Forecast",
    "event_name": "No Event",
    "year_of_event": " "
}, {
    "cnt": "2",
    "category_name": "Actual Docs",
    "event_name": "Holloween",
    "year_of_event": "2018"
}, {
    "cnt": "1",
    "category_name": "Daily Post",
    "event_name": "Holloween",
    "year_of_event": "2018"
}]

I'm trying to transform it like this:

{
    "category": ["Typhoon Sample 2000", "Typhoon Abra 2015", "No Event ", "Holloween 2018"],
    "series": [{
        "name": "Entertainment",
        "data": ["1",0,0,0]
    }, {
        "name": "Heavy Rainfall Warning and Advisory",
        "data": [0,"1",0,0]
    }, {
        "name": "Daily Post",
        "data": [0,0,"1", "1"]
    }, {
        "name": "Weather Forecast",
        "data": [0,0,"1",0]
    }, {
        "name": "Actual Docs",
        "data": [0,0,0,"2"]
    }]
}

What it does is, unique event_name is group then based on its position or index, the series properties will contain the name of each unique categories with the data from the query result. For example, if it does not have value fro Entertainment category, that event_name will have a value of 0.

What I've done so far:

while($row = $result_select->fetch_assoc()) {
        $evennt = $row["event_name"]." ".$row["year_of_event"];
        if (!in_array($evennt, $dbdata["category"])){
            $dbdata["category"][]=$evennt;
        }

        $ccat = $row["category_name"];
        $category_names = array_column($dbdata["series"], 'name');  
        if (!in_array($ccat, $category_names)){
            $dbdata["series"][] = array(
                 'name' => $ccat,
                 'data' => []
            );
        }
        print_r ($dbdata["series"]);
        echo '<br/>';
        print_r ($dbdata["category"]);
        echo '<br/>';
        echo 'Current '.$evennt.' --'.$ccat.'<br/>';
        foreach (array_values($dbdata["series"]) as $i => $value)  {

            foreach (array_values($dbdata["category"]) as $ii => $valuee){              

                if(($value["name"] == $ccat) && ($valuee==$evennt ) && ($row["cnt"])){
                    array_push($dbdata["series"][$i]["data"],$row["cnt"]);
                    echo $ii.' : '.$value["name"].' : '.$valuee.' with value '.$row["cnt"].' <br/>';
                }else if (in_array($evennt, $dbdata["category"]) && in_array($ccat, $category_names)){
                    array_push($dbdata["series"][$i]["data"],0);
                    echo $ii.' : '.$value["name"].' : '.$valuee.' without value 0 <br/>';
                }

I'm stuck with the else statement. Thanks.

Result from the code above:

{
    "category": ["Typhoon Sample 2000", "Typhoon Abra 2015", "No Event ", "Holloween 2018"],
    "series": [{
        "name": "Entertainment",
        "data": ["1", 0, 0, 0, 0]
    }, {
        "name": "Heavy Rainfall Warning and Advisory",
        "data": ["1", 0, 0, 0, 0]
    }, {
        "name": "Daily Post",
        "data": ["1", 0, 0, 0, "1"]
    }, {
        "name": "Weather Forecast",
        "data": ["1", 0, 0, 0, 0]
    }, {
        "name": "Actual Docs",
        "data": ["2", 0, 0, 0, 0]
    }]
}

EDIT: I might have the wrong title, it's not actually transforming the JSON structure, my problem is how to manipulate the result of my query into the desired JSON structure. Also, The code snippet above does achieve the structure or format but the values in data is wrong.

Please store data in a multidimensional array and then do json_encode it will transform the array in to the format as you've mentioned. — Rajender Verma
– Rajender Verma, Commented Oct 29, 2018 at 8:29
Firstly, please extract a minimal reproducible example, without DB access but mock data and formatted according to PSR standards. Then, "stuck with else statement" is far from a suitable description, elaborate on that. Further, any JSON modification starts with decoding it (it's not JSON anymore then!), then running whatever algorithm you need on the data and then encoding it again. Unless the encoding or decoding is your problem, the fact that it's encoded as JSON at some point can be removed from your question completely! Do that in order to focus on the problem. — Ulrich Eckhardt
– Ulrich Eckhardt, Commented Oct 29, 2018 at 8:48
@UlrichEckhardt I just want to transform the JSON structure before encoding it and passing it to the client. — Matt Mateo
– Matt Mateo, Commented Oct 29, 2018 at 9:00
@ADyson Yeah, you are right, it is NOT a JSON object yet, that is my main objective to throw a JSON Object with the format that is mentioned above. My present code manipulates the result of the query in which if outputted as JSON to the client has the structure above. — Matt Mateo
– Matt Mateo, Commented Oct 29, 2018 at 11:43

ADyson · Accepted Answer · 2018-10-29 17:30:08Z

1

I wasn't quite sure about your original logic - there were too many flaws to make it easy to fix, so instead I did a complete rewrite:

//prefetch all the DB rows, because we'll be looping them twice
$data2 = $result_select->fetch_all(MYSQLI_ASSOC); 

//next, populate the series and category lists by looking through the data
foreach ($data2 as $row) {
    $evennt = $row["event_name"]." ".$row["year_of_event"];
    if (!in_array($evennt, $dbdata["category"])){
        $dbdata["category"][]=$evennt;
    }

    $ccat = $row["category_name"];
    $category_names = array_column($dbdata["series"], 'name');  
    if (!in_array($ccat, $category_names)){
        $dbdata["series"][] = array(
             'name' => $ccat,
             'data' => []
        );
    }
}

//now process the information to produce the "data" array
foreach ($dbdata["series"] as $serKey => $ser)
{
  foreach ($dbdata["category"] as $catKey => $cat)
  {
    $dbdata["series"][$serKey]["data"][$catKey] = 0; //default value

    foreach ($data2 as $rowKey => $row)
    {
      $rowCat = $row["event_name"]." ".$row["year_of_event"];
      $rowName = $row["category_name"];

      if ($rowName == $ser["name"] && $rowCat == $cat) {
        $dbdata["series"][$serKey]["data"][$catKey] = intval($row["cnt"]);
      }
    }
  }
}

echo json_encode($dbdata);

A demo (using static data instead of SQL row data) can be found here: http://sandbox.onlinephpfunctions.com/code/6744c95bbbc0043cc015dc10069c7e045154d03c

answered Oct 29, 2018 at 17:30

ADyson

62.9k16 gold badges86 silver badges100 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

markus Over a year ago

I haven't tested if this works and I also haven't benchmarked it and it may just solve the issue and be ok-ish. But just generally speaking 4 nested control structures, 3 of which are loops = code smell. Or in clearer words, I'd never let it go past a code review. :)

ADyson Over a year ago

@markus I agree it's not ideal, but the transformation the OP wants to do is quite...interesting, shall we say. There are probably ways to make the code nicer and possibly optimise performance, or maybe better use of SQL can be made...but I wasn't inclined to spend all day on a question like this TBH. At least now OP has a workable algorithm and maybe they can then improve on the process themselves by using some array functions etc. Or be my guest if you want to make a better fist of it yourself. P.S. the sandbox link will let you test it (saving the DB code, of course) :-)

Collectives™ on Stack Overflow

Manipulate the result of MySQL query into the desired JSON structure in PHP

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related