sql query problem in c++

[rahulvishwakarma]

hi to all, i've centos 7.5 in VM. I've a problem with sql query. here i want to display product, customer and billing information by a one sql. here is a program.


void classBill::todaySales()
{
mysql = classConn::connection();
mysql->reconnect = true;

draw->clrscr();
draw->drawRect();

draw->gotoxy(15, 3);

cout << "Sales By Date";
draw->gotoxy(13,4);
cout << "-----------------";
draw->gotoxy(10, 5);
cout << "Enter Date to see sale : ( yyyy / MM/ dd ) : ";
do
{
flag = true;
draw->gotoxy(55, 5);
ioctl(STDOUT_FILENO, TIOCGWINSZ, &w);
for(int i = 55; i < w.ws_col -1; i++)
{
cout << " ";
}
draw->gotoxy(55, 5);
getline(cin,strdate);
if(strdate.empty())
{
flag == true;
}
else
{
flag = classValid::isValidDate(strdate);
if(!flag)
{
draw->gotoxy(10, 6);
cout << "invalid date please re-enter";
strdate.clear();
getc->getch();
draw->gotoxy(10, 6);
ioctl(STDOUT_FILENO, TIOCGWINSZ, &w);

for(int i = 10; i < w.ws_col - 1; i++)
{
cout << " ";
}
}
}
}while(flag == false);


if(strdate.empty() == true)
{
date *dt = new date;
time_t now = time(0);
tm *ltm = std::localtime(&now);
dt->yy = ltm->tm_year;
dt->mm = ltm->tm_mon+1;
dt->dd = ltm->tm_mday;

strdate = std::to_string(1900 + dt->yy) + "/" + std::to_string(dt->mm) + "/" + std::to_string(dt->dd);
draw->gotoxy(10, 6);
cout << "date : " << strdate << endl;
}
else
{
draw->gotoxy(10, 6);
cout << "date : " << strdate << endl;
}

mysql = classConn::connection();
// here is sql query
sql = "select p.productname, p.rate, b.quantity, b.total, b.billno, c.customername, c.contactaddress, c.mobileno, c.id as CustomerID from tableProductRecords as p, tableBilling as ";
sql += "b, tableCustomers as c where b.dateofsale = '"+ strdate+"' and p.productname in ( select productname from tableProductRecords where productid = ";
sql += "b.productid ) and c.customername in (select customername from tableCustomers where billno = b.billno) order by b.billno;";

qstate = mysql_query(mysql, sql.c_str());
if(!qstate)
{
res = mysql_store_result(mysql);
lines = sqlp->process_result_set(mysql, res, 10, totallen);
}
else
{
draw->gotoxy(10, 7);
cout << "Error in todays sale : " << mysql_error(mysql);
getc->getch();
return;
}

sql = "select sum(total) from tableBilling where dateofsale = '"+strdate+"';";
mysql = classConn::connection();
qstate = mysql_query(mysql, sql.c_str());
if(!qstate)
{
res = mysql_store_result(mysql);
if((row = mysql_fetch_row(res)) != nullptr)
{
if(row[0] != nullptr)
{
gtotal = std::stoi(row[0]);
}
else
{
gtotal = 0;
}
}
}
else
{
draw->gotoxy(10, lines + 25);
cout << "Error in sum(total) : " << mysql_error(mysql);
getc->getch();
}

draw->gotoxy(10, lines + 15);
cout << "Grand total : " << gtotal;

getc->getch();
}


now problem is that sql query returns unwanted rows. as in these pics. here cutomer id "2" is not included in billno = 18, customer id 2 is having bill no 2 not 18 butit customerid 2 is showing in billno with 18. how to get only rows that have correct billno and customer id.

sorry for line indentation in my pc formats is not working
one last thing howto upload images

[kbw]

Please format your code using the code format tags.

Posting a whole function with variables for which there is not definition isn't helpful, nor is posting lots of irrelevant code.

You should post a minimal program that demonstrates the problem.

Have you tried to run the sql directly (without your C++ program)?

[rahulvishwakarma]

yes i tried to run sql in workbench and having same result. Sorry for code format it is not working in my PC.

[coder777]

how to get only rows that have correct billno and customer id.

But what is the correct billno and customer id?

Currently you have b.productid and b.billno. Those values are something floating around within the statement. Nothing particular. So you get a result that is more or less random.

one last thing howto upload images

You can't, but you can post links to the image.

[kbw]

yes i tried to run sql in workbench and having same result.

What result? Does it work or does it not work?

[rahulvishwakarma]

it doesn't work. sql query returns unwanted rows, here cutomer id "2" is not included in billno = 18, customer id 2 is having bill no 2 not 18 butit customerid 2 is showing in billno with 18

[kbw]

If the query doesn't work, it's not going to work when wrapped in a C++ function.

I suggest you fix your query, get it going what you need it to do.

Your program has "hard coded" the query and response. Apply the fixed query to your program, and handle the response correctly.

That's the most direct route to fixing what you have.

[jonnin]

what I would do is print the generated sql statement and run it in your sql tool.
fix and debug it there, and keep track of the changes so you can fix the c++. Once you have it working outside the c++, fix the c++ to generate it exactly as it was when it worked.