1

Is it possible to complete a mySQL query when second or any next parameter but first is null ?

For example I generate a report based on a given date (datereport) for a single item (itemreport).

Now when no item is selected to the frontend and its req.query.itemreport gets null, the rest of the query has to be executed considering that user asked for all items and not for a single one without stopping the execution because it gets null parameter (itemreport).

reportsdateres.post(function(req, res, next) {

    datereport = req.query.datereport;
    itemreport = req.query.itemreport; // this can be null for querying all items

    req.getConnection(function(err, conn) {

        if (err) return next("Cannot Connect");

        var query = conn.query("SELECT SUM(total_profit) AS fee_total FROM transactions WHERE date_created = ? AND item = ?", [datereport, itemreport], function(err, rows) {
        //                                                                                                     ^^^^^^^^^^^^ 

            // do myStuff

        });
    });
});
Improve this question
1
  • 1
    You can add a simple if check if it is null and just opitmize the query as per the req value. Commented Jun 21, 2017 at 12:26

3 Answers 3

Reset to default
2 
reportsdateres.post(function(req, res, next) {

    datereport = req.query.datereport;
    itemreport = req.query.itemreport; // this can be null for querying all items

    req.getConnection(function(err, conn) {

        if (err) return next("Cannot Connect");

        var query = conn.query("SELECT SUM(total_profit) AS fee_total FROM transactions WHERE date_created = ? AND item = COALESCE(?, item)", [datereport, itemreport], function(err, rows) {

            // do myStuff
        });
    });
});

COALESCE (we can get rid of if else in code) function will take first parameter into consideration if it is not null otherwise it will compare the value of item column with itself, for more detail visit : https://www.w3schools.com/sql/sql_isnull.asp

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

1 Comment

I think this should be the correct answer.
1 
reportsdateres.post(function(req, res, next) {

    datereport = req.query.datereport;
    itemreport = req.query.itemreport; // this can be null for querying all items

    req.getConnection(function(err, conn) {

        if (err) return next("Cannot Connect");

       var queryString = "SELECT SUM(total_profit) AS fee_total FROM transactions WHERE date_created = ? ";
       var queryParams = [];
       queryParams.push(datereport);

       if(itemreport){
          queryString += ' AND item = ?'
          queryParams.push(itemreport);
        }

        var query = conn.query(queryString, queryParams, function(err, rows) {
          // do yourStuff

        });
    });
});

Update

Added the Fix, appened the query string. Even I missed it :)

Improve this answer

5 Comments

It gets error when item parameter is defined and not null: Error: ER_PARSE_ERROR: You have an error in your SQL syntax; check [...] right syntax to use near 'AND item = '2017-06-21'"' at line 1 . Your second queryString in if(itemreport) overwrites the the initial queryString and as a result it makes the whole query wrong. I didn't notice before upvoting.
Great. However it was still needed some single quotes to be added on the initial query string like '"SELECT SUM(total_profit) AS fee_total FROM transactions WHERE date_created = ? ' so it does not break in conn.query. Now the next error with it is that when query uses queryParams it parses only the first char from the object so it does read only the first character of the first string (datereport-> 2017/06/21), in that case the number 2. ` check the right syntax to use near '"SELECT SUM(total_profit) AS fee_total FROM transactions WHERE date_created = '2' at line 1`
I see what you are facing. But the date you are getting in param is string. Is your db storing the date_created the date time in epoch ( integer ) format and then comparing with string ?
no, is stored as date formated as 2017-06-21, there is no problem if I make the query manually through the backend, I get results. However the problem is not in the MySQL storage, is on the backend conn.query which does not read the whole string (datereport in the queryParams obj) and it only gets the first char of the first string from the obj and stops there, does not matter if that is a date or an item string, in my case just happend to be the date string first so it gets only the 2 (first char)
I have added some comments over here, see if they make sense codepen.io/anon/pen/PjjeXg?editors=0010
0 
reportsdateres.post(function(req, res, next) {

datereport = req.query.datereport;
itemreport = req.query.itemreport; // this can be null for querying all items

req.getConnection(function(err, conn) {

    if (err) return next("Cannot Connect");
    if (itemreport==null || itemreport=='') {
        var sql = "SELECT SUM(total_profit) AS fee_total FROM transactions WHERE date_created = ?";
        var params = [datereport];
    }else{
        var sql = "SELECT SUM(total_profit) AS fee_total FROM transactions WHERE date_created = ? AND item = ?";
        var params = [datereport, itemreport];
    }
    var query = conn.query(sql, params, function(err, rows) {
    //                                                                                                     ^^^^^^^^^^^^ 

        // do myStuff

    });
}); });
Improve this answer

1 Comment

Thanks, your suggestion has critical syntax errors after if (itemreport==null || itemreport='') { - codepen.io/anon/pen/ZyyzKv?editors=0010

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.