Jump to content

add a update statment MS SQL in php

kat35601

By kat35601
in PHP Coding Help

 Share
Go to solution Solved by requinix,

Recommended Posts

kat35601 Advanced Member

kat35601

Posted
Posted

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
requinix Prolific Member

requinix

Posted
Posted 
"


 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(8)),8) 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
kat35601 Advanced Member

kat35601

Posted
  • Author
Posted

 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
  • Solution
requinix Prolific Member

requinix

Posted
  • Solution
Posted

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
kat35601 Advanced Member

kat35601

Posted
  • Author
Posted (edited)

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>
Edited by kat35601
Link to comment
Share on other sites
This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
 Share
Go to topic listing
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.