0

I am trying to delete a record from a table when a condition is met. The condition is this: If there is a record in Table 1 that fails to match a record in Table 2, then delete the record from Table 1.

$survey_mapping =$wpdb->get_results( $wpdb->prepare("SELECT surveyid from oc_partner_x_survey")); 
 foreach($survey_mapping as $row){
     $dup_survey2 =absint($wpdb->get_var( $wpdb->prepare("SELECT surveyls_survey_id FROM oc_surveys_languagesettings WHERE surveyls_survey_id =%d",$row)));   
    if (absint($dup_survey2) > 0) { 
        $error_msg1[] = $dup_survey2;
    } else {
        $error_msg2[0] = $row;
        $error_msg2[1] = $dup_survey;
        $error_msg2[2] = $survey_mapping;

        $wpdb->delete('oc_partner_x_survey', 
        array('surveyid' => absint($row)),array('%d'));     
    }
}   

This is from the console. You can see how the variable $dup_survey is null.

error_msg1":null,"error_msg2":[{"surveyid":"748254"},0,[{"surveyid":"955287"},{"surveyid":"748254"}]]}

Here is Table 1:
enter image description here

Here is Table 2:
enter image description here

I am stuck. Any help or insight would be most appreciated! Thank you!
David

1 Answer 1

1

get_results() by default returns each databse row as object, so $survey_mapping is an array of objects. Therefore you should replace $row with $row->surveyid in the second query:

$dup_survey2 = absint( $wpdb->get_var( 
   $wpdb->prepare("SELECT surveyls_survey_id FROM oc_surveys_languagesettings WHERE surveyls_survey_id = %d", $row->surveyid)
)); 

and in $wpdb->delete():

array('surveyid' => absint($row->surveyid))

By the way, you can use a single SQL query that removes entries from oc_partner_x_survey that have no match in oc_surveys_languagesettings:

DELETE oc_partner_x_survey 
FROM oc_partner_x_survey 
  LEFT JOIN oc_surveys_languagesettings ON oc_surveys_languagesettings.surveyls_survey_id = oc_partner_x_survey.surveyid 
WHERE 
  oc_surveys_languagesettings.surveyls_survey_id IS NULL

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.