Using saved acf field value in wpdb query to dynamically populate select field

• Haikukitty

• August 18, 2021 at 5:24 pm

I’m trying to dynamically populate a dropdown field from the results from querying a custom database table.

I currently have two multi-select dropdowns. The first is “States” and is being dynamically populated by a query to “tbl_states” in db.

function acf_load_states( $field ) {
	global $wpdb;
	$states = $wpdb->get_results( "SELECT  * FROM {$wpdb->prefix}states" );
	$field['choices'] = array();
    if($states){        
        foreach( $states as $state_value ) {       
            $field['choices'][ $state_value->state_name ] = $state_value->state_name;
        }
    }
    return $field;
}
add_filter('acf/load_field/name=states_served', 'acf_load_states');

This works great.

Now, I need to take the selections from that field and dynamically populate the “Cities” dropdown based on which states were selected. Right now, I’m find doing this upon post save, at least to get it working. I’ve set up this function to save my states array as a string in a text field upon post save – messy but I wanted to troubleshoot the output of my save.

function my_acf_save_post( $post_id ) {
    
  // get new value
  $colors = get_field( 'states_served', $post_id );
  if( $colors ): 
    //$color = implode( ', ', $colors ); 
    $color = "'" . implode ( "', '", $colors ) . "'";
    $data = substr($color, 1, -1);

   endif;
  // do something
  update_field('testified', $data);
}

When I save the post, I can see the text field “Testified” get populated with my string of selected states without first and last quote mark:
i.e.: Arizona’,’Wisconsin’,’Wyoming

I’ve removed the first and last character so that I can take the saved string and populate it into this query as variable $cs:
$wpdb->get_results( "SELECT * FROM {$wpdb->prefix}cities WHERE state_id IN ('$cs')");

The full function for this is here:

function acf_load_routes_effected_field_choices( $field2 ) {

	global $wpdb;
  
  $cs = get_field( 'testified', $post_id );

  $routes = $wpdb->get_results( "SELECT * FROM {$wpdb->prefix}cities WHERE state_id IN ('$cs')",ARRAY_A);

	$field2['choices'] = array();
    if($routes){        
        foreach( $routes as $route_value ) {       
            $field2['choices'][ $route_value->city_id ] = $route_value->city.' ' .$route_value->city_ascii;
        }
    }
    return $field2;
}
add_filter('acf/load_field/name=locations', 'acf_load_routes_effected_field_choices');

I was able to output the results of this query on the front-end and then down at the bottom echo pairs of id / city name using this PHP snippet:

$cs = get_field( 'testified', $post_id );
  echo $cs;
$routes = $wpdb->get_results( "SELECT * FROM {$wpdb->prefix}cities WHERE state_id IN ('$cs')");
print_r($routes);

foreach ($routes as $details) {
      echo $details->city_id;
      echo $details->city;}

Example output of query:
https://miaj10.sg-host.com/entertainers/test-t-98989898989898

But I cannot get it to work in the back-end admin page to populate the Cities drop down.

However, if I replace my variable $cs in the query by manually pasting in the string from “Testified” directly into the query – it works and populates the drop down.

I’m wondering if I am not able to use get_field here? Because everything works in the back-end if I do it by manually entering the states in the query:

function acf_load_routes_effected_field_choices( $field2 ) {
global $wpdb;
  $routes = $wpdb->get_results( "SELECT * FROM {$wpdb->prefix}cities WHERE state_id IN ('Arizona,Wisconsin')",ARRAY_A);

	$field2['choices'] = array();
    if($routes){        
        foreach( $routes as $route_value ) {       
            $field2['choices'][ $route_value->city_id ] = $route_value->city.' ' .$route_value->city_ascii;
        }
    }
    return $field2;
}
add_filter('acf/load_field/name=locations', 'acf_load_routes_effected_field_choices');

Can someone please point me in the right direction? I’ve spent way too long trying to figure this out.