kat35601 Posted May 19, 2017 Share Posted May 19, 2017 Hello I need to update the tranmit field in table SalesOrders where orders are in my $sql2 query results field $orderid. I am just not sure how/where to do that. Thanks <html> <head> </head> <body> <?php $connect = odbc_connect("removed"); if (!$connect) { exit("Connection Failed: " . $connect); } $gr_total = 0; $gr_count = 0; $sql = " SELECT distinct '1234' AS CIT ,'A' as record_type ,ltrim(rtrim(SO.ompCustomerOrganizationID))as customer --,ltrim(rtrim(so.ompShipLocationID)) as location ,ltrim(rtrim(left(cmoName,30))) as name ,left(ltrim(rtrim(cmoAddressLine2)),30) as address1 ,ltrim(rtrim(cmoCity)) as city ,ltrim(rtrim(cmoState)) as state ,ltrim(rtrim(cmoPostCode)) as postal , ltrim(rtrim(REPLACE(REPLACE(REPLACE(cmoPhoneNumber, '(', ''), ')', ''), '-', ''))) as phone FROM m1_kf.dbo.SalesOrders SO LEFT JOIN m1_kf.dbo.Organizations ON cmoOrganizationID = SO.ompCustomerOrganizationID WHERE SO.ompOrderDate >= '05-18-2017' AND ompPaymentTermID in ('CN30','CTN30') "; $sql2 = " select '1234' as clid ,' ' as trade ,'R' as record_ty ,ltrim(rtrim(ompCustomerOrganizationID)) as cust ,ltrim(rtrim(ompSalesOrderID)) as orderid , right('00000000'+cast(cast(round(ompOrderTotalBase,0)as int) as varchar(), as num ,REPLACE(CONVERT(VARCHAR(10), ompRequestedShipDate, 1), '/', '') as reqship ,'030' as terms ,REPLACE(CONVERT(VARCHAR(10), ompRequestedShipDate, 1), '/', '') as ship from m1_kf.dbo.SalesOrders where ompOrderDate >= '05-18-2017' and ompOrderTotalBase >1 AND ompPaymentTermID in ('CN30','CTN30') "; $result = odbc_exec($connect, $sql); if (!$result) { exit("Error in SQL"); } $mycount = 0; $tradestyle = ' '; $address2=' '; $my_file = date('m-d-Y_hia') . '.CO'; $handle = fopen($my_file, 'w+') or die('Cannot open file: ' . $my_file); while ($row = odbc_fetch_array($result)) { $record_type = $row['record_type']; $cit_cust_id = $row['CIT']; $cust_num = $row['customer']; $name = $row['name']; $address = $row['address1']; $city = $row['city']; $state = $row['state']; $postal = $row['postal']; $phone = $row['phone']; fprintf($handle, "%-4s%-2s%-1s%-15s%-30s%-30s%-30s%-17s%-2s%-9s%-10s", $cit_cust_id, $tradestyle, $record_type, $cust_num, $name, $address,$address2, $city, $state, $postal, $phone . "\n"); $mycount = $mycount + 1; } $results = odbc_exec($connect, $sql2); if (!$results) { exit("Error in SQL"); } $mycount2 = 0; $space1 =' '; $space6 = ' '; $space12 = ' '; $today = date("mdy"); while ($row = odbc_fetch_array($results)) { $client = $row['clid']; $trade = $row['trade']; $record_ty = $row['record_ty']; $cust = $row['cust']; $orderid = $row['orderid']; $num = $row['num']; $reqship = $row['reqship']; $terms = $row['terms']; $ship = $row['ship']; $mycount2 = $mycount2 + 1; $gr_total = $gr_total + $row['num']; $tradestyle = '99'; $custnum = '999999999999999'; $record = 'S'; $recordtype = '999999T999999999999999'; fprintf($handle, "%-4s%-1s%-1s%-15s%-22s%-8s%-1s%-6s%-3s%-6s", $client, $trade,$record_ty , $cust, $orderid, $num,$space1, $reqship, $terms, $ship . "\n"); } fprintf($handle, "%-4s%-2s%-1s%-15s%06d%06d%-6s%012d", $client, $tradestyle, $record, $custnum, $mycount, $mycount2, $space6, $gr_total); fprintf($handle, "\n"); fprintf($handle, "%-22s%06d%06d%-6s%012d%-12s%-6s", $recordtype, $mycount, $mycount2, $space6, $gr_total, $space12, $today); fclose($handle); odbc_close($connect); ?> </body> </html> Link to comment Share on other sites More sharing options...
requinix Posted May 20, 2017 Share Posted May 20, 2017 What column(s) are you trying to update and with what new values? Link to comment Share on other sites More sharing options...
kat35601 Posted May 22, 2017 Author Share Posted May 22, 2017 The column is transmit and I want to set it to a 1 Update SalesOrders set transmit = 1 where The where would be the list of sales orders ($orderid) Link to comment Share on other sites More sharing options...
requinix Posted May 22, 2017 Share Posted May 22, 2017 " select '1234' as clid ,' ' as trade ,'R' as record_ty ,ltrim(rtrim(ompCustomerOrganizationID)) as cust ,ltrim(rtrim(ompSalesOrderID)) as orderid , right('00000000'+cast(cast(round(ompOrderTotalBase,0)as int) as varchar(), as num ,REPLACE(CONVERT(VARCHAR(10), ompRequestedShipDate, 1), '/', '') as reqship ,'030' as terms ,REPLACE(CONVERT(VARCHAR(10), ompRequestedShipDate, 1), '/', '') as ship from m1_kf.dbo.SalesOrders where ompOrderDate >= '05-18-2017' and ompOrderTotalBase >1 AND ompPaymentTermID in ('CN30','CTN30') "That query is going to get all the rows you want to update, right? It chooses which records to return according to the WHERE conditions, right? So make an UPDATE query that has the same conditions to update the same records. Link to comment Share on other sites More sharing options...
kat35601 Posted May 22, 2017 Author Share Posted May 22, 2017 do I put that at the end of the while statement and how do I call it? Thanks while ($row = odbc_fetch_array($results)) { $client = $row['clid']; $trade = $row['trade']; $record_ty = $row['record_ty']; $cust = $row['cust']; $orderid = $row['orderid']; $num = $row['num']; $reqship = $row['reqship']; $terms = $row['terms']; $ship = $row['ship']; $mycount2 = $mycount2 + 1; $gr_total = $gr_total + $row['num']; $tradestyle = '99'; $custnum = '999999999999999'; $record = 'S'; $recordtype = '999999T999999999999999'; fprintf($handle, "%-4s%-1s%-1s%-15s%-22s%-8s%-1s%-6s%-3s%-6s", $client, $trade,$record_ty , $cust, $orderid, $num,$space1, $reqship, $terms, $ship . "\n"); $update = odbc_exec($connect, $sql_update); $sql_update="update SalesOrders set transmit = 1 where ompSalesOrderID in $orderid" Link to comment Share on other sites More sharing options...
requinix Posted May 22, 2017 Share Posted May 22, 2017 Forget about $orderid. The data is coming from the $sql2 query, right? Every single $orderid came from that query, right? That query is what decided which orderids to return, right? It looked at everything and decided which ones to get, right? Now you want to do something to all those orders that came back, right? They all get the same treatment, right? Look at the query you started with. You gave it conditions to match against each order. Here's where it gets magical: If you do an UPDATE query with those very same conditions then it'll target the very same orders that the first query returned. You follow that? So. All you have to do is make a new UPDATE query with the right syntax that does the thing you want it to do, copy those conditions from the earlier SELECT, and run it. Link to comment Share on other sites More sharing options...
kat35601 Posted May 22, 2017 Author Share Posted May 22, 2017 This is what I ended up with ??? thanks <html> <head> </head> <body> <?php $connect = odbc_connect("removed"); if (!$connect) { exit("Connection Failed: " . $connect); } $gr_total = 0; $gr_count = 0; $sql = " SELECT distinct '1234' AS CIT ,'A' as record_type ,ltrim(rtrim(SO.ompCustomerOrganizationID))as customer --,ltrim(rtrim(so.ompShipLocationID)) as location ,ltrim(rtrim(left(cmoName,30))) as name ,left(ltrim(rtrim(cmoAddressLine2)),30) as address1 ,ltrim(rtrim(cmoCity)) as city ,ltrim(rtrim(cmoState)) as state ,ltrim(rtrim(cmoPostCode)) as postal , ltrim(rtrim(REPLACE(REPLACE(REPLACE(cmoPhoneNumber, '(', ''), ')', ''), '-', ''))) as phone FROM m1_kf.dbo.SalesOrders SO LEFT JOIN m1_kf.dbo.Organizations ON cmoOrganizationID = SO.ompCustomerOrganizationID WHERE SO.ompOrderDate >= '05-18-2017' AND ompPaymentTermID in ('CN30','CTN30') "; $sql2 = " select '1234' as clid ,' ' as trade ,'R' as record_ty ,ltrim(rtrim(ompCustomerOrganizationID)) as cust ,ltrim(rtrim(ompSalesOrderID)) as orderid , right('00000000'+cast(cast(round(ompOrderTotalBase,0)as int) as varchar(), as num ,REPLACE(CONVERT(VARCHAR(10), ompRequestedShipDate, 1), '/', '') as reqship ,'030' as terms ,REPLACE(CONVERT(VARCHAR(10), ompRequestedShipDate, 1), '/', '') as ship from m1_kf.dbo.SalesOrders where ompOrderDate >= '05-18-2017' and ompOrderTotalBase >1 AND ompPaymentTermID in ('CN30','CTN30') and UOMPSCHEDULENUMBER !=1 "; $result = odbc_exec($connect, $sql); if (!$result) { exit("Error in SQL"); } $mycount = 0; $tradestyle = ' '; $address2=' '; $my_file = date('m-d-Y_hia') . '.CO'; $handle = fopen($my_file, 'w+') or die('Cannot open file: ' . $my_file); while ($row = odbc_fetch_array($result)) { $record_type = $row['record_type']; $cit_cust_id = $row['CIT']; $cust_num = $row['customer']; $name = $row['name']; $address = $row['address1']; $city = $row['city']; $state = $row['state']; $postal = $row['postal']; $phone = $row['phone']; fprintf($handle, "%-4s%-2s%-1s%-15s%-30s%-30s%-30s%-17s%-2s%-9s%-10s", $cit_cust_id, $tradestyle, $record_type, $cust_num, $name, $address,$address2, $city, $state, $postal, $phone . "\n"); $mycount = $mycount + 1; } $results = odbc_exec($connect, $sql2); if (!$results) { exit("Error in SQL"); } $mycount2 = 0; $space1 =' '; $space6 = ' '; $space12 = ' '; $today = date("mdy"); while ($row = odbc_fetch_array($results)) { $client = $row['clid']; $trade = $row['trade']; $record_ty = $row['record_ty']; $cust = $row['cust']; $orderid = $row['orderid']; $num = $row['num']; $reqship = $row['reqship']; $terms = $row['terms']; $ship = $row['ship']; $mycount2 = $mycount2 + 1; $gr_total = $gr_total + $row['num']; $tradestyle = '99'; $custnum = '999999999999999'; $record = 'S'; $recordtype = '999999T999999999999999'; fprintf($handle, "%-4s%-1s%-1s%-15s%-22s%-8s%-1s%-6s%-3s%-6s", $client, $trade,$record_ty , $cust, $orderid, $num,$space1, $reqship, $terms, $ship . "\n"); } fprintf($handle, "%-4s%-2s%-1s%-15s%06d%06d%-6s%012d", $client, $tradestyle, $record, $custnum, $mycount, $mycount2, $space6, $gr_total); fprintf($handle, "\n"); fprintf($handle, "%-22s%06d%06d%-6s%012d%-12s%-6s", $recordtype, $mycount, $mycount2, $space6, $gr_total, $space12, $today); fclose($handle); $sql_update="update m1_kf.dbo.SalesOrders set UOMPSCHEDULENUMBER=1 where ompOrderDate >= '05-18-2017' and ompOrderTotalBase >1 AND ompPaymentTermID in ('CN30','CTN30') and UOMPSCHEDULENUMBER !=1"; $update = odbc_exec($connect, $sql_update); odbc_close($connect); ?> </body> </html> Link to comment Share on other sites More sharing options...
requinix Posted May 22, 2017 Share Posted May 22, 2017 Looks right to me. Does it work the way you want it to? Link to comment Share on other sites More sharing options...
kat35601 Posted May 23, 2017 Author Share Posted May 23, 2017 Thanks works great Link to comment Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.