StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POCreating JSON tree structure based on sql query
text
Body
copied!<p>My simplified tables from database are as follows:<br> <strong>ImageContainer</strong> </p> <pre><code>Id Name ======= 1 A 2 B 3 C </code></pre> <p><strong>Images</strong> </p> <pre><code>Id ParentId ImageData ===================== 1 2 sthsth 2 1 sthsth 3 1 sthsth </code></pre> <p>I need to fetch these from database and create tree-like structure (I've got 4 tables of these parent-child relations), then encode it into JSON. I'm using PHP + Zend. So far my solution was to create queries to each table, encode it to JSON and then manually insert child JSON into parent JSON. Is there any better way to do this? </p> <p>So far I tried </p> <pre><code>private function insertAsLastInJson($json, $name, $insert) { if(strlen($json) < 2) return '{' . $insert . '}'; $in = ",\"$name\":$insert"; return substr($json, 0, strlen($json) - 1) . $in; } </code></pre> <p>and then I used it like this:</p> <pre><code>$json = Zend_Json::encode($imageContainerRow); //$imageContainerRow contains single 'ImageContainer' row fetched from database insertAsLastInJson($json, "images", $imagesRows); //$imagesRows contains multiple 'Images' rows fetched from database and encoded to JSON, separated by commas and everything is in beetween [ ]. </code></pre> <p>Example JSON would be: </p> <pre><code>{"Id":1,"Name":"A","Images":[{"Id":2,"ParentId":1,"ImageData":"sthsth"}, {"Id":3,"ParentId":1,"ImageData":"sthsth"}]} </code></pre> <p>This required some parentheses insertions but all in all I got it working. Is there any better way to do this?</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload