Why can't I return a value from $wpdb->get var?

Question

Why can't I return a value from $wpdb->get var?

David Weisser

2021年6月26日 19:50

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:

Here is Table 2:

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

Topic select php Wordpress

Category Web

nmr · Accepted Answer · 2021年6月26日 19:50

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

Why can't I return a value from $wpdb->get var?

About