Database structure store comments and post

Question

I have two table

posts_table

post_id | user_id | status_message | date_time

comments_table

comments_id | user_id | post_id | comments_message | date_time

My code is this i want show every post and show all comments message in this post

router.get('/', (req, res, next) => {


 connection.query('SELECT * FROM photos_status', (err, result) => {

   if(err){
   console.error(err);  
   }else{
    if(result.length >0){ 
        for(var i = 0; i < result . length ;i++){    

            var Temp = [];
         var post_id =result[i]. post_id;
         connection.query('SELECT * FROM comments WHERE post_id = ?', 
    [post_id], function (error, results) {
                    if (error) {
                        res.json({
                          status:false,
                          message:'there are some error with query'
                          })
                        }else{

                            res.status(200).json({

                                result ,
                                results
                            });  

                        }
                    })
        }

         }

   }
 });

});

I want select data from database and show like this

   [ 
      { "post_id":"1",
        "user_id":"2",
         "status_x":"demo ..."

       " comments"[
                   { 
                 "user_id":"1",
                 "post_id":"1",
                  "comments_message":"demo..",
                    },
                 { 
                 "user_id":"2",
                 "post_id":"1",
                 "comments_message":"demo..",
                    }
               ]


       }

  ]

add a complete description to your question and also add what you have tried to do.. — prabhat mishra, Mar 15 '18 at 18:16

score 1 · Answer 1 · answered Mar 15 '18 at 19:23

I think this will give you some Idea:

var allPosts = []
for(var i = 0; i < result.length ;i++){ 
    singlepost={}
    singlepost['post_id'] = result[i].post_id;
    singlepost['user_id'] = result[i].user_id ;
    singlepost['status_x'] = result[i].status_message;
    singlepost['comments'] = [];
    connection.query('SELECT * FROM comments WHERE post_id = ?', 
    [post_id], function (error, results) {
                    if (error) {
                        res.json({
                          status:false,
                          message:'there are some error with query'
                          })
                        }else{ res.status(200).json({
                                //loop over comments create an comment object
                                comment={}
                                //same as we did above for singlepost add values and push to array for each element
                                singlepost['comments'].push(comment)
                                result ,
                                results
                            });
                        }
                }
}

for Reference : How to push JSON object in to array using javascript Dynamically Add Variable Name Value Pairs to JSON Object

in this case comment not push output is "allPosts": [ { "post_id": 1, "user_id": 1, "post_message": "hi", "comments": [ ] }, but i console.log(results); then comment data is print — Sudhir Singh, Mar 16 '18 at 10:48

score 0 · Answer 2 · answered Mar 16 '18 at 10:57

 if(result.length >0){ 



      var allPosts = []
       for(var i = 0; i < result.length ;i++){    
        var result1;
        singlepost={}
        singlepost['post_id'] = result[i].post_id;
        singlepost['user_id'] = result[i].user_id ;
        singlepost['post_message'] = result[i].post_messagecol;
        singlepost['comments']=[];
        var post_id =result[i]. post_id;
        connection.query('SELECT * FROM comment WHERE post_id = ?', [post_id], function (error, results) {
                   if (error) {
                       res.json({
                         status:false,
                         message:'there are some error with query'
                         })
                       }else{

                         singlepost['comments'].push(results)

                           console.log(results);
                    }

                   });

                   allPosts.push(singlepost);

  }

       res.json({
        allPosts
        });

        }

  }
});

but output is

        {
 "allPosts": [
  {
  "post_id": 1,
  "user_id": 1,
  "post_message": "hi",
  "comments": [

  ]
}
 ]
 }


console.log(results);

then comments results is print

Database structure store comments and post

2 Answers2