Encoding Json from MySQL

Question

So I am trying to encode to JSON from MySQL and I need it in a [pagenumber][id,type,description][answerid,answerdescription] format. The goal of this is to read the data in a javascript file that will generate a multi step poll for me.

I will try to draw a pseudocode on how I want it to look right here:

{"pages":
  [{1:
    [{"id":1,"text":"U mad?","options":
      [{"opt_id":1,"option":"yes","answer:''"},
       {"opt_id":2,"option":"no","answer:''"},
       {"opt_id":3,"option":"perhaps","answer:''"}]},
       {"id":2,"text":"Got it?","options":
    [{"opt_id":1,"option":"yes","answer:''"},
       {"opt_id":2,"option":"no","answer:''"}]
    }]
   },
   {2:
    [{"id":3,"text":"Help me?","options":
     [{"opt_id":1,"option":"yes","answer:''"},
       {"opt_id":2,"option":"no","answer:''"},
       {"opt_id":3,"option":"perhaps","answer:''"}]},
     {"id":4,"text":"Please?","options":
      [{"opt_id":1,"option":"yes","answer:''"},
       {"opt_id":2,"option":"no","answer:''"}]
    }]
  }]
}

This is what I got so far, but I can't seem to think of a way to add the 3rd "dimension" to this, I want an array of [id => (int), description => (string)] attached to each question. And each question needs room for several answers connected to them. The last column in the answers/options array is for text strings (most answers are answered by ID numbers, but some are textareas that needs whole strings). this might not be needed as I can probably send the form results back by serializing.

$rows = array();

while($r = mysql_fetch_assoc($sth)) 
{
    $Qid = $r['id']; 
    $page=$r['page']; 
    $type=$r['type']; 
    $Qdesc=$r['description'];

    $rows[$page][] = array(
                    'id' => $Qid,
                    'type' => $type,
                    'description' => $Qdesc);
}

The result of this is the following (first 3 pages).

{
"1":[
  {"id":"2","type":"1","description":"U mad?"},
  {"id":"3","type":"1","description":"Got it?"},
  {"id":"4","type":"1","description":"Help me?"}],
"2":[
  {"id":"5","type":"1","description":"Please?"},
  {"id":"6","type":"1","description":"Any clues?"}],
"3":[
  {"id":"7","type":"2","description":"Foobar?"}]}

Do you know about json_encode ? ==> http://php.net/manual/fr/function.json-encode.php — gilles emmanuel, Jul 16 '12 at 12:17
looks like you need an 'options' field with key:value pairs to build you answer options from. You could have a value of "1:yes,2:no,3:maybe" then split by , and extract KEY:VALUE to build the options for each question — Waygood, Jul 16 '12 at 12:26
@gillesemmanuel I do, but I still need the arrays to be precise before I do. The encoding was the next step. — Tom, Jul 16 '12 at 13:26

score 1 · Answer 1 · answered Jul 16 '12 at 12:32

This is not an entire answer, but to help you start off, you could have:

json_encode(array("pages" => array(1 => $row1, 2=>$row2)));

Generally you can achieve multilevel nesting using array(..) within another array(..). Like:

json_encode(
    array(
        $item1,
        $item2,
        array(
            "key1" => array(
                    1,
                    2,
                    array(
                        "inKey1" => array(4,5,6)
                    )
            )
        )
    )
);

Waygood · Accepted Answer · 2012-07-16T12:50:36.957

How about an options table?

id, option, answer
1, yes, ''
2, no, ''
3, perhaps, ''

(your answer data is always empty so I've included it for consistency)

Then for each question you would have an options field with "1,2,3" for all the options "1,2" for just yes/no etc..

To implement this you could:-

$options=array();
if(!empty($r['options']))
{
    $sql="SELECT * FROM options_table WHERE id IN (".$r['options'].")";
    $result=mysql_query($sql);
    while($row=mysql_fetch_assoc($result){
       $options[]=$row;
    }
}

$rows[$page][] = array(
                'id' => $Qid,
                'type' => $type,
                'description' => $Qdesc,
                'options'=>$options);

This way you could add options to your hearts content

Never thought of adding the whole row in, will definately give this a go! — Tom, Jul 16 '12 at 12:58

Encoding Json from MySQL

2 Answers2

Linked