mysql to json using php. Nested objects

Question

Good Afternoon, I am trying to get these results into arrays in PHP so that I can encode them into json objects and send them to the client. The results of the query look like this:

   id   name    hours   cat status
3bf JFK Int 24  pass    open
3bf JFK Int 24  std closed
3bf JFK Int 24  exp open
5t6 Ohm CA  18  pass    closed
5t6 Ohm CA  18  std closed
5t6 Ohm CA  18  std2    open
5t6 Ohm CA  18  exp open
...

I would like for the json objects to look like this:

{ "id": "3bf", "name": "JFK Int", "cats":
    { [ { "cat": "pass", "status": "open" },
        { "cat": "std", "status": "closed" },
        { "cat": "exp", "status": "open" } ] }
{ "id": "5t6", "name": "Ohm CA", "cats":
    { [ { "cat": "pass", "status": "closed" },
        { "cat": "std", "status": "closed" },
        { "cat": "std2", "status": "open" } ],
        { "cat": "exp", "status": "open" } ] }

I have succesfully connected to mysql and exported using json_encode using flat tables but this part I do not know how to do in PHP. Thanks.

This is the code that I have. This returns an array of json objects but it is flat, not nested:

$SQL = "SELECT id, name, hours, cat, status FROM bwt.vewPortCats";

$result = mysql_query($SQL);

$arr = array();
    while ($row = mysql_fetch_assoc($result)) {
        $arr[] = $row;}

$json = json_encode($arr);

echo $json;

The data itself is from a view that combines the tables ports and cats.

can you please show us your PHP code? There are different ways to extract data from mysql and solution varies depending on what you have (IE mysqli, pdo...). What you can probably do is to iterate over "external" elements using foreach, and then again on internal ones (same construct). You have as an output a multidimensional array, which you can then transform to JSON with json_encode — maraspin
– maraspin, Commented Nov 6, 2011 at 22:17
use group by on query for id and name, and group_concat to get array object for category... provide SQL to be able give you example — bensiu
– bensiu, Commented Nov 6, 2011 at 22:19
good comment by maraspin. for code examples and more, check php.net/manual/en/function.json-encode.php — supertopi
– supertopi, Commented Nov 6, 2011 at 22:19
I have updated the question with the php code that I have. @maraspin, that is what I was thinking but I don't know how to create a nested array that links parents (ports) and children (cats). — jangeador
– jangeador, Commented Nov 6, 2011 at 22:43
@bensiu I really have no idea of what the things you mentioned are. I would like to know more about that. — jangeador
– jangeador, Commented Nov 6, 2011 at 22:44

maraspin · Accepted Answer · 2011-11-07 01:12:51Z

10

what you could do (sorry, not the best code I could write... short on time, ideas, and energy ;-) is something like this (I hope it still conveys the point):

$SQL = "SELECT id, name, hours, cat, status FROM bwt.vewPortCats";

$result = mysql_query($SQL);

$arr = array();
    while ($row = mysql_fetch_assoc($result)) {

        // You're going to overwrite these at each iteration, but who cares ;-)
        $arr[$row['id']]['id'] = $row['id'];
        $arr[$row['id']]['name'] = $row['name'];

        // You add the new category
        $temp = array('cat' => $row['cat'], 'status' => $row['status']);

        // New cat is ADDED
        $arr[$row['id']]['cats'][] = $temp;
    }


$base_out = array();

// Kind of dirty, but doesn't hurt much with low number of records
foreach ($arr as $key => $record) {
    // IDs were necessary before, to keep track of ports (by id), 
    // but they bother json now, so we do...
    $base_out[] = $record;
}

$json = json_encode($base_out);

echo $json;

Haven't had the time to test or think twice about it, but again, I hope it conveys the idea...

edited Nov 7, 2011 at 1:12

answered Nov 6, 2011 at 22:53

maraspin

2,42321 silver badges16 bronze badges

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

2 Comments

jangeador Over a year ago

that was amazing. I don't even know what you are capable of when you have time, energy, and ideas, but this worked just fine. I will accept as answer, but since I do not have edit privileges, can you please fix 3 spots on the code where $arr[row should be $arr[$row . Also, in order to get the names to spit out on the json object I changed $temp= array($row['cat'], $row['status']); to $temp= array("cat" => $row['cat'], "status" => $row['status']);

maraspin Over a year ago

You're right @jangeador. Sorry for those typos. Glad solution worked out for you.

M. Akbar Zain · Accepted Answer · 2022-08-14 07:36:42Z

With thanks to @maraspin, I have got my below code:

function merchantWithProducts($id)
    {
        if (
            !empty($id)
        ) {

            //select all query
            $query = "SELECT
                     m.id as 'mMerchantID', m.name as 'merchantName', m.mobile, m.address, m.city, m.province,
                     p.id as 'ProductID', p.merchantId as 'pMerchantID', p.category, p.productName, p.description, p.price, p.image, p.ratingCount 
                FROM " . $this->table_name . " m 
                JOIN by_product p 
                ON m.id = p.merchantId 
                WHERE m.id = :id 
                GROUP BY m.id";

            // prepare query statement
            $stmt = $this->conn->prepare($query);

            // sanitize
            //  $this->id = htmlspecialchars(strip_tags($this->id));

            // bind values
            $stmt->bindParam(":id", $this->id);

            try {
                $success = $stmt->execute();

                if ($success === true) {

                    $results = $stmt->fetchAll();
                    
                    $this->resultToEncode = array();

                    foreach ($results as $row) {
                       
                        $objItemArray = array(
                            "merchantID" => $row->mMerchantID,
                            "merchantName" => $row->merchantName,
                            "mobile" => $row->mobile,
                            "address" => $row->address,
                            "city" => $row->city,
                            "province" => $row->province, 
                            "product" => array(
                                "productID" => $row->ProductID,
                                "pMerchantID" => $row->pMerchantID,
                                "category" => $row->category,
                                "productName" => $row->productName,
                                "description" => $row->description,
                                "price" => $row->price,
                                "image" => $this->baseUrl . 'imagesProducts/' . $row->image,
                                "ratingCount" => $row->ratingCount
                            )
                        );

                        
                        array_push($this->resultToEncode, $objItemArray);
                    }

                    http_response_code(200);
                    $httpStatusCode = '200 OK';
                    $pass = true;

                    // return json_encode($resultToEncode);
                } else {
                    http_response_code(204);
                    $httpStatusCode = '204 No Content';
                    $pass = false;
                    $this->resultToEncode = 'No Record Found';
                }
            } catch (PDOException $pdoEx) {
                http_response_code(500); // internal server error.
                $httpStatusCode = '500 Internal Server Error';
                $pass = false;
                $this->resultToEncode = $pdoEx->getCode();
            } catch (Exception $ex) {
                // return $ex->getMessage();
                http_response_code(404); // 404 Not Found.
                $httpStatusCode = '404 Not Found';
                $pass = false;
                $this->resultToEncode = $ex->getMessage();
            }
        } else {
            http_response_code(400);
            $httpStatusCode = '400 bad request';
            $pass = false;
            $this->resultToEncode = 'User id not specified';
        }
        echo json_encode(array('passed' => $pass, 'Response' => $httpStatusCode, 'result' => $this->resultToEncode));
    }

Collectives™ on Stack Overflow

mysql to json using php. Nested objects

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related