1

I would like to be able to modify a MySQL query based on the selection in a dropdown menu. This would then update the subsequent table shown on the page.

The current code I have is below (note I'm using PHP code snippets (Insert PHP) plugin) however this doesn't have the desired result as no table is generated.

Any assistance would be greatly appreciated.

<form method="POST">
<select name="post_ID">
    <option value="21">21</option>
    <option value="22">22</option>
</select>
</form>

[insert_php]
global $wpdb;
echo $_POST['post_ID'];
$myQuery = $wpdb->get_results('SELECT * FROM ' . $wpdb->prefix.'posts' . 'WHERE ID = $_POST['post_ID']');

if($myQuery){
echo '<table>';
    echo "<tr>"; 
    echo "<th>Header 1</th>"; 
    echo "<th>Header 2</th>"; 
    echo "<th>Header 3</th>"; 
    echo "<th>Header 4</th>"; 
    echo "</tr>"; 
foreach ( $myQuery as $result ) 
    {
        echo '<tr><td>'.$result->post_title.'</td><td>'.$result->post_status.'</td><td>'.$result->post_date_gmt.'</td><td>'.$result->ID.'</td></tr>';
    }
echo '</table>';
}
[/insert_php]
  • when and how does the form get submitted? I don't see any trigger in the above code. – Atlas_Gondal Jul 15 '18 at 5:26
  • Sorry, I'm no expert in coding. I've added <input type="submit" name="submit" value="Get Selected Values" /> but still not having much luck – Morts81 Jul 15 '18 at 5:45
1

You don't need to write your own queries, just use wordpress functions.

For instance, to get post by id; use get_post

<form method="POST">
  <select name="post_ID">
    <option value="21">21</option>
    <option value="22">22</option>
  </select>

 <input type="submit" value="Get Selected Value" />
</form>

<?php

 $post_id = $_POST['post_ID'];

 if ( is_int( $post_id ) && !empty ($post_id ) ){

   $post = get_post( $post_id );

   if($post){

      echo '<table>';
      echo "<tr>";
      echo "<th>Header 1</th>";
      echo "<th>Header 2</th>";
      echo "<th>Header 3</th>";
      echo "<th>Header 4</th>";
      echo "</tr>";

      //foreach loop is removed because it will return only single object
      echo '<tr><td>'.$post->post_title.'</td><td>'.$post->post_status.'</td><td>'.$post->post_date_gmt.'</td><td>'.$post->ID.'</td></tr>';

      echo '</table>';
  }

}

BTW, if you need to retrieve multiple posts then try get_posts

  • If I wanted to continue to use $myQuery = $wpdb->get_results('SELECT * FROM ' . $wpdb->prefix.'posts' . 'WHERE ID = $_POST['post_ID']'); is this correct? I feel like there is something wrong there. – Morts81 Jul 15 '18 at 6:53
  • I won't recommend using query, because it is bad practice. If you need to use the query then you need to fix it, it has several issues like concatenation and spacing. I didn't tested this but you can try this: $myQuery = $wpdb->get_results("SELECT * FROM ' . $wpdb->prefix.'posts' . ' WHERE ID = '. $_POST['post_ID']"); – Atlas_Gondal Jul 15 '18 at 7:05
  • The reason why I want to use query is because I used the wp_posts table as an example to learn how to carry out this project. In actual fact I'll use this structure to query different tables that I add to the database. I can assign the variable now from the dropdown, however still struggling to return anything from the query when adding in a WHERE dependancy. There is no such issue when i use $myQuery = $wpdb->get_results('SELECT * FROM ' . $wpdb->prefix.'posts' . ' LIMIT 20'); – Morts81 Jul 15 '18 at 7:17
  • The major issue in your query is that, you are passing $_POST data directly. Store the post id in a variable and use that variable inside your query. Like this: $post_id = $_POST['post_ID']; $myQuery = $wpdb->get_results('SELECT * FROM ' . $wpdb->prefix.'posts' . ' WHERE ID = '. $post_id ); – Atlas_Gondal Jul 15 '18 at 7:30
  • Wow, excellent. That works a treat!!! Thank you very much, that's really appreciated. – Morts81 Jul 15 '18 at 7:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.