Jump to content


Photo

add a update statment MS SQL in php

php sql update

Best Answer requinix, 22 May 2017 - 03:32 PM

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. Go to the full post


  • Please log in to reply
8 replies to this topic

#1 kat35601

kat35601
  • Members
  • PipPipPip
  • Advanced Member
  • 96 posts

Posted 19 May 2017 - 09:46 PM

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(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')
 ";
$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> 


#2 requinix

requinix
  • Administrators
  • Forgotten Administrator
  • 8,619 posts
  • LocationWA

Posted 20 May 2017 - 03:36 AM

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

#3 kat35601

kat35601
  • Members
  • PipPipPip
  • Advanced Member
  • 96 posts

Posted 22 May 2017 - 01:03 PM

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) 



#4 requinix

requinix
  • Administrators
  • Forgotten Administrator
  • 8,619 posts
  • LocationWA

Posted 22 May 2017 - 02:16 PM

"
  
 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.

#5 kat35601

kat35601
  • Members
  • PipPipPip
  • Advanced Member
  • 96 posts

Posted 22 May 2017 - 03:18 PM

 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"



#6 requinix

requinix
  • Administrators
  • Forgotten Administrator
  • 8,619 posts
  • LocationWA

Posted 22 May 2017 - 03:32 PM   Best Answer

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.

#7 kat35601

kat35601
  • Members
  • PipPipPip
  • Advanced Member
  • 96 posts

Posted 22 May 2017 - 06:21 PM

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(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') 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, 22 May 2017 - 06:22 PM.


#8 requinix

requinix
  • Administrators
  • Forgotten Administrator
  • 8,619 posts
  • LocationWA

Posted 22 May 2017 - 07:18 PM

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

#9 kat35601

kat35601
  • Members
  • PipPipPip
  • Advanced Member
  • 96 posts

Posted 23 May 2017 - 01:16 PM

Thanks works great






0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users