CODEWITHSUNDEEP
javascriptmysqldatabase
Chris Walker
Chris Walker

Reputation: 75

Condensing series of mySQL queries

I have a series of queries to find a question given a username. The queries find the userID associated with a username in the user table, then the questionID attached to the userID in the user_question table, then the question attached to the questionID in the question table.

Because my database structure follows the 2nd normal form and stores different entities in different tables (users, questions etc), it leads to needing multiple queries across multiple tables.

Is it possible to condense the series of queries into something which 1) requires less code and 2) requires less queries?

username = escape(req.query.username);

client.query("SELECT userID FROM user WHERE username = \"" + username + "\"", function(err, result) {
    if (err !== null) {
        console.log(err);
    }
    else {
        client.query("SELECT questionID FROM user_question WHERE userID = " + result[0]["userID"], function(err, result) {
            if (err !== null) {
                console.log(err);
            }
            else {
                client.query("SELECT question FROM question WHERE questionID = " + result[0]["questionID"], function(err, result) {
                    if (err !== null) {
                        console.log(err);
                    }
                    else {
                        //handle question
                    }
               });
        });
 });

EDIT: K. Bastian below gave a correct answer. The code in use now is:

var query = 
    `SELECT 
        q.question 
    FROM 
        question q 
        JOIN user_question uq ON q.questionID = uq.questionID 
        JOIN user u ON u.userID = uq.userID 
    WHERE  
        u.username = \"` + username + `\"`;

client.query(query, function(err, result) {
    //Handle
});

EDIT: After K. Bastian also pointed out the node-mysql library's ability to escape user-input strings (instead of JS performing the function), I also implemented that, further simplifying the code.

var query = 
    `SELECT 
        q.answer  
    FROM 
        question q 
        JOIN user_question uq ON q.questionID = uq.questionID 
        JOIN user u ON u.userID = uq.userID 
    WHERE  
        u.username = ` + client.escape(req.query.username);

client.query(query, function(err, result) {
    //Handle result...
});

Upvotes: 1

Views: 43

Answers (1)

K. Bastian
K. Bastian

Reputation: 121

You can put your queries into one ...

something like this? 

select 
    q.question 
from 
    question q
    JOIN user_question uq ON q.questionID = uq.questionID
    JOIN user u ON u.userID = uq.userID
WHERE 
    u.username = \"" + username + "\"

Btw... this looks like a potential sql injection part... \"" + username + "\"

Upvotes: 1

Related Questions