2. PHPRunner General questions
  3. update from other connection
This topic is locked

update from other connection

 		8/8/2024 10:57:02 AM
PHPRunner General questions
ffrinai author

Hi,
in MS acces i have this query :
UPDATE trdo INNER JOIN v_diprdo ON trdo.matr = v_diprdo.matricola SET trdo.dtcessdip = [v_diprdo].[dtcessato], trdo.dtagg = Now() WHERE (((trdo.matr) Is Not Null));
i'd like to replicate it in phprunner. I have inserted a custom button and in server tab i insert this code:

$sql = DB::PrepareSQL("UPDATE trdo INNER JOIN v_diprdo ON trdo.matr = v_diprdo.matricola SET trdo.dtcessdip = [v_diprdo].[dtcessato], trdo.dtagg = CURDATE()
WHERE (((trdo.matr) Is Not Null)");
DB::Exec( $sql );

but it doesn't work, i suppose because the v_diprdo table is not in the default connection but on another DB connection (in my project i use 4 different connections).
Is there a way to use DB::SetConnection($name) in a query like this or other solution to update fields from tables in different connections?
thanks
Fabio

Phprunner Enterprise 10.91 build 41974 x64
C
 		cristi 8/8/2024

It should work with DB::SetConnection strictly from multiple connections point of view - did you debug the query? - use the method from here and see if there is an error message.
You should give a more detailed definition than "it doesn't work".
ffrinai author 8/9/2024

Hi,
"it doesn't work" means that no records was updated.
Now I tried this, more complex but working

$rs = DB::Select("trdo", "matr is not null" );
while( $record = $rs->fetchAssoc() )
{
// search in v_diprdo
DB::SetConnection("conn2");
$data = array();
$data["matricola"] = $record["matr"];
$rsdip = DB::Select("v_diprdo", $data );
if( $recorddip = $rsdip->fetchAssoc() )
{
// update dtcess in trdo
DB::SetConnection("");
$datardo = array();
$keyvalues = array();
$datardo["dtcessdip"] = $recorddip["dtcessato"];
$datardo["dtagg"] = now();
$datardo["useragg"] = $userData;
$keyvalues["idrdo"] = $record["idrdo"];
DB::Update("trdo", $datardo, $keyvalues );
DB::SetConnection("conn2");
}
}
// connection default

DB::SetConnection("");

Fabio
C
 		cristi 8/9/2024

I don't understand what database engine are you using now??? You translated the ms access to mysql/postgresql, etc or you are using msacces with phprunner?
Also - you should really debug your code:
For example:

if( $recorddip = $rsdip->fetchAssoc() )

First of all conditionals don't use"=". They use"==".
'=" is used to asign a value to a variable while '==' is used for comparing two values if they are equal.
Also fetchAssoc() returns an associative array so you can't use it like that...
The right way:

$x = mysqli_fetch_assoc($y);
if($x['field'] == 1){
echo "1";
} else
"error";

or if you check multiple rows:

while($x = mysqli_fetch_assoc($y)) {
if($x['field'] == 1){
echo "1";
} else
"error";
}

The way you have written that conditional I don't understand what you plan to achieve - it doesn't work like this.

Also if you use PHPRunner with MSAccess did you follow the steps from here?