Selected value in dropdown list generated using MySQL query does not pass to variable

Question

I have a dropdown on my Wordpress page which uses a MySQL query to populate the values from MySQL to the dropdown list

This is the code on my Wordpress page

<form method="POST" action="">
[wpse_233034_shortcode]
<br>
<input type="submit" name="submit">
[wpse_233036_shortcode]
</form>

The code used to generate the dynamic MySQL query is [wpse_233034_shortcode] which I have defined in the functions.php file

add_shortcode('wpse_233034_shortcode', function(){
global $wpdb;
$results1 = $wpdb->get_results ("SELECT `Compound` FROM PNaphtha ORDER BY 
`SrNo`");
echo '<td><select id="Compound" name="Compound">';
echo '<option value="">Select Compound</option>';
foreach ( $results1 as $result1 ) {
echo '<option>'.$result1->Compound.'</option>';
}
echo '</select></td>';
});

It seems to be working fine until this point. When I dropdown the list, the value for the Compounds is selected from the PNaphtha table and populated on the screen.

What I want next is to pass the selected value from this dropdown into another shortcode/MySQL query which will fetch some more data associated with the Compound

This is what I have so far, however it does not seem to be be able to "pull" the value from the above dropdown into the next MySQL query

add_shortcode('wpse_233036_shortcode', function(){
global $wpdb;
$Compound = filter_input( INPUT_POST, 'Compound' );
$Compound = $Compound ? $Compound : 'acetone';
$query = $wpdb->prepare( "SELECT * FROM PNaphtha WHERE `Compound` = %s", 
$Compound );
$myrows1 = $wpdb->get_results( $query, ARRAY_A );
ob_start();
foreach ( $myrows1 as $row1) {
echo "Compound: ".$row1['Compound'].", "."Formula: ".$row1['Formula'].", 
"."Molecular Weight: ".$row1['MW']."<br>";
}
return ob_get_clean();
});

Now, the shortcode 'wpse_233036_shortcode' seems to be "working", as the default/static value specified 'acetone' is always queried and echoed on the page, even before I take a dropdown and select any value from the dropdown. This is what I get on page load

Compound: acetone, Formula: C3H6O, Molecular Weight: 58.08

What I want is for the dropdown to be populated using the first MySQL query as it does now

"SELECT `Compound` FROM PNaphtha ORDER BY `SrNo`"

Next, when I select a value from this dropdown, instead of the default 'acetone' the selected value should pass to

"SELECT * FROM PNaphtha WHERE `Compound` = %s", $Compound

I was able to get this to work using static/non-SQL queried dropdown list as shown below based on Sally's answer here

<form method="POST" action="">
<select name="C_Option">
<option value=""></option>
<option value="abietic acid">abietic acid</option>
<option value="acenaphthene">acenaphthene</option>
...
<option value="acetone">acetone</option>
</select>
<input type="Submit">
[wpse_233032_shortcode]
</form> 

Answer 1

The problem is because your shortcode callback/function (i.e. wpse_233034_shortcode()) is not returning any output, although it does echo the expected output.

Or more precisely, the select field in your wpse_233034_shortcode() function is outputted to the browser before the form HTML is outputted. Which means the form does not have the select field, hence when you submit the form, the key Compound is not available in the superglobal $_POST.

So make sure your shortcode is returning the proper output, but if you need to echo it in the shortcode callback, then use output buffering just as you did in the wpse_233036_shortcode() function.

add_shortcode('wpse_233034_shortcode', function(){
    global $wpdb;
    $results1 = $wpdb->get_results ("SELECT `Compound` FROM PNaphtha ORDER BY `SrNo`");

    ob_start(); // turn on output buffering
    echo '<td><select id="Compound" name="Compound">';
    echo '<option value="">Select Compound</option>';
    foreach ( $results1 as $result1 ) {
        echo '<option>'.$result1->Compound.'</option>';
    }
    echo '</select></td>';

    return ob_get_clean(); // return the output
});

But where possible, avoid using output buffering and instead, assign the output to a variable, like so:

add_shortcode('wpse_233034_shortcode', function(){
    global $wpdb;
    $results1 = $wpdb->get_results ("SELECT `Compound` FROM PNaphtha ORDER BY `SrNo`");

    $output = ''; // save the output to this variable
    $output .= '<td><select id="Compound" name="Compound">';
    $output .= '<option value="">Select Compound</option>';
    foreach ( $results1 as $result1 ) {
        $output .= '<option>'.$result1->Compound.'</option>';
    }
    $output .= '</select></td>';

    return $output; // return the output
});