current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

I'm not a php developer, but trying to get an array within an array from this query:

SELECT distinct d.DiscussionId as DiscussionId, d.AvatarId as AvatarId, d.Heading as Heading, d.body as Body, c.commentid as CommentId, c.userid as UserId, c.comment as Comment FROM Discussion AS d INNER JOIN Comments AS c ON d.discussionid = c.discussionid WHERE d.DiscussionId = c.DiscussionId

The JSON output is:

[
    {
        "DiscussionId": "1", 
        "AvatarId": null, 
        "Heading": "New discussion heading", 
        "Body": "This is the discussion body", 
        "Comments": [
            {
                "DiscussionId": "1", 
                "CommentId": "1", 
                "UserId": "2", 
                "Comment": "This is a comment i made"
            }
        ]
    }, 
    {
        "DiscussionId": "1", 
        "AvatarId": null, 
        "Heading": "New discussion heading", 
        "Body": "This is the discussion body", 
        "Comments": [
            {
                "DiscussionId": "1", 
                "CommentId": "2", 
                "UserId": "2", 
                "Comment": "This is a second comment"
            }
        ]
    }
]

What I need is to nest all Comments in one Discussion.

the php code is below, no error but not giving the output i want, for each discussion there maybe several comments so i need DiscussionId:1 displayed only once with multilple comments array

$result = mysql_query($query,$link) or die('Errant query:  '.$query);



 $model = array();
 $record = -1;
 $currentWeID = -1;    
 while($e = mysql_fetch_assoc($result)){ 
 $record++;
 $model[] = array();
 $model[$record]['DiscussionId'] = $e['DiscussionId']; 
 $model[$record]['AvatarId'] = $e['AvatarId']; 
 $model[$record]['Heading'] = $e['Heading'];
 $model[$record]['Body'] = $e['Body']; 
 $model[$record]['Comments'][] = array( 

 'DiscussionId'=> $e['DiscussionId'], 
 'CommentId' => $e['CommentId'], 
 'UserId' => $e['UserId'], 
 'Comment' => $e['Comment'] 
 ); 

}


print json_encode ($model);
share|improve this question
1  
Post code from what you have tried and what errors it is causing. –  JClaspill Apr 18 at 21:35

4 Answers 4

up vote 0 down vote

Try this:

$result = mysql_query($query,$link) or die('Errant query: '.$query);

 $model = array();
 $record = 0;
 $currentWeID = -1;    
 while($e = mysql_fetch_assoc($result)){ 
 $model[] = array();
 $model[$record][$e['DiscussionId']]['AvatarId'] = $e['AvatarId']; 
 $model[$record][$e['DiscussionId']]['Heading'] = $e['Heading'];
 $model[$record][$e['DiscussionId']]['Body'] = $e['Body']; 
 $model[$record][$e['DiscussionId']]['Comments'][] = array( 
    'DiscussionId'=> $e['DiscussionId'], 
    'CommentId' => $e['CommentId'], 
    'UserId' => $e['UserId'], 
    'Comment' => $e['Comment'] 
    ); ;
 $record++;
}


print json_encode ($model);

This might help you. Dont forget to accept answer if it helps.

share|improve this answer
up vote 0 down vote

try it like this:

Added Disscussion Id as an index for the array, should work for you. Just off the top of my head, not tested.

$result = mysql_query($query,$link) or die('Errant query:  '.$query);
$model = array();
 $record = -1;
 $currentWeID = -1;
 $model = array(); 

 while($e = mysql_fetch_assoc($result)){ 
 $record++;
 $model[$e['DiscussionId']][$record]['AvatarId'] = $e['AvatarId']; 
 $model[$e['DiscussionId']][$record]['Heading'] = $e['Heading'];
 $model[$e['DiscussionId']][$record]['Body'] = $e['Body']; 
 $model[$e['DiscussionId']][$record]['Comments'][] = array( 

 'DiscussionId'=> $e['DiscussionId'], 
 'CommentId' => $e['CommentId'], 
 'UserId' => $e['UserId'], 
 'Comment' => $e['Comment'] 
 ); 

}


print json_encode ($model);
share|improve this answer
    
Thanks for the quick reply, it's looking closer but only returns the second comment for the discussion –  user3550256 Apr 18 at 23:45
    
@user3550256 try now, I had left $model = array(); inside the while loop by mistake –  CodeBird Apr 19 at 8:30
up vote 0 down vote

You are selecting many comments from a single dicussion. You should overwrite that variable instead of creating a new element at each iteration:

 $model = array();  
 while($e = mysql_fetch_assoc($result)){ 
 //We overwrite the same variable, that's ok
 $model['DiscussionId'] = $e['DiscussionId']; 
 $model['AvatarId'] = $e['AvatarId']; 
 $model['Heading'] = $e['Heading'];
 $model['Body'] = $e['Body']; 
 //Only comments would be an array
 $model['Comments'][] = array( 
        'DiscussionId'=> $e['DiscussionId'], 
        'CommentId' => $e['CommentId'], 
        'UserId' => $e['UserId'], 
        'Comment' => $e['Comment'] 
        ); 
}
share|improve this answer
up vote -1 down vote

It's not possible with just changing the SQL query: you need to change the server-side code (PHP, as I understand) which transorms query results to JSON.

share|improve this answer
1  
this is a comment not an answer. –  CodeBird Apr 18 at 21: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.