PHP MySQL: Delete row if its not present in API da

Question

I am getting data from API and adding it to a table. we have a status to control the data visible on website if Status = available show on web or if Status = Expired remove it from website

While inserting the data from API, I am running update and insert query.

$final_data = $response->Results; - //Data From API JSON converted to Array

and checking if data is already their in table Update if data is not their Insert

if(is_array($final_data)) {
    foreach ($final_data as $row) {
        $id = $row->id
        $data1 = $row->data1;
        $data2 = $row->data2;
        $data3 = $row->data3;
    }

    global $wpdb;

    // Insert Data if not exists
    $updateJobsDatabase_mainquery = $wpdb->get_results("SELECT id  FROM ".$table_name." WHERE id = ".$id."");

    if (count($updateJobsDatabase_mainquery) > 0) { 
        // Update query excluding id
    } else {
        // Insert query including id
    }
}

But by this logic when status changes to expired for one of the data row in API. it still stays in database.

I want to remove that row from local table. So what will be a proper way to remove the rows which are not falling in the If-Else statements. i.e. extra rows which are not in API data but present in local database.

I am thinking if (count($updateJobsDatabase_mainquery) = 0) will return the data which does not match the id of API data. and to remove it using

$deleteIfStatus = $wpdb->query('DELETE FROM '.$table_name_temp.' WHERE id = what? ');

but then again what should I add in where clause? if this logic is correct

Answer 1

Final Solution as recommended by Nick - Outside for each loop

$deleteFromDatabase_mainquery = $wpdb->get_results("SELECT PositionId  FROM `".$table_name_temp."`");

$deleteFromDatabase_tmparr1 = array();
$deleteFromDatabase_tmparr2 = array();

foreach ($deleteFromDatabase_mainquery as $row) { // Push local table id's in empty array
    array_push($deleteFromDatabase_tmparr1, $row->PositionId);
}
foreach ($final_data as $row) { // Push API data id's in empty array
    array_push($deleteFromDatabase_tmparr2, $row->PositionId);
}
// Add a joint array excluding excess records.
$deleteFromDatabase_finalqr = array_intersect($deleteFromDatabase_tmparr2,$deleteFromDatabase_tmparr1);

// Delete data from live table where ids are not present in new joint array
$deleteIfStatus = $wpdb->query("DELETE FROM ".$table_name." WHERE PositionId NOT IN ('" . implode( "', '" , $deleteFromDatabase_finalqr ) . "' )");

Answer 2

If I understand your code correctly you should be able to use (assuming id is numeric)

$deleteIfStatus = $wpdb->query('DELETE FROM '.$table_name_temp.' WHERE id NOT IN (' . implode(',', array_map(function ($v) { return $v->code; }, $updateJobsDatabase_mainquery)) . ')');

If id is not numeric, use

$deleteIfStatus = $wpdb->query('DELETE FROM '.$table_name_temp.' WHERE id NOT IN (\'' . implode("','", array_map(function ($v) { return $v->code; }, $updateJobsDatabase_mainquery)) . '\')');