01 number input for date not giving output on system-CodePudding

I can't enter 01 into my c input, it will return with empty result but when i type other date such as 12 and 11 it does show in the system.

string month;
    cin.ignore(1, '\n');
    cout << "Please Enter Month For Example 01 for January:";
    getline(cin, month);
    string search_query = "SELECT DATE(OrderDate), SUM(TotalPrice) FROM order1 WHERE MONTH(OrderDate) like '%"   month   "%' GROUP BY DATE(OrderDate)";
    const char* q = search_query.c_str();
    qstate = mysql_query(conn, q);

DATABASE

This is what happen if I enter "01"

This is what happen when I enter "11"

This is when I type "12" it successfully show

CodePudding user response：

The problem is that you are using the LIKE operator in MySQL, which checks to see if the pattern specified on the right occurs in the string specified on the left. The pattern "01" probably doesn't occur in the value on the left, since the string on the left should be "1" for January orders, and that doesn't have a "0" in it.

I also imagine that if you type "1" you would actually see all orders from January, October, November, and December since all those months have "1" in them.

Try using something like MONTH(OrderDate) = 1 instead.

To be more explicit: try changing the line in your program that defined search_query to this:

std::string search_query = "SELECT DATE(OrderDate), SUM(TotalPrice) FROM order1 WHERE MONTH(OrderDate) = "   month   " GROUP BY DATE(OrderDate)";

By the way, for a more robust program, you should also make sure you turn the user-supplied month number into an integer before adding it to the query string; right now you are letting users insert arbitrary strings into your query, which could be dangerous.