PHP json formatting

Question

I am creating an inbox where each section of messages is grouped into months.

Im not sure what is the best way to go about achieving this is. Maybe I have an inefficient table design? or is there a method i'm missing, if someone would mind pointing me in the right direction that would be a massive help.

I have a table set up as below:

I want to achieve the following json from this table:

[  
   {  
      "messagesCollections":[  
         {  
            "month":"Oct19",
            "messages":[  
               {  
                  "id":2,
                  "user_id":4,
                  "time":"2019-10-0317:34:00",
                  "message":"Test",
                  "header_image":"***/imboxHeader-min.png",
                  "title":"testtitle",
                  "message_part2":"test2",
                  "middle_image":"***/inboxMiddleImage-min.png",
                  "read":0,
                  "read_time":"0000-00-0000:00:00"
               }
            ]
         },
         {  
            "month":"Sep19",
            "messages":[  
               {  
                  "id":3,
                  "user_id":4,
                  "time":"2019-09-0317:34:00",
                  "message":"Test",
                  "header_image":"***/imboxHeader-min.png",
                  "title":"testtitle",
                  "message_part2":"test2",
                  "middle_image":"***/inboxMiddleImage-min.png",
                  "read":0,
                  "read_time":"0000-00-0000:00:00"
               }
            ]
         },
         {  
            "month":"Sep18",
            "messages":[  
               {  
                  "id":4,
                  "user_id":4,
                  "time":"2018-09-0317:34:00",
                  "message":"Test",
                  "header_image":"***/imboxHeader-min.png",
                  "title":"testtitle",
                  "message_part2":"test2",
                  "middle_image":"***/inboxMiddleImage-min.png",
                  "read":0,
                  "read_time":"0000-00-0000:00:00"
               }
            ]
         }
      ]
   }
]

I am using the following code:

function getMessages($id) {

        $messagesArray = [];

        $connection = new db("app");
        $result = json_decode($connection->select("SELECT * FROM messages WHERE `user_id` = '$id'"), true);

        for ($x = 0; $x <= count($result) - 1; $x++) {

            $arrayName = date("M y",strtotime($result[$x]["time"]));
            $messagesArray[0][$arrayName]["messages"][$x] = $result[$x];
        }

        return $messagesArray;
    }

which produces this:

 [  
   {  
      "messagesCollections":[  
         {  
            "Oct 19":{  
               "messages":[  
                  {  
                     "id":1,
                     "user_id":4,
                     "time":"2019-10-03 17:34:00",
                     "message":"Test",
                     "header_image":"https:\/\/s3.eu-west-2.amazonaws.com\/clubmission\/imboxHeader-min.png",
                     "title":"test title",
                     "message_part2":"test 2",
                     "middle_image":"https:\/\/s3.eu-west-2.amazonaws.com\/clubmission\/inboxMiddleImage-min.png",
                     "read":0,
                     "read_time":"0000-00-00 00:00:00"
                  },
                  {  
                     "id":2,
                     "user_id":4,
                     "time":"2019-10-03 17:34:00",
                     "message":"Test",
                     "header_image":"https:\/\/s3.eu-west-2.amazonaws.com\/clubmission\/imboxHeader-min.png",
                     "title":"test title",
                     "message_part2":"test 2",
                     "middle_image":"https:\/\/s3.eu-west-2.amazonaws.com\/clubmission\/inboxMiddleImage-min.png",
                     "read":0,
                     "read_time":"0000-00-00 00:00:00"
                  }
               ]
            },
            "Sep 19":{  
               "messages":{  
                  "2":{  
                     "id":3,
                     "user_id":4,
                     "time":"2019-09-03 17:34:00",
                     "message":"Test",
                     "header_image":"https:\/\/s3.eu-west-2.amazonaws.com\/clubmission\/imboxHeader-min.png",
                     "title":"test title",
                     "message_part2":"test 2",
                     "middle_image":"https:\/\/s3.eu-west-2.amazonaws.com\/clubmission\/inboxMiddleImage-min.png",
                     "read":0,
                     "read_time":"0000-00-00 00:00:00"
                  }
               }
            },
            "Sep 18":{  
               "messages":{  
                  "3":{  
                     "id":4,
                     "user_id":4,
                     "time":"2018-09-03 17:34:00",
                     "message":"Test",
                     "header_image":"https:\/\/s3.eu-west-2.amazonaws.com\/clubmission\/imboxHeader-min.png",
                     "title":"test title",
                     "message_part2":"test 2",
                     "middle_image":"https:\/\/s3.eu-west-2.amazonaws.com\/clubmission\/inboxMiddleImage-min.png",
                     "read":0,
                     "read_time":"0000-00-00 00:00:00"
                  }
               }
            }
         }
      ]
   }
]

any ideas?

jhaagsma · Accepted Answer · 2018-10-04 18:17:19Z

1

You're not setting "month" anywhere; so the output makes sense; I haven't tested this, but this might work (or at least be in the correct direction):

function getMessages($id) {

    $messagesArray = [];

    $month_indices = [];

    $connection = new db("app");
    $result = json_decode($connection->select("SELECT * FROM messages WHERE `user_id` = '$id'"), true);

    for ($x = 0; $x <= count($result) - 1; $x++) {

        $arrayName = date("M y",strtotime($result[$x]["time"]));

        if (isset($month_indices[$array_name]) {
            $index = $month_indices[$array_name];
        } else {
            $index = count($month_indices);
            $month_indices[$array_name] = $index;
            $messagesArray[$index]["messages"] = [];

        }

        $messagesArray[$index]["month"][0] = $arrayName
        $messagesArray[$index]["messages"][] = $result[$x];
    }

    return $messagesArray;
}

That should fix the grouping by month, I think? Edit to prevent overwriting of array

edited Oct 4, 2018 at 18:17

answered Oct 3, 2018 at 21:08

jhaagsma

2,4642 gold badges27 silver badges28 bronze badges

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

3 Comments

MattBlack Over a year ago

Hi, thanks for replying, unfortunately by doing that I have lost the grouping of the messages by month.

jhaagsma Over a year ago

Oh, I see what you were doing there; yea, that's fair; I can adjust that for you...

jhaagsma Over a year ago

Just realized there was a mistake in there where it'd overwrite things; this should fix, i think

Collectives™ on Stack Overflow

PHP json formatting

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related