How to create JSON from Query in PHP

Question

I have been looking and trying many different ways to create the JSON below from a postgres table using PHP, but did not work, specially to add the "group". Group should be added when there are different date, so it would create groups by date, as the example below. Does anyone know how can I do it?

EXPECTED:

"t": [

                {
                    "group": "2015-03-25",
                    "list": [{
                        "t": 1,
                        "titulo": "Pages - Multi-Purpose Admin Template Revolution Begins here!",
                        "to": ["David Nester", "Jane Smith"],
                        "time": "5 min. atrás",
                        "datetime" : "Today at 1:33pm",
                        "from": "David Nester",
                        "icone": 1
                    }, {
                        "t": 2,
                        "titulo": "Your site has some very imaginative animation /movement, especially the Sluggo! ",
                        "to": ["Anne Simons"],
                        "time": "45 min. atrás",
                        "datetime" : "Today at 1:33pm",
                        "from": "Anne Simons",
                        "icone": 2
                    }, {
                        "t": 3,
                        "titulo": "Recently ordered a new pair of soccer cleats from your website on June 21",
                        "to": ["Herald Menster"],
                        "time": "13:33",
                        "datetime" : "Today at 1:33pm",
                        "from": "David Nester",
                        "icone": 1
                    }, {
                        "t": 4,
                        "titulo": "Everything here, Made Just for you :)",
                        "to": ["John Doe"],
                        "time": "11:23",
                        "datetime" : "Today at 11:23am",
                        "from": "David Nester",
                        "icone": 3
                    }, {
                        "t": 5,
                        "titulo": "Simplicity is the ultimate sophistication",
                        "to": ["John Doe", "Anne Simons"],
                        "time": "22:33",
                        "datetime" : "Today at 10:33pm",
                        "from": "David Nester",
                        "icone": 2
                    }]
                }, {
                    "group": "2015-03-24",
                    "list": [{
                        "t": 6,
                        "titulo": "Good design is obvious. Great design is transparent",
                        "to": ["John Doe", "Anne Simons"],
                        "time": "13:33",
                        "datetime" : "Today at 1:33pm",
                        "from": "David Nester",
                        "icone": 1
                    }, {
                        "t": 7,
                        "titulo": "Your site has some very imaginative animation /movement, especially the Sluggo! ",
                        "to": ["Anne Simons"],
                        "time": "45 mins ago",
                        "datetime" : "Today às 13:33",
                        "from": "Anne Simons",
                        "icone": 2
                    }, {
                        "t": 8,
                        "titulo": "Aliquam est tellus, fringilla egestas fermentum quis",
                        "to": ["John Doe", "Anne Simons"],
                        "time": "13:33",
                        "datetime" : "Today at 1:33pm",
                        "from": "David Nester",
                        "icone": 2
                    }, {
                        "t": 9,
                        "titulo": "Aliquam est tellus, fringilla egestas fermentum quis",
                        "to": ["John Doe", "Anne Simons"],
                        "time": "13:33",
                        "datetime" : "Today at 1:33pm",
                        "from": "David Nester",
                        "icone": 1
                    }

CURRENT CODE:

With this code below I get the following result, but it never add different "group", just add the first one:

echo '{ "t": [';

while ($row = pg_fetch_array($result, null, PGSQL_ASSOC)) {
    $data["group"] = $row["datetime"];
    $data["list"][] = $row; 
};

echo json_encode($data);

echo "]}";

WRONG RESULT:

{
    "t": [
        {
            "group": "2014-09-22",
            "list": [
                {
                    "t": "133640",
                    "titulo": "Some problem",
                    "to": "Erik",
                    "time": "9:30",
                    "datetime": "2014-09-22",
                    "from": "Julian",
                    "icone": "1"
                },
                {
                    "t": "133641",
                    "titulo": "Problems",
                    "to": "Robert",
                    "time": "9:30",
                    "datetime": "2014-09-22",
                    "from": "Julian",
                    "icone": "1"
                }
            ]
        }
    ]
}

Gopakumar Gopalan · Accepted Answer · 2015-03-25 11:29:46Z

1

May be you need to push $data into a $tickets array

$tickets = array();
while ($row = pg_fetch_array($result, null, PGSQL_ASSOC)) {
  $data["group"] = $row["datetime"];
  $data["list"] = $row;
  $tickets['tickets'][] = $data;
};

echo json_encode($data);

May this helps!

answered Mar 25, 2015 at 11:29

Gopakumar Gopalan

1,19714 silver badges22 bronze badges

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

4 Comments

Erik Figueiredo Over a year ago

Thanks for your answer, Gopakumar! Unfortunately it did not work. I changed $data to $tickets in the last line, but I got "group" repeating every single date. I did not explain so well at the first, but It should be added only when I have diferent group, in other words, different date. Do you have other sugestion? Thanks!

Gopakumar Gopalan Over a year ago

can I see the query part too?

Erik Figueiredo Over a year ago

Hi Gopakumar, thanks for your interest in help me. Fortunately I solved this problem already. Best regards!

Gopakumar Gopalan Over a year ago

I'm glad to hear you solved it by yourself. Please put your solution as an answer and make it accepted, so people who may face slimier situation can use your soultion.

fedmich · Accepted Answer · 2015-03-25 12:53:56Z

1

Try this.

<?php
$json = array();    //This is the MAIN array that you'll encode later

$json_tickets = array();        //This is the json tickets, 1st child / parent of all
$json_tickets["list"] = array();

while ($row = pg_fetch_array($result, null, PGSQL_ASSOC)) {

    $new_item = $row;           //Copy $row into a new variable, since you only want to modify $new_item later...

    $new_item["group"] = $new_item["datetime"];
    $json_tickets["list"][] = $new_item;            //keep adding this into a list array
};
$json['tickets'] = $json_tickets;

echo json_encode( $json );

Tips:

Don't mix your variables.
Don't cheat :) on this part of your code -> echo '{ "tickets": [';
All your table data on that table will show in your JSON result, be careful as it may include sensitive data later on. might be some security issue
hmm... what else can I put here as tip for you

answered Mar 25, 2015 at 12:53

fedmich

5,3733 gold badges39 silver badges52 bronze badges

2 Comments

Erik Figueiredo Over a year ago

Thanks for your answer! Thanks also for your recommendations, I really appreciate them. I tried your code, but it did not solve my needs. What I need is this: pages.revox.io/doc/1.1.0/jquery/email.php - that is the same structure, I just changed the field names.

fedmich Over a year ago

Erik, practice writing more readable codes as it will really help you out in the long-run. I'm just glad you now have a working and that your problem is now solve. Cheers to that

Navneet Garg · Accepted Answer · 2015-03-25 13:00:49Z

1

Create an array and than use json_encode php function

$data = array(
    "group" => "2015-03-24",
    "list" => array(
        "ticket" => 6,
        "titulo" => "Good design is obvious. Great design is transparent",
        "to" => array("John Doe", "Anne Simons"),
        "time" => "13:33",
        "datetime" => "Today at 1:33pm",
        "from"=> "David Nester",
        "icone"=> 1
    )


    );

echo json_encode($data);

this will give you out put like this

{"group":"2015-03-24","list":{"ticket":6,"titulo":"Good design is obvious. Great design is transparent","to":["John Doe","Anne Simons"],"time":"13:33","datetime":"Today at 1:33pm","from":"David Nester","icone":1}}

answered Mar 25, 2015 at 13:00

Navneet Garg

1,3721 gold badge13 silver badges31 bronze badges

Comments

NaijaProgrammer · Accepted Answer · 2015-03-25 12:46:00Z

0

That's because your current code :

$data["group"] = $row["datetime"];
$data["list"][] = $row;

overwrites the 'group' and 'list' members of the array, with the next iteration.

You'd have to find a way around it. For example, by doing something like:

$data = array();
while ($row = pg_fetch_array($result, null, PGSQL_ASSOC)) {
   $data[]["group"] = $row["datetime"];
   $data[]["list"][] = $row; 
}

for($i = 0; $i < count($data); $i++)
{
   echo echo json_encode($data[$i]);
}

edited Mar 25, 2015 at 12:46

answered Mar 25, 2015 at 11:26

NaijaProgrammer

2,9202 gold badges27 silver badges33 bronze badges

2 Comments

Erik Figueiredo Over a year ago

Hi mike O., thanks for your answer! It did not work. It adds "group": "Hoje, 24 de Março" between { }, when actually it should not be, as the example. Do you have any other suggestion? Thanks!

NaijaProgrammer Over a year ago

I've updated my answer to take into account the new array structure. Try that and let me know what you get.

Collectives™ on Stack Overflow

How to create JSON from Query in PHP

4 Answers 4

4 Comments

2 Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

4 Comments

2 Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related