MySQL query in Wordpress with AJAX

Question

I have a search form which searches the DB and returns back some data. I have managed to use Wordpress wpdb to connect and use select statement on the table and retrieve data. Everything works fine..But..I have page refresh issue. I want to add AJAX code in order to get my data without refreshing. So, I have followed along with this, this and tutorials. But,I'm facing some problems with them. So, here's what I got:

My JS code:

var j$ = jQuery;
var search_text = j$(".cv__text").val();
j$.post({
    url: "<?php echo admin_url('admin-ajax.php'); ?>",
    data: ({
        action: "search_cv", search_text: search_text
    }),
    success: function (response){
        console.log(response);
        j$("#search_results").html(response);
    }
}); 

My functions.php:

function search_cv(){
    $search_text = ucfirst($_POST["search_text"]);

    //Creating New DB Connection

    $database_name = "employee_cv";

    $mydb = new wpdb(DB_USER, DB_PASSWORD, $database_name, DB_HOST);
    $mydb -> show_errors();

    if ($_POST["search_text"] != null){

        $result = $mydb -> get_results(
            $mydb -> prepare(
                'SELECT * FROM employee 
                WHERE Experience = %s',
                $search_text
            ) );

    }

    foreach ($result as $employee){
        //Here I just echo html blocks (divs, paragraphs etc.)                          
    }

    die();
}

add_action( 'wp_ajax_search_cv', 'search_cv' );
add_action( 'wp_ajax_nopriv_search_cv', 'search_cv' );

My HTML:

<form class="searchCV_form" role="form" action="">
    <div>
        <input type="text" id="search_text" name="search_text" class="cv__text">
        <span class="input-group-btn">
            <button type="submit" class="btn btn-default btn-primary cv__button search--form-btn">SUBMIT</button>
        </span>
    </div>
</form>
<div id="search_results"></div>

So, after all this changes, I still get the refresh on page and also, my query is not working at all. I'm not sure what I'm doing wrong here. I've used AJAX before on my projects but never with Wordpress and php.

EDIT

Apparently either I'm doing something wrong or the question is wrong. Can someone just point me on how to use AJAX with wpdb requests to MySQL? On own experience? preferably with some details? all the tutorials i found seems to fail me here.

Answer 1

I would need to see your trigger function but it sounds like you just need to add event.preventdefault to the submit. This will block the page load while still executing AJAX.

$("form.searchCV_form").submit(function(event){
    event.preventDefault();

    alert( "We're not going anywhere now..." );
    // j$.post...
}); 

or

$("form.searchCV_form").submit(function(e){
    alert( "We're not going anywhere now..." );
    // j$.post...

    return false;
});

More info at event.preventDefault() vs. return false.

Here are some mods to get it working how you expected -- sans actual searching.

FORM

<form class="searchCV_form" role="form" action="">
     <div>
         <input type="text" id="search_text" name="search_text" class="cv__text">
         <span class="input-group-btn">
             <button type="submit" class="btn btn-default btn-primary cv__button search--form-btn">SUBMIT</button>
         </span>
     </div>
 </form>
 <div id="search_results"></div>

JQUERY

<script>
// wrap everything in a closure
(function($){

  // get our references
  var $form = $('form.searchCV_form'),
      $search_field = $('#search_text'),
      $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: "search_cv",
        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>

WP AJAX

function search_cv()
{
    // get the search query
    $search_text = ucfirst($_POST["search_text"]);

    // clean it up
    $search_text = sanitize_text_field( $search_text);

    // ... do stuff with it

    // output the HTML which will be consumed by $.html()
    ?><div>You searched for <?php echo $search_text; ?> and we found... </div><?php

    // stop doing stuff
    die();
}

add_action( 'wp_ajax_search_cv', 'search_cv' );
add_action( 'wp_ajax_nopriv_search_cv', 'search_cv' );

Answer 2

Your Javascript file has PHP in it:

url: "<?php echo admin_url('admin-ajax.php'); ?>",

Javascript files are not parsed by the PHP interpretter, so the URL you providing is literally <?php echo admin_url('admin-ajax.php'); ?>. You need to create a Javascript variable that you can access. What you want to do is this (taken from the wp_localize_script() codex page, with modifications:

// Register the script
wp_register_script( 'some_handle', 'path/to/myscript.js' );

// Localize the script with new data
$translation_array = array(
    'myajax_url' => admin_url('admin-ajax.php'),
);
wp_localize_script( 'some_handle', 'object_name', $translation_array );

// Enqueued script with localized data.
wp_enqueue_script( 'some_handle' );

Then in your Javascript:

url: some_handle.myajax_url,

Answer 3

// Register the script - located in functions.php (usually)
wp_register_script( 'some_handle', 'path/to/myscript.js' );

//In the file you need the ajax results

// Enqueued script
wp_enqueue_script( 'some_handle' );

// Localize the script with new data
wp_localize_script( 'some_handle', 'object_name', array('ajax_url' => admin_url( 'admin-ajax.php' ) );

// the ajax call in the js file
jQuery.ajax({
    url : object_name.ajax_url,
    type : 'post',
    data : {
           action:'search_cv', 
           search_text: search_text
    }
    success : function( response ) {
         response = jQuery.parseJSON(response);
         //use the response variable to print what you need on the webpage. Basically instead of running the foreach in the php function run it here with js
   }
 });

In the php function (which should be in functions.php) remove the foreach and just echo the result

echo json_encode($results);