2. PHPRunner General questions
  3. [SOLVED] Using MySql IN Clause in Tri-part Server Event
This topic is locked
[SOLVED]

Using MySql IN Clause in Tri-part Server Event

 		5/30/2021 10:54:11 PM
PHPRunner General questions
A
 AlphaBase author

The example shown here: https://xlinesoft.com/phprunner/docs/getselectedrecordkeys().htm

Client before:

params["keys"] = pageObj.getSelectedRecordKeys();

Server:

foreach($params["keys"] as $key){
DB::Exec("delete from carsmodels where make=".$key["id"]);

Uses foreach to loop through the records.

How would you do the same things using an IN clause?

DB::Exec("delete from carsmodels where make IN ".$params["keys"]);

Or ???
A
 		AlphaBase author 5/31/2021

The above is not a great example since in the docs it is referring to a master detail relation. So forgetting about that. Here is my real world example based upon updating a table that works:

Server tri-part Event

$result["Fee_Id"] = $params["Fee_Id"];
$Fee_Id = $params["Fee_Id"];
$selectedRecords = $params["recs"];

$Count = Count($selectedRecords);

DB::SetConnection("AlphaBase");

for ($x = 0; $x < $Count; $x++) {
$Time_Id = $selectedRecords[$x][0];
DB::Exec("UPDATE bill_time SET Fee_Id = ".$Fee_Id." WHERE Time_Id = ".$Time_Id);
}

This allows me to update records but it doesn't seem very efficient.

I would still like to use the IN clause for the array values which may be one to several records.
A
 		acpan 5/31/2021

Maybe you can try to flatten your 2-dimensional array and convert to CSV string.

Asumming your array is:
$selectedRecords[1][0] = 2;
$selectedRecords[2][0] = 4;
$selectedRecords[0][0] = 9;

// Flatten the aray:
$one_dimensional = array_reduce($selectedRecords, 'array_merge', array());
// array(2,4,9)

Convert to CSV string:
$csv_string = implode(",", $one_dimensional);
// 2,4,9

Then execute DB Update or DELETE:

$sql = "DELETE FROM table WHERE id in (".$csv_string.")" ;

Hope it helps.
A
 		AlphaBase author 5/31/2021

That works great acpan. Thanks much.