CODEWITHSUNDEEP
javascriptmysqlnode.js
Lisda Wijayanti
Lisda Wijayanti

Reputation: 63

Create a nested array in nodejs

I have a table name master_surveys, master_questions and master_answers. from those table I want to create a nested array result for json query, and I got some problem because the result is couldnt give any response

i tried at postman

I have search what is the problem , but it seems like the problem is at this code and then i tried to change the code but the error become so many, so i decide to keep the first code

Here is my code

this.getSurvey = function(req,res,next) {
var id = req.query.id;
 connection.acquire(function(err,con){
  var survey = 'SELECT ms.id ,ms.title, ms.created_at, count(mq.id) question FROM master_surveys ms, master_questions mq WHERE ms.id=mq.survey_id';
  con.query(survey, function(err,data){
    if (data.length > 0) {
      var survey = data[0];
      var question = 'SELECT mq.id, mq.title, mq.type, mq.survey_id FROM master_surveys ms, master_questions mq WHERE ms.id=mq.survey_id and ms.id="'+survey.id+'"';
      con.query(question, function(err, data){
        if (data.length > 0) {
          var answer = 'SELECT ma.id, ma.title, ma.selected_answer, ma.question_id FROM master_questions mq, master_answers ma WHERE mq.id=ma.question_id and mq.id="'+id+'"';
          con.query(answer, function(err, data){
            if(data.length>0){
              question.data = data;
            }
            if (data.question== null) {
              survey.data = [];
            }
            survey.data.push(question);
          }), function (err) {
            if (survey.questions.length == questions.length) {
          res.json(survey);
        }
          }
          ;
        };
      });
    };
  });
});

};

Upvotes: 1

Views: 672

Answers (1)

VincentTellier
VincentTellier

Reputation: 588

You don't answer the http request when your queries do not return any result or even issues an error. I would recommend to start with one simple query first like that:

con.query('SELECT * FROM your_table', function(err, data) {
    if(err)
        // TODO reply the HTTP request with an appropriate error code
    else {
        if(data.length > 0) {
            // TODO parse the result and form you json object
            res.json(/* your json object */);
        }
        else {
            res.json({}); // for example
        }
    }
});

And only once you get it work improve it until you get the result you aim for.

You should not execute several sql queries in series like that, or at least the less as possible. The JOIN clause is certainly the way to go.

Finally you should also be aware your code is open for SQL injections, see this post.

Upvotes: 1

Related Questions