current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
Join the Stack Overflow Community
Stack Overflow is a community of 6.5 million programmers, just like you, helping each other.
Join them; it only takes a minute:
Sign up
up vote 8 down vote favorite
2

I have two tables, table 1 has 2 fields (question_pk, question_name) and table 2 has 4 fields(ans_pk, options, question_fk and right_answer). I want to create json like the following structure

{
    "type": "quiz",
    "name": "Brand Colors",
    "description": "Can you identify these brands by the background color?",
    "questions": [
        {
            "name": "Can you identify this color?",
            "description": "#ea4c89",
            "answers": [
                {
                    "name": "Dribbble",
                    "description": "dribbble.png",
                    "weight": 1
                },
                {
                    "name": "Amazon",
                    "description": "amazon.png",
                    "weight": 0
                },
                {
                    "name": "Apple",
                    "description": "apple.png",
                    "weight": 0
                }
            ]
        },
        {
            "name": "Can you identify this color?",
            "description": "#3d9ae8",
            "answers": [
                {
                    "name": "Youtube",
                    "description": "youtube.png",
                    "weight": 0
                },
                {
                    "name": "Dropbox",
                    "description": "dropbox.png",
                    "weight": 1
                },
                {
                    "name": "Wordpress",
                    "description": "wordpress.png",
                    "weight": 0
                }
            ]
        },
        {
            "name": "Can you identify this color?",
            "description": "#c4302b",
            "answers": [
                {
                    "name": "Youtube",
                    "description": "youtube.png",
                    "weight": 1
                },
                {
                    "name": "Twitter",
                    "description": "twitter.png",
                    "weight": 0
                },
                {
                    "name": "Vimeo",
                    "description": "vimeo.png",
                    "weight": 0
                }
            ]
        }

    ]
}

MY PHP CODE

<?php
include '../config/config.php';
if(isset($_GET['sub_cat_id']))
{
         $sub_cat_id = $_GET['sub_cat_id']; 
        $result = mysql_query("select * from $questions where sub_cat='$sub_cat_id' order by level_fk asc"); 
        $json_response = array(); //Create an array
        $i=1;
                        while ($row = mysql_fetch_array($result))
                        {
                        $row_array['qus_pk'] = $row['qus_pk'];        
                        $row_array['question'] = $row['question'];
                        $qus_pk = $row['qus_pk'];  


                        $option_qry = mysql_query("select * from $qus_ans where qus_pk=$qus_pk");
                        while ($opt_fet = mysql_fetch_array($option_qry))
                        {

                        $row_array['options'] = $opt_fet['options'];  
                        $row_array['right_ans'] = $opt_fet['right_ans'];  
                        array_push($json_response,$row_array); //push the values in the array
                            }                       


                        $i++;
                        }
        echo json_encode($json_response);
}

?>

And My Result I am getting json response like the following

[
    {
        "qus_pk": "1",
        "question": "Ten years ago, P was half of Q in age. If the ratio of their present ages is 3:4, what will be the total of their present ages?",
        "options": "45",
        "right_ans": "0"
    },
    {
        "qus_pk": "1",
        "question": "Ten years ago, P was half of Q in age. If the ratio of their present ages is 3:4, what will be the total of their present ages?",
        "options": "40",
        "right_ans": "0"
    },
    {
        "qus_pk": "1",
        "question": "Ten years ago, P was half of Q in age. If the ratio of their present ages is 3:4, what will be the total of their present ages?",
        "options": "35",
        "right_ans": "1"
    },
    {
        "qus_pk": "1",
        "question": "Ten years ago, P was half of Q in age. If the ratio of their present ages is 3:4, what will be the total of their present ages?",
        "options": "50",
        "right_ans": "0"
    },
    {
        "qus_pk": "2",
        "question": "Father is aged three times more than his son Sunil. After 8 years, he would be two and a half times of Sunil's age. After further 8 years, how many times would he be of Sunil's age?",
        "options": "4 times",
        "right_ans": "0"
    },
    {
        "qus_pk": "2",
        "question": "Father is aged three times more than his son Sunil. After 8 years, he would be two and a half times of Sunil's age. After further 8 years, how many times would he be of Sunil's age?",
        "options": "1 times",
        "right_ans": "0"
    },
    {
        "qus_pk": "2",
        "question": "Father is aged three times more than his son Sunil. After 8 years, he would be two and a half times of Sunil's age. After further 8 years, how many times would he be of Sunil's age?",
        "options": "3 times",
        "right_ans": "1"
    },
    {
        "qus_pk": "2",
        "question": "Father is aged three times more than his son Sunil. After 8 years, he would be two and a half times of Sunil's age. After further 8 years, how many times would he be of Sunil's age?",
        "options": "5 times",
        "right_ans": "0"
    }
]

In my respose each time the question is repeated so how to avoid and if i want to achive the first json structure, in my PHP code what&where i need to make changes?. If any one knows help me.

share|improve this question
up vote 9 down vote

Hi try this,

<?php
include '../config/config.php';
if(isset($_GET['sub_cat_id']))
{
    $sub_cat_id = $_GET['sub_cat_id']; 
    $result = mysql_query("SELECT * FROM $questions WHERE sub_cat='$sub_cat_id' ORDER BY level_fk ASC"); 
    $json_response = array(); //Create an array
    while ($row = mysql_fetch_array($result))
    {
        $row_array = array();
        $row_array['qus_pk'] = $row['qus_pk'];        
        $row_array['question'] = $row['question'];
        $row_array['answers'] = array();
        $qus_pk = $row['qus_pk'];  

        $option_qry = mysql_query("SELECT * FROM $qus_ans WHERE qus_pk=$qus_pk");
        while ($opt_fet = mysql_fetch_array($option_qry))
        {
            $row_array['answers'][] = array(
                'options' => $opt_fet['options'],
                'right_ans' => $opt_fet['right_ans'],
            );

        }
        array_push($json_response, $row_array); //push the values in the array
    }
    echo json_encode($json_response);
}
?>
share|improve this answer
    
Ya, its working fine.. Thank you... – Vetrivel May 10 '15 at 19:25
4  
accept it if it works – Ohmen Feb 27 '16 at 13:40
up vote 4 down vote

I think this code is easier to figure out and by the way it uses mysqli ...

This is based on my own data structure, I am in the middle of something and I have no time a.t.m. to adapt it to the question but should easy to figure out how to adapt it to other structures :

$usersList_array =array();
$user_array = array();
$note_array = array();

$fetch_users = mysqli_query($mysqli, "SELECT ID, Surname, Name FROM tb_Users WHERE Name LIKE 'G%' ORDER BY ID") or die(mysqli_error($mysqli));
while ($row_users = mysqli_fetch_assoc($fetch_users)) {
    $user_array['id'] = $row_users['ID'];
    $user_array['surnameName'] = $row_users['Surname'].' '.$row_users['Name'];
    $user_array['notes'] = array();

    $fetch_notes = mysqli_query($mysqli, "SELECT id, dateIns, type, content FROM tb_Notes WHERE fk_RefTable = 'tb_Users' AND fk_RefID = ".$row_users['ID']."") or die(mysqli_error($mysqli));
    while ($row_notes = mysqli_fetch_assoc($fetch_notes)) {
        $note_array['id']=$row_notes['id'];
        $note_array['dateIns']=$row_notes['dateIns'];
        $note_array['type']=$row_notes['type'];
        $note_array['content']=$row_notes['content'];
        array_push($user_array['notes'],$note_array);
    }

    array_push($usersList_array,$user_array);
}

$jsonData = json_encode($usersList_array, JSON_PRETTY_PRINT);


echo $jsonData;

Resulting JSON :

[
{
    "id": "1",
    "surnameName": "Xyz Giorgio",
    "notes": [
        {
            "id": "1",
            "dateIns": "2016-05-01 03:10:45",
            "type": "warning",
            "content": "warning test"
        },
        {
            "id": "2",
            "dateIns": "2016-05-18 20:51:32",
            "type": "error",
            "content": "error test"
        },
        {
            "id": "3",
            "dateIns": "2016-05-18 20:53:00",
            "type": "info",
            "content": "info test"
        }
    ]
},
{
    "id": "2",
    "cognomeNome": "Xyz Georg",
    "notes": [
        {
            "id": "4",
            "dateIns": "2016-05-20 14:38:20",
            "type": "warning",
            "content": "georg warning"
        },
        {
            "id": "5",
            "dateIns": "2016-05-20 14:38:20",
            "type": "info",
            "content": "georg info"
        }
    ]
}
]
share|improve this answer
    
Welcome to Stack Overflow. While the link you provided may answer the question, it is best to put the essential parts of your solution directly in your answer in case the page at the link disappears in the future. – Kmeixner May 20 '16 at 14:07
2  
Thank you, I thought avoid duplication were preferred.. I'll put in my code :-) – Giorgio Zanetti May 21 '16 at 10:43

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.