Databases
StackOverflow2013
Postsdbo
POMulti SQL-Group by to JSON compatible form
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POMulti SQL-Group by to JSON compatible form
    primarykey
    data
    text
    <p>I have a generic SQL query builder which gives me Uniform tables with the following columns:</p> <pre><code>Name, Name2, Amount </code></pre> <p>The query ends with <code>GROUP BY Name2, Name</code>.</p> <p>Now i want to make it suitable to transform it into a JSON with the following gestalt:</p> <pre><code>{dim2: Name2, data: [{label: Name, y: Amount},{label: Name, y: Amount},{label: Name, y: Amount},...]}, {dim2: Name2, data: [{label: Name, y: Amount},{label: Name, y: Amount},{label: Name, y: Amount},...]}, {dim2: Name2, data: [{label: Name, y: Amount},{label: Name, y: Amount},{label: Name, y: Amount},...]}, {dim2: Name2, data: [{label: Name, y: Amount},{label: Name, y: Amount},{label: Name, y: Amount},...]}, ... </code></pre> <p>So far, I made this:</p> <pre><code>while($result = mysqli_fetch_array($results)) { if(!isset($return[$result['Name2']])) $return[$result['Name2']] = array(); $return[$result['Name2']][] = array( 'Label' =&gt; $result['Name'], 'y' =&gt; $result['Anzahl'] ); } $filestring = json_encode($return, JSON_NUMERIC_CHECK); </code></pre> <p>Which doesnt work out. Working on this the whole day really got annoying and I really would appreciate some help now, thanks in advance!!!</p> <p><strong>Update:</strong></p> <p>Here is an example how it computes so far:</p> <pre><code>[{"dim2":"Consumer Entertainment","data":{"Label":"Entertainment","y":1}}, {"dim2":"Digital Life","data":{"Label":"Electronic Devices","y":1}}, {"dim2":"Digital Life","data":{"Label":"Entertainment","y":1}}, {"dim2":"Digital Life","data":{"Label":"Financial Services","y":2}}, {"dim2":"Digital Life","data":{"Label":"IT","y":2}}, {"dim2":"Digital Life","data":{"Label":"Media &amp; Publishing","y":1}}, {"dim2":"Digital Life","data":{"Label":"Transportation &amp; Mobility","y":1}}, {"dim2":"Healthy Life","data":{"Label":"Fashion &amp; Accessories","y":1}}, ...] </code></pre> <p>I want:</p> <pre><code>[{"dim2":"Consumer Entertainment","data":[{"Label":"Entertainment","y":1}]}, {"dim2":"Digital Life","data":[{"Label":"Electronic Devices","y":1}, {"Label":"Entertainment","y":1}, {"Label":"Financial Services","y":2}, {"Label":"IT","y":2}, {"Label":"Media &amp; Publishing","y":1}, {"Label":"Transportation &amp; Mobility","y":1}]}, {"dim2":"Healthy Life","data":[{"Label":"Fashion &amp; Accessories","y":1}]}, ...] </code></pre> <p>and so on</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USuser3009259
    1. USuser3009259
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. COcan't get the point. Provide a real sample.
      singulars
      1. POMulti SQL-Group by to JSON compatible form
      1. USPheu Verg
    2. COThe structure above is needed for a chart with the CanvasJS library. Dont know how to make it more clear. What are you missing?
      singulars
      1. POMulti SQL-Group by to JSON compatible form
      1. USuser3009259
    3. COi've done an array '[{\"dim2\":1,\"data\":[{\"l1\":\"v1\",\"l2\":\"v2\"},{\"l1\":\"v3\",\"l2\":\"v4\"},{\"l1\":\"v5\",\"l2\":\"v6\"}]},{\"dim2\":2,\"data\":[{\"l1\":\"v1\",\"l2\":\"v2\"},{\"l1\":\"v3\",\"l2\":\"v4\"},{\"l1\":\"v5\",\"l2\":\"v6\"}]}]' using json_encode without second parameter. is this what u mean? Or you whant to make another kind of structure?
      singulars
      1. POMulti SQL-Group by to JSON compatible form
      1. USPheu Verg
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

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