Jump to content


Photo

use keys from associative array in and update statement.


Best Answer Sepodati, 21 September 2017 - 08:07 PM

You can use array_keys() to get the keys from order and then build your IN clause using implode

$key_string = implode(',', array_keys($order);
$sql_update="update salesorders set ompOrderApproval= -1 where order_id in ({$key_string})";
Go to the full post


  • Please log in to reply
5 replies to this topic

#1 kk4iku

kk4iku
  • New Members
  • Pip
  • Newbie
  • 5 posts

Posted 21 September 2017 - 05:32 PM

I am pulling values from a text file and putting them in an associative array and then filtering them to get what I need. I would then like to use those key values in my MSSQL update statement. But I am not sure how to convey that to my MSSQL statement. How would I do that?

 

<?php
$lines = [];
$fp    = fopen('sample.txt', 'r');
while (!feof($fp))
{
  $line = fgets($fp);
  $lines[substr($line, 69, 5)] = substr($line, 117, 2);
}
fclose($fp);
$order=array_filter($lines,function($AA){ 
return $AA=='AA';}
);

$sql_update="update salesorders set ompOrderApproval= -1 where order_id in $orders_array_key_place_holder"

$connect = odbc_connect("1234");
$update = odbc_exec($connect, $sql_update);
odbc_close($connect);  
?>

 



#2 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 17,737 posts

Posted 21 September 2017 - 07:35 PM

The syntax for an "in" clause is

... IN ('aaa','bbb',...,'zzz')

Edited by Barand, 21 September 2017 - 07:36 PM.

If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#3 Sepodati

Sepodati
  • Members
  • PipPipPip
  • Advanced Member
  • 210 posts
  • LocationCaro, MI

Posted 21 September 2017 - 08:07 PM   Best Answer

You can use array_keys() to get the keys from order and then build your IN clause using implode

$key_string = implode(',', array_keys($order);
$sql_update="update salesorders set ompOrderApproval= -1 where order_id in ({$key_string})";


#4 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 17,737 posts

Posted 21 September 2017 - 08:15 PM

Assuming that's where the keys are. He hasn't given that information - we have to guess.
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#5 kk4iku

kk4iku
  • New Members
  • Pip
  • Newbie
  • 5 posts

Posted 21 September 2017 - 09:50 PM

I update my code to use the implode and I changed my sql statement to test and I get the correct results. thanks

 

 

$lines = [];
$fp    = fopen('sample.txt', 'r');
while (!feof($fp))
{
  $line = fgets($fp);
  $lines[substr($line, 69, 5)] = substr($line, 117, 2);
}
fclose($fp);
$order=array_filter($lines,function($AA){ 
return $AA=='AA';}
);
$key_string = implode(',', array_keys($order));
//print_r($key_string);


$sql_update="select ompsalesorderid from  .SalesOrders where  ompsalesorderid in ($key_string)";
$connect = odbc_connect("1234");
$result = odbc_exec($connect, $sql_update);
if(!$result){
exit("Error in SQL");
}
 while ($row = odbc_fetch_array($result))
{
   $orderid=$row['ompsalesorderid'];
   echo $orderid;
 }


odbc_close($connect);


#6 Sepodati

Sepodati
  • Members
  • PipPipPip
  • Advanced Member
  • 210 posts
  • LocationCaro, MI

Posted 22 September 2017 - 01:09 AM

Sometimes the guess is right. :)




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users