Node JS Parse Error in MySQL Syntax

Question

In the following code, I am trying to query the database based on three parameters (search type, search term, operator that connects different search types in one query). As you can see, I gave it two search types along with their terms so that the query will be like search where the author is WayneRooney AND the tag is UCL. Later, it might have more than two search types such as search based on author x, author y, mention z, and tag a.

    var search = ['Author','Tag'];
    var term = ['WayneRooney','UCL'];
    var operator = 'AND';

    var queryString = "select Tweet.ID, Tweet.Label, Tweet.TDate, Tweet.RetweetID, User.Name, User.ScreenName from Tweet, User where";

    for(var i in search){
        if(search.indexOf(search[i]) != 0){ 
            queryString += operator;
        }

        if(search[i] == 'Author')
        {
            queryString += "Tweet.UserID IN (select ID from User where ScreenName = '"+ term[i] +"') AND User.ID IN (select ID from User where ScreenName = '"+ term[i] +"')";
        }
        else if(search[i] == 'Mention')
        {
            queryString += "select Tweet.ID, Tweet.Label, Tweet.TDate, User.ScreenName, User.Name, Tweet.RetweetID from Tweet, User where Tweet.ID IN (select TweetID from TweetMention where UserID IN (select ID from User where ScreenName = '"+ term[i] +"')) AND User.ID = Tweet.UserID";
        }
        else if(search[i] == 'Tag')
        {
            queryString += "select Tweet.ID, Tweet.Label, Tweet.TDate, User.Name, User.ScreenName, Tweet.RetweetID from Tweet, User where Tweet.ID IN (select TweetID from TweetHashs where HashID IN (select ID from Hashtag where Label = '"+ term[i] +"')) AND User.ID = Tweet.UserID";
        }
    };

    var query = connection.query(queryString, function(err, rows) {
        if (err) {
            throw (err);
        }
        console.log(rows);
        //res.write(JSON.stringify(rows));
        var tweet = JSON.parse(JSON.stringify(rows));
        var queries_made = 0;
        var queries_success = 0;

The error that I got is:

Error: ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.UserID IN (select ID from User where ScreenName = 'WayneRooney') AND User.ID IN'at line 1

I tried to figure out what the problem is but I couldn't.

mscdex · Accepted Answer · 2016-03-30 12:59:44Z

3

You're missing prepended spaces when appending to your queryString, otherwise you get run-on keywords like: whereTweet.UserID IN ... or ANDTweet.UserID IN ....

answered Mar 30, 2016 at 12:59

mscdex

107k15 gold badges201 silver badges159 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

dlock Over a year ago

Thats a pit we all got into before!

Collectives™ on Stack Overflow

Node JS Parse Error in MySQL Syntax

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related