0

I'm creating a web form on my WordPress-powered website, such that by entering a date and some upper/lower bounds into the provided fields, then submitting the form, the page will query a MySQL table, SELECT the rows which fit those criteria and dispense those values into a table on the same page. Unfortunately, I'm having a difficult time understanding what to do to get WordPress to perform this seemingly-simple task.

From the research I've done so far, three things are necessary to query a SQL table from within a WordPress:

  1. A form, written in PHP, which resides in the .php file for your page template
  2. A function which actually executes the SQL query, added to your theme's functions.php file
  3. A segment of Javascript code to tie the above two things together

The website page I've set up to test this form is "http://www.finstability.com/serch/". Here's what I've done so far:

First, the form code:

<form type="post" action="" role="form" id="MRISearchForm" class="MRISerch">
    <p style="font-size: 16pt;">Date: <input style="font-size: 16pt;" type="date" name="inDate" value="12/31/2016" width="200" height="32"></p>
    <p style="font-size: 16pt;">Minimum MRI: <input style="font-size: 16pt;" type="number" name="inMRILow" value="70" min="0" max="100" width="200" height="32"></p>
    <p style="font-size: 16pt;">Maximum MRI: <input style="font-size: 16pt;" type="number" name="inMRIHi" value="100"  min="0" max="100" width="200" height="32"></p>
    <p style="font-size: 16pt;">Min. Market Cap: <input style="font-size: 16pt;" type="number" name="inMCapLow" value="" width="200" height="32"></p>
    <p style="font-size: 16pt;">Max. Market Cap: <input style="font-size: 16pt;" type="number" name="inMCapHi" value="" width="200" height="32"></p>
    <input type="hidden" name="action" value="mriSearch"/>
    <input type="submit" name="submit" value="Search" style="font-size: 18pt;">
    <input type="reset" style="font-size: 18pt;">
</form> 
<div id="search_results"></div>

Second, the function in functions.php:

function mriSearch() {
    global $wpdb;
    echo "33333";   

    $inDate = $_POST['inDate'];
    $inMRILow = $_POST['inMRILow'];
    $inMRIHi = $_POST['inMRIHi'];
    $inMCapLow = $_POST['inMCapLow'];
    $inMCapHi = $_POST['inMCapHi'];

    $query = 'SELECT * FROM mri_sql_test WHERE Date = %s AND MRIScore >= %s AND MRIScore <= %s';

    $results = $wpdb->get_results( $wpdb->prepare($query, $inDate, $inMRILow, $inMRIHi) );
    echo $results;
    die();
}
add_action( 'wp_ajax_mriSearch', 'mriSearch' );    //If called from admin panel
add_action( 'wp_ajax_nopriv_mriSearch', 'mriSearch' );    //If called by non-user

Third, the javascript sequence to tie the above two together. This is the part I'm having the most trouble understanding, as I have -0- prior knowledge of Javascript:

<script type="text/javascript">
(function($){               
  // get our references
  var $form = $('form.MRISerch'),
      $inDate = $('#inDate'),
      $results = $('#search_results');

  // AJAX search call
  function do_search() {

    // grab the query value from the search field
    var search_text = $search_field.val();

    // do a POST ajax call
    $.ajax({
      type: "POST",
      url: '<?php echo admin_url('admin-ajax.php'); ?>',
      data: ({
        action: "mriSearch",
        search_text: search_text
      }),
      success: function (response){
        console.log(response);
        $results.html(response);
      }     
    });
  }

  // on submit, do the search but return false to stop page refresh
  $form.submit(function(e) {
    do_search();
    return false;
  });

})(jQuery);
</script>

Most of what I've been able to piece together so far I've learned from looking at similar examples, such as this one. If someone would kindly provide me with an explanation, ideally with example code I can copy and modify, for whatever I'm missing in order to complete this task, I would be most appreciative.

For reference, clicking the Submit button on the above-linked page currently just causes the page to reload and adds a bunch of hash to the http address, which implied to me that the SQL query in the second block of code is probably not being executed.

  • Are you sure the jquery selector works and your JS console has no errors? Also why do you return false on submit? Have you tried the standard e.preventDefault() in jQuery events? – Tom J Nowell Oct 26 '17 at 22:24
  • Thanks for the tip about using the console to check for errors. Judicious use of console logging let me see what the code was doing. – Thomas H Oct 27 '17 at 4:06
1

you should use e.preventDefault() instead of return false;

$form.submit(function(e) {
    e.preventDefault();
    do_search();
});
  • That helped, thanks! The other thing I had to do was move this snippet of code up to the top of the <script> section; I had it at the bottom. – Thomas H Oct 27 '17 at 3:51
  • The position of this is not relevant for your issue. It could be anywhere in your script after var $form = $('form.MRISerch'),, above or after the do_search function – 86Dev Oct 28 '17 at 0:25

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.