ksgush Posted February 14, 2007 Share Posted February 14, 2007 ??? ??? ??? ??? ??? Hi, New Member Here... Was wondering if anyone had any clues on to how I could do this same thing but a hell of alot simpler.... Right now it doesn't return anything.. not even in view source.. But I contribute that to so many open connections, and someplace i know it probably broke.. The purpose of this little script is to take look at orders in a mssql db, than jump through the other db's to find the information it needs to find out if the Quantity on hand is more or equal to than the quantity the customer wants to order. Here is the idea in another way. find orders that are not shipped... SELECT * FROM WSTCompOrders WHERE (NOT (OrderStatus = '6')) AND (NOT (OrderStatus = '5')) AND (NOT (OrderStatus = '4')) Take 'CartID' from above query for each order AND that will be used for OrderNumber below SELECT * FROM WSTCart WHERE (OrderNumber = 'CartID') For each order number... take 'Quantity' and 'ProductID' which is what the customer wants SELECT * FROM Products WHERE (ID = 'ProductID') and 'ProductID' it will give you the manufacturer part number which you will find in the RMSDB to find the quantity. 'ManPartNumbers' RMSDB ON LOCAL SELECT * FROM Item WHERE (ItemLookupCode = 'ManPartNumbers') NOW COMPARE the feild 'Quantity' to the field on WSTCART which is 'Quantity' and if quantity is on rms db is greater than or equal to the quantity on WSTCART then display to screen that says it should be shipped and we must have forgot about it. <?php include("config.php"); $connect1_dbhandle = mssql_connect($connect1_SERVER, $connect1_USER, $connect1_PASS) or die("Couldn't connect to SQL Server on $connect1_SERVER"); $connect1_selected = mssql_select_db($connect1_DB, $connect1_dbhandle) or die("Couldn't open database $connect1_DB"); $connect1_query = "SELECT CartID "; $connect1_query .= "FROM WSTCompOrders "; $connect1_query .= "WHERE (NOT (OrderStatus = '6')) AND (NOT (OrderStatus = '5')) AND (NOT (OrderStatus = '4'))"; $connect1_result = mssql_query($connect1_query); while($connect1_row = mssql_fetch_array($connect1_result)) { $part2_dbhandle = mssql_connect($connect1_SERVER, $connect1_USER, $connect1_PASS) or die("Couldn't connect to SQL Server on $connect1_SERVER"); $part2_selected = mssql_select_db($connect1_DB, $part2_dbhandle) or die("Couldn't open database $connect1_DB"); $part2_query = "SELECT Quantity, ProductID, OrderNumber "; $part2_query .= "FROM WSTCart "; $part2_query .= "WHERE OrderNumber = '" . $connect1_row["CartID"] . "'"; $part2_result = mssql_query($part2_query); while($part2_row = mssql_fetch_array($part2_result)) { $PRODUCTS_dbhandle = mssql_connect($connect1_SERVER, $connect1_USER, $connect1_PASS) or die("Couldn't connect to SQL Server on $connect1_SERVER"); $PRODUCTS_selected = mssql_select_db($connect1_DB, $PRODUCTS_dbhandle) or die("Couldn't open database $connect1_DB"); $PRODUCTS_query = "SELECT ManPartNumbers "; $PRODUCTS_query .= "FROM Products "; $PRODUCTS_query .= "WHERE ID = '" . $part2_row["ProductID"] . "'"; $PRODUCTS_result = mssql_query($PRODUCTS_query); while($PRODUCTS_row = mssql_fetch_array($PRODUCTS_result)) { $RMS_dbhandle = mssql_connect($connect2_SERVER, $connect2_USER, $connect2_PASS) or die("Couldn't connect to SQL Server on $connect2_SERVER"); $RMS_selected = mssql_select_db($connect2_DB, $RMS_dbhandle) or die("Couldn't open database $connect2_DB"); $RMS_query = "SELECT Quantity "; $RMS_query .= "FROM rmsBetaDB "; $RMS_query .= "WHERE ItemLookupCode = '" . $PRODUCTS_row["ManPartNumbers"] . "'"; $RMS_result = mssql_query($RMS_query); while($RMS_row = mssql_fetch_array($RMS_result)) { if( $RMS_row["Quantity"] >= $part2_row["Quantity"] ) { echo $connect1_row["CartID"] . "should be shipped<br><br><br>"; } else { } } } } } ?> Quote Link to comment Share on other sites More sharing options...
Greaser9780 Posted February 14, 2007 Share Posted February 14, 2007 Maybe it's because I'm fairly new, but I am wondering why you are using 3 different db's when you could just create 3 different tables on 1 db. Then you wouldn't need 3 different database connections in one script. Quote Link to comment Share on other sites More sharing options...
ksgush Posted February 14, 2007 Author Share Posted February 14, 2007 The databases arranged this way for different operations and origanizational purposes i guess. My predecessor had things this way.. it isn't the best.. but it is what I have to work with. Quote Link to comment Share on other sites More sharing options...
ksgush Posted February 15, 2007 Author Share Posted February 15, 2007 Anybody has any suggestions ? Please, and thank you. Quote Link to comment Share on other sites More sharing options...
Greaser9780 Posted February 15, 2007 Share Posted February 15, 2007 Try posting this in mssql help forum. THey can give you alot more info on joining tables. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.