Generate JSON Array from PHP SQL Loop

Question

I need to generate the following JSON from a PHP loop and SQL DB but I'm having trouble:

    [
      {
        "ItemName": "Websites1",
        "Websites1": [
          {
            "0": "1",
            "ID": "1",
            "1": "Essential",
            "WebsiteName": "Essential 1",
            "2": "EI",
            "WebsiteCode": "EI"
          },
          {
            "0": "2",
            "ID": "2",
            "1": "Icon Ibiza",
            "WebsiteName": "Icon 1",
            "2": "IC",
            "WebsiteCode": "IC"
          },
          {
            "0": "3",
            "ID": "3",
            "1": "So Ibiza",
            "WebsiteName": "So 1",
            "2": "SO",
            "WebsiteCode": "SO"
          }
        ]
      },
      {
        "ItemName": "Websites2",
        "Websites2": [
          {
            "0": "1",
            "ID": "1",
            "1": "Essential Ibiza",
            "WebsiteName": "Essential 2",
            "2": "EI",
            "WebsiteCode": "EI"
          },
          {
            "0": "2",
            "ID": "2",
            "1": "Icon Ibiza",
            "WebsiteName": "Icon 2",
            "2": "IC",
            "WebsiteCode": "IC"
          },
          {
            "0": "3",
            "ID": "3",
            "1": "So Ibiza",
            "WebsiteName": "So 2",
            "2": "SO",
            "WebsiteCode": "SO"
          }
        ]
      }
    ]

I have the relevant data being returned from the DB into PHP fine but I can't work out how to generate the relevant key value pairs and nesting using PHP loops. The code I have so far is:

            $this->db->sql =    "SELECT ID AS ID, WebsiteName AS SelectText, WebsiteCode AS SelectValue FROM Banners_Websites ORDER BY WebsiteName";

            $rs = $this->db->query($this->db->sql);

            if ($this->db->row_count != 0) {

                $response = array();
                $json = array();

                while ($row = $this->db->row_read($rs)) {

                    $json["ID"] = $row["ID"];
                    $json["SelectText"] = $row["SelectText"];
                    $json["SelectValue"] = $row["SelectValue"];

                    //$json[] = $row;                               
                }

                $response["Websites1"] = $json;


            }


            $rs = $this->db->query($this->db->sql);

            if ($this->db->row_count != 0) {

                $json2 = array();

                while ($row = $this->db->row_read($rs)) {

                    $json2["ID"] = $row["ID"];
                    $json2["SelectText"] = $row["SelectText"];
                    $json2["SelectValue"] = $row["SelectValue"];

                    //$json[] = $row;                               
                }

                $response["Websites2"] = $json2;

            }



            return '[' . json_encode($response) . ']';

I'm obviously doing something very wrong as I only end up with one row for each query. The key value pairs are being overwritten with each loop. Also the nesting isn't correct. Sorry for such a dumb question but I've been trying to figure this out from info online and am stuck.

Thanks,

Noon.

EDIT - Managed to fix this using the annswer provided by Anushil Nandan below but the PHP needed a few extra tweaks to get the required format as folows:

            // -----------------------------------------------------------------------------------------------
            // BANNERMANGEMENTFORM
            function bannerManagementJson($JsonItem) {

                $this->db->connect();

                $response = array();

                //Generate Websites drop-down
                $SqlStr = "SELECT WebsiteName AS SelectText, WebsiteCode AS SelectValue FROM Banners_Websites ORDER BY WebsiteName";
                $response[] = $this->retrieveFormElementJson($SqlStr,'Websites',1);

                //Generate Clients drop-down
                $SqlStr = "SELECT ClientName AS SelectText, ID AS SelectValue FROM Banners_BannerClients ORDER BY ClientName";
                $response[] = $this->retrieveFormElementJson($SqlStr,'Clients',2);

                return json_encode($response);

            }   

            // -----------------------------------------------------------------------------------------------
            // RETRIEVEFORMELEMENTJSON
            function retrieveFormElementJson($SqlStr,$ElementName,$SelectListNo) {

                $this->db->sql = $SqlStr;

                $rs = $this->db->query($this->db->sql);

                if ($this->db->row_count != 0) {

                    $json = array();

                    while ($row = $this->db->row_read($rs)) {
                        $json[] = $row;
                    }

                    $arrayResponse = array("ItemName"=>$ElementName, "SelectList" . $SelectListNo=>$json);

                }

                return $arrayResponse;

            }

this commented line is you need $json[] = $row;

JYoThI
– JYoThI

2017-04-22 11:30:06 +00:00
Commented Apr 22, 2017 at 11:30 — JYoThI
– JYoThI, Commented Apr 22, 2017 at 11:30

Anushil Nandan · Accepted Answer · 2017-04-22 11:42:48Z

1

your array is being overridden every time it's entering while loop

try: $json1[] = array(); $json2[] = array();

        $this->db->sql =    "SELECT ID AS ID, WebsiteName AS SelectText, WebsiteCode AS SelectValue FROM Banners_Websites ORDER BY WebsiteName";

        $rs = $this->db->query($this->db->sql);

        if ($this->db->row_count != 0) {

            $response = array();
            $json[] = array();

            while ($row = $this->db->row_read($rs)) {

                $json[]["ID"] = $row["ID"];
                $json[]["SelectText"] = $row["SelectText"];
                $json[]["SelectValue"] = $row["SelectValue"];

                //$json[] = $row;                               
            }

            $response["Websites1"] = $json;


        }


        $rs = $this->db->query($this->db->sql);

        if ($this->db->row_count != 0) {

            $json2[] = array();

            while ($row = $this->db->row_read($rs)) {

                $json2[]["ID"] = $row["ID"];
                $json2[]["SelectText"] = $row["SelectText"];
                $json2[]["SelectValue"] = $row["SelectValue"];

                //$json[] = $row;                               
            }

            $response["Websites2"] = $json2;

        }



        return '[' . json_encode($response) . ']';

edited Apr 22, 2017 at 11:42

answered Apr 22, 2017 at 11:18

Anushil Nandan

2842 silver badges6 bronze badges

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

3 Comments

JYoThI Over a year ago

still your code looks overwriting each time $json["ID"] = $row["ID"];

N.Widds Over a year ago

Needed to make a few extra tweaks but this got me there so thanks! I've added the extra tweaks to my original question

Barmar Over a year ago

Don't use string concatenation to add the [] characters at the end. Use return json_encode([$response]);

Collectives™ on Stack Overflow

Generate JSON Array from PHP SQL Loop

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