0

I am having a weird issue in regards to getting data from a DB Table via AJAX.

My outcome is to get the employees from the DB and into a dropdown list, but i haven't been able to get it to work. So here is my code and what i have done so far and the current results.

  1. I have registered the action and function in WP:

    add_action('wp_ajax_get_users_dropdown', array( $this, 'get_users_dropdown'));
    add_action('wp_ajax_nopriv_get_users_dropdown', array( $this, 'get_users_dropdown'));
    
  2. I created the PHP code to retrieve the employees from custom DB Table:

    public function get_users_dropdown()
    {
     $mydb = new wpdb('*****','*****','******','*****'); //External DB Auth
     $data = $mydb->get_results("SELECT id, name FROM employee WHERE email IS NOT NULL");
    
     echo wp_json_encode($data);
     die();
    }
    
  3. Then, I created my JS function to call the PHP code which gets called in the $(document).ready():

    function populate_users_dropdown()
    {
     $.ajax({
         type: "POST",
         url : MyAjax.ajaxurl, // Including ajax file
         data: { 
             action: 'get_users_dropdown',
             security: MyAjax.security
         },
         success: function(response) { // Show returned data using the function.
             //console.log("> response: ", response );
             // send data to function
             display_users_dropdown(response);
         },
         error: function() {
            // console.log("FAILED...");
         }
     });
    }
    
  4. From there I pass the successful response from the AJAX call to a function to handle the outputted data.

    function display_users_dropdown(responseData)
    {
     console.log ( "++ display_users_dropdown - ", responseData.length, responseData[0].id, responseData[0].name, responseData );
     }
    

The output from the console is:

   ++ display_users_dropdown: 1309 undefined undefined 
        [{"id":"4","name":"John Game"},
            {"id":"5","name":"Cyan Bergan"},
            {"id":"7","name":"Stephen Rogers"},
       ...]

But what i am getting in the console.log is not right, if i log the "responseData" only I get 36 items in the array, but if i use the above, id and name are undefined, length is 1309 instead of 36 (DB table only has 47 entries) and the array shows up fine.

I am not sure why this i doing this and would like a second option.

4
  • Why are you doing that $mydb = new wpdb - you could've just replace that with global $wpdb; and use $wpdb->get_results()? And if you want just (at most) 36 results, then use something like LIMIT 36 in your SQL query. Also, have you considered using the REST API - it's not required, but it seems to me as a better choice since you're outputting a JSON data. – Sally CJ Feb 15 at 2:32
  • This is an internal website. And the DB is not a Core WP table it's getting the data from a seperate DB. And this method works fine for all other instances appart from this one. There are 47 employees in the DB with 36 have emails. – Jimmy Feb 15 at 3:40
  • Ok then, but if your table is in another database, but uses the same user and password as the ones for your WordPress database, you could use the select() method for selecting your custom database using the current database connection. – Sally CJ Feb 15 at 4:29
  • And actually (as you've figured it out), the responseData in your code is a string (it's the response text/body returned by the server) and not an array. If you want it be automatically converted to an array, use wp_send_json() or add dataType: 'json' in your $.ajax() args, although yes, JSON.parse() also works for manually converting the data to an array. – Sally CJ Feb 15 at 4:30
0

I found the solution. data = JSON.parse(data); was needed on the JS side for receiving the data.

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.