1

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);
        }
          }
          ;
        };
      });
    };
  });
});

};

Lisda Wijayanti
  • 63
  • 2
  • 7
  • You should tell us how you serve your content (which web server), the nodejs packages you use, especially for the mysql connection (the `require()`) as well as the log traces of the execution on your server. – VincentTellier Feb 14 '18 at 07:15
  • 1
    [xkcd: Exploits of a Mom](https://www.explainxkcd.com/wiki/index.php/327:_Exploits_of_a_Mom) – Andreas Feb 14 '18 at 07:29

1 Answers1

1

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.

VincentTellier
  • 558
  • 6
  • 16