add a update statment MS SQL in php

kat35601 · 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>

requinix · May 20, 2017

What column(s) are you trying to update and with what new values?

kat35601 · 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)

requinix · 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.

kat35601 · 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"

requinix · 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.

kat35601 · 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>

requinix · May 22, 2017

Looks right to me. Does it work the way you want it to?

kat35601 · May 23, 2017

Thanks works great

Sign In

add a update statment MS SQL in php

Recommended Posts

kat35601

Link to comment

Share on other sites

requinix

Link to comment

Share on other sites

kat35601

Link to comment

Share on other sites

requinix

Link to comment

Share on other sites

kat35601

Link to comment

Share on other sites

requinix

Link to comment

Share on other sites

kat35601

Link to comment

Share on other sites

requinix

Link to comment

Share on other sites

kat35601

Link to comment

Share on other sites

Archived

Browse

Activity

Important Information