Passing value from a dropdown or text field in Wordpress to MySQL query in functions.php

Question

I have a dropdown on one of my Wordpress pages, I want the value selected from this dropdown to be passed to MySQL query in functions.php file dynamically. I have created a shortcode in the functions.php file in which currently the where clause is static, however I want to be able to dynamically generate the where clause based on the value selected from the dropdown when the Submit button is pressed.

I am having trouble obtaining the selected value from the dropdown and passing it on to MySQL query.

I have tried reading several posts on SO, but have not been able to figure out how to pass selected value to the query Build dynamic WHERE clause in mySQL, PHP string variable in WHERE clause MySQL, How to pass the selected value in dropdown (HTML) to PHP

<select name="C_Option">
<option value=""></option>
<option value="acenaphthene">acenaphthene</option>
.....
<option value="acetone cyanohydrin">acetone cyanohydrin</option>
</select>

<input type="Submit">

<?php
$C_Option = $_POST['C_Option'];


function(){
global $wpdb;
$myrows = $wpdb->get_results("SELECT * FROM PNaphtha WHERE `Compound` = 
'".$C_Option."', ARRAY_A);
ob_start();
foreach ( $myrows as $row) {
echo "Compound: ".$row['Compound'].", Critical Temperature (K): ".$row['Tc 
(K)'];
}
return ob_get_clean();
};

What I am trying to do is obtain the selected value from the dropdown, pass it to a variable and then use that in MySQL query.

I actually have a shortcode created which seems to be working fine, with the exception that the value in the WHERE clause is static

add_shortcode('wpse_233032_shortcode', function(){
global $wpdb;
$myrows = $wpdb->get_results("SELECT * FROM PNaphtha WHERE `Compound` = 
'abietic acid'", ARRAY_A);
ob_start();
foreach ( $myrows as $row) {
echo "Compound: ".$row['Compound'].", Critical Temperature (K): ".$row['Tc 
(K)'];
}
return ob_get_clean();
});

The above shortcode returns the expected results, please see the bottom of the screenshot

Compound: acetone, Critical Temperature (K): 508.200

However, I want to be able to dynamically update the

SELECT * FROM PNaphtha WHERE `Compound` = //value from dropdown

either in the shortcode or directly on the html page in Wordpress when the Submit button is pressed.

Edit1:

add_shortcode('wpse_233032_shortcode', function(){
global $wpdb;
$compound = filter_input( INPUT_POST, 'C_Option' );
$compound = $compound ? $compound : 'acetone';
$query = $wpdb->prepare( "SELECT * FROM 
PNaphtha WHERE `Compound` = %s", $compound );
$myrows = $wpdb->get_results( $query, ARRAY_A );

ob_start();
foreach ( $myrows as $row) {
echo "Compound: ".$row['Compound'].", Critical Temperature (K): ".$row['Tc 
(K)'];
}
return ob_get_clean(); 
});

Answer 1

If I properly understand your question, you can do it like so:

1. In the shortcode, add this before the $myrows:

   // Use the submitted "compound" if any. Otherwise, use the default/static value.
   $compound = filter_input( INPUT_POST, 'C_Option' );
   $compound = $compound ? $compound : 'abietic acid';
   

2. Then run the dynamic MySQL query by utilizing the wpdb::prepare() function in WordPress:

   $query = $wpdb->prepare( "SELECT * FROM PNaphtha WHERE `Compound` = %s", $compound );
   $myrows = $wpdb->get_results( $query, ARRAY_A );
   

   That will replace the $myrows in your code.

UPDATE

You need to put the select in a form like so:

<form method="POST" action="">
  <select name="C_Option">
    ...
  </select>
  <input type="submit">
</form>

Otherwise, the form will not be submitted — unless you use JavaScript, but that's another story.