simple question - small database

[tones1986]

I ran this query on my database:

select O.ORDER_NUM, O.ORDER_DATE, O.CUSTOMER_NUM, C.CUSTOMER_NAME, C.STREET, C.CITY, C.STATE, C.ZIP from CUSTOMER C, ORDERS O where O.CUSTOMER_NUM =148 and C.CUSTOMER_NUM = 148;

+-----------+------------+--------------+--------------------------+----------------+----------+-------+-------+
| ORDER_NUM | ORDER_DATE | CUSTOMER_NUM | CUSTOMER_NAME | STREET | CITY | STATE | ZIP |
+-----------+------------+--------------+--------------------------+----------------+----------+-------+-------+
| 21608 | 2007-10-20 | 148 | Al's Appliance and Sport | 2837
Greenway | Fillmore | FL | 33336 |
| 21619 | 2007-10-23 | 148 | Al's Appliance and Sport | 2837
Greenway | Fillmore | FL | 33336 |
+-----------+------------+--------------+--------------------------+----------------+----------+-------+-------+
2 rows in set (0.00 sec)

This gives me the correct info, but i want to narrow it down even further. I would like ONLY the newest order to show up here. How can i 'comapre' the two order dates here? So that i can print just the the latest order that was made?

There is ONE last part to this...but if i can get the above - it shouldn't be too hard to figure out myself!

I am thinking a nested select statement would be needed, but doing something like:

O.ORDER_DATE < O.ODER_DATE doesnt work etc. Do i have a special date compare function or something i have to use?

Thanks for the help all!

[Menster]

Hi there tones,
Just append this to your query:

ORDER BY O.ORDER_DATE DESC LIMIT 1

[tones1986]

Awesome! Thank you Menster! I knew it was something as simple as that - but i couldn't put the two parts together correctly!

Now i have the data i want, i am using c++ to create a program that will now take this query:

SELECT O.ORDER_NUM, O.ORDER_DATE, O.CUSTOMER_NUM, C.CUSTOMER_NAME, C.STREET, C.CITY, C.STATE, C.ZIP FROM CUSTOMER C, ORDERS O WHERE O.CUSTOMER_NUM =148 AND C.CUSTOMER_NUM = 148 ORDER BY O.ORDER_DATE DESC LIMIT 1;

In my C++ to print out the data in a similar fashion to the mysql command line i use the row[n] values for each column of data. Would it be possible to use the ORDER_NUM from the out put from the 1st query as a variable for a second query?

Lets say the above query (without so many variables prints)
123 2007-10-20 Sams Store

I would want to take the order number (123) and use that as a way to select the order from a different table (order_line) ...which contains data like:

+-----------+----------+-------------+--------------+
| ORDER_NUM | PART_NUM | NUM_ORDERED | QUOTED_PRICE |
+-----------+----------+-------------+--------------+
| 21608 | AA11 | 11 | 21.95 |
| 21610 | BB22 | 1 | 495.00 |
| 21610 | CC33 | 1 | 399.99 |

I would then take the order data for this line and print that out to screen in a similar fashion as it appears here, but obvisouly with some math (whcih i can do myself).

So once again my question is... do i, or can i, add another step of my above query to select the order number found for the latest order to then print all the items within that order? Or is there a way to use the row[n] syntax to get that data and use it in another query?

Also, if you are c++ users which i am sure you are, is it possible to ask the user for a value (in my case customer number) then use this value in the query string in place of hard coded customer number 148?

I tried doing:

char VAL[2];

cout >> "enter cust. number: ";
cin >> VAL;

Thank you for helping me out once again. Mysql is a lot of fun - and mixing it up with c++ is pretty impressive with how much you can do...noted i am using .0001% of the abilities --- it still is pretty awesome!

[Menster]

You could use a nested query for what you want to do, however your nested query can only return a single column (MySql rules)
as follows:

SELECT * FROM order_line WHERE col_name IN
{
SELECT O.ORDER_NUM FROM CUSTOMER C, ORDERS O WHERE O.CUSTOMER_NUM =148 AND C.CUSTOMER_NUM = 148 ORDER BY O.ORDER_DATE DESC LIMIT 1
};

Will match all the entries from orderline where col_name is in the set of order_num's returned by the nested query.

If you want to do it in two steps, you can still capture all the other variables that you did in the first statement, then pull out the ORDER_NUM value and stick it in a variable. And then construct a second query passing that variable as a parameter in the WHERE clause:

SELECT * FROM order_line WHERE col_name = MY_VAR_NAME;

Unfortunately, I havent programmed C++ in donkey's years so i'm not sure how you would build the string to pass as a query but your logic is there.

Hope I've helped :)

[tones1986]

My idea is to get the order number from the row[0] part of the query you helped me create in the first post above. Hopefully i can make this happen in my c++.

Assuming i could do something like:

char orderNum[5];

orderNum = row[0];

If i do this, i could use your line in your post to compare with a variable? Does anyone else have c++/mysql experience?

I would want to do :

select PART_NUM, NUM_ORDERED from ORDER_LINE where ORDER_NUM = orderNum;

Could/Would this work? Is this the incorrect way?

Thanks again...you are very helpful!

[tones1986]

Alright got myself a good bit further in figuring out how to insert my variables into the query. Basically i had to store multiple sections of the query then strcat() the inputed data into the query wherever i needed it, then sent the char data in the query such as:

query_state = mysql_query(connection, first);

My problem now is i get the following 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 '21619' at line 1

This occurs when i try this set of statements:

while ( ( row = mysql_fetch_row(result)) != NULL )
{
  cout << left <<  setw(7) << row[0] << setw(12) << row[1] << setw(5) << row[2] << setw(15) << row[3] << setw(15) << row[4] << setw(10) << row[5] << setw(3) << row[6] << setw(6) << row[7] << endl;

cout << "row[0]: " << row[0];
strcat(orderNum, row[0]);

}
cout << orderNum;

strcpy(first, "select OL.PART_NUM, P.DESCRIPTION, OL.NUM_ORDERED, P.PRICE, (OL.NUM_ORDERED*P.PRICE) AS TOT_PRICE from ORDER_LINE OL, PART P where OL.PART_NUM = P.PART_NUM and OL.ORDER_NUM = ");
strcpy(first, orderNum);

query_state = mysql_query(connection, first);

if (query_state !=0)
{
  cout << mysql_error(connection) << endl;
  return 1;
}

result2 = mysql_store_result(connection);

For whatever reason it doesnt like the orderNum value (which is the value listed in the error above). The value is a declared like:

char orderNum[4] = "";

Which is exactly how i named my previous variables when i solved the previous issues i was having. I did the code the exact same way, yet for whatever reason it doesn't like the value stored in orderNum. Is it because it came from an sql query and its a different type? Am i missing something important here?

Any help would be appreciated! Thank you !