Getting value from database table depending on field value

Question

I am using a plugin called formidable which is similar to Gravity Forms in terms of purpose.

I have two fields of interest. The first field loads up all the values in the Town column of my wp_locations table. Using the code below.

function frm_populate_posts_town($values, $field){
    if($field->id == 800){ //change 125 to the ID of the field to populate
    global $wpdb;
    $towns =$wpdb->get_results("SELECT Town FROM wp_locations");
    $values['options'] = array(); 
    foreach ($towns as $town) {

     $values['options'][$town->Town] = $town->Town;
     }

    }
    return $values;
}

The second field is a hidden field that automatically selects the value in the 'County' Column depending on the value of the first field. (btw $_POST['item_meta'][802] represents the hidden field

add_filter('frm_validate_field_entry', 'my_custom_validation_town', 8, 3);

function my_custom_validation_town($errors, $posted_field, $posted_value){
    if($posted_field->id == 802){ //change 25 to the ID of the hidden field
        $town_b = $_POST['item_meta'][800];//Gets the value from the first field
        global $wpdb;

        $query = "SELECT County FROM wp_locations WHERE Town = $town_b";
        $the_county = $wpdb->get_var($query);

          $_POST['item_meta'][802] = $the_county; 
    }
    return $errors;
}

The second does not work.

EDIT

I found out that I need to use the action hook frm_after_create_entry and then add a Javascript in order to do 'Text calculations' which is one of the functions the plugin has.

Unfortunately I do not know how to add the SQL query as a variable using the value of #field_800 e.g:

"SELECT County FROM wp_locations WHERE Town =`#field_800`"

<script type="text/javascript">
  jQuery(document).ready(function($){
$('#field_800').change(function(){

 var val1 = $("#field_800").val();//How can I add mySQL here 

$("#field_802").val(val1);
});
});
</script>

Answer 1

My guess is that this is the wrong hook for what you are trying to do, or you are using it incorrectly.

A few notes:

You are passing unvalidated data into a query. Please don't do that. This...

$town_b = $_POST['item_meta'][800];//Gets the value from the first field
global $wpdb;

$query = "SELECT County FROM wp_locations WHERE Town = $town_b";

... is begging to be hacked. Any data in $_POST['item_meta'][800] hits the DB unfiltered. Very dangerous. Use prepare, at the very least.

$query = $wpdb->prepare("SELECT County FROM wp_locations WHERE Town = %s",$town_b);

Second, you've hook into what is pretty obviously a field validation system but you are trying to set a $_POST value. That is clearly not what it is meant for. So you are either too late or too early setting the value, or something. It is hard to tell with your two functions out of context the way they are. I can almost guarantee that you need to be using another hook though. Add some more detail and I will see about editing with a more specific answer.

Answer 2

Hi I solved this by just using the first function and Jquery. I called up BOTH the Town and County in the first function as values inthe drop down list Town-County then i used Jquery string split to split the string using '-' as a separator and then put the values into two separate fields.

function frm_populate_posts_town($values, $field){
if($field->id == 800){ //change 125 to the ID of the field to populate
global $wpdb;
$towns =$wpdb->get_results("SELECT Town,County FROM wp_locations");
$values['options'] = array(); 
foreach ($towns as $town) {

 $values['options'][$town->Town] = $town->Town.'-'.$town->County;
 }

}
return $values;
}

I used this to split the dropdown list values into two separate fields.

<script type="text/javascript">
jQuery(document).ready(function($){

$('#field_hmm').change(function(){

var str = $("#field_hmm").val();
var substr = str.split('-');
var val1 = substr[0];
var val2 = substr[1];
$("#field_xhoxo1").val(val1);
  $("#field_xhoxo2").val(val2);
});
});
</script>