Select2 ajax not showing results

Question

I am using select2 and ajax to query my database for terms under a certain taxonomy, but when I search the search boxes just hangs on "searching" without retrieving any results.

This is my html

<select multiple="" name="regions1[]" id="regions1" class="job-manager-multiselect select2-hidden-accessible" required="" tabindex="-1" aria-hidden="true"></select>

My jquery:

<script>
jQuery(function($) {
$(document).ready(function() {
$( "#regions1" ).select2({        
ajax: {
    url: "/ajax/connect.php",
    dataType: 'json',
    delay: 250,
    data: function (params) {
        return {
            q: params.term // search term
        };
    },
    processResults: function (data) {
        // parse the results into the format expected by Select2.
        // since we are using custom formatting functions we do not need to
        // alter the remote JSON data
        return {
            results: data
        };
    },
    cache: true
},
minimumInputLength: 2
  });
  });
   });
 </script>

and my php code to query the database, I am looking to get all the term names under the taxonomy "job_listing_region"

<?php

  $servername = "localhost";
  $username = "myusername";
  $password = "mypassword";

   try {
$conn = new PDO("mysql:host=$servername;dbname=mydatabase", $username, $password);
// set the PDO error mode to exception
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
   }
    catch(PDOException $e)
   {
   echo "Connection failed: " . $e->getMessage();
   }



  // strip tags may not be the best method for your project to apply extra 
   layer of security but fits needs for this tutorial 
   $search = strip_tags(trim($_GET['q'])); 

 // Do Prepared Query
   $query = $conn->prepare("
   SELECT * FROM (
    SELECT wp_terms.name
   FROM wp_terms
   JOIN wp_term_taxonomy
    ON wp_term_taxonomy.term_id = wp_terms.term_id
    WHERE taxonomy = 'job_listing_region'
    AND count = 0
    ) as T"
     );

    // Add a wildcard search to the search variable
     $query->execute(array(':search'=>"%".$search."%"));


   // Do a quick fetchall on the results
    $list = $query->fetchall(PDO::FETCH_ASSOC);

   // Make sure we have a result
   if(count($list) > 0){
    foreach ($list as $key => $value) {
    $data[] = array('id' => $value['name'], 'text' => $value['name']);              
   } 
    } else {
   $data[] = array('id' => '0', 'text' => 'No Products Found');
   }


// return the result in json
echo json_encode($data);

And as you can see, I am retrieving my data, but the search just hangs.

Thanks in advance.

Nombril

try changing this: processResults to: success – Noctambulous

Are u using indexes? – Ciaracibber

Run yur query manually on phpmyadmin is it working fast or nt – Ciaracibber

@DerekPollard I tried that, it did not work. – Nombril

@devpro, Yes the query is working when I run it through phpmyadmin – Nombril

Explain your query and chk how many rows fetched – Ciaracibber

And what is the reason of using sub query? – Ciaracibber

You need to iterate through the data and then use javascript to append it to the element you wish. – Noctambulous

I am attempting to retrieve all the names of the terms in wp_terms the under the taxonomy "job_listing_region". There are around 29,000 rows fetched. – Nombril

@DerekPollard would you mind explaining how to do it? I am an amateur at this. – Nombril

I explained below. See my answer. – Noctambulous

And what about count column is it indexd? – Ciaracibber

Answer 1

Found the solution here How to load JSON data to use it with select2 plugin

Needed to recreate my results like this

processResults: function (data) {
return {
    results: $.map(data, function(obj) {
        return { id: obj.id, text: obj.text };
    })
};
}

Nombril

Answer 2

So you need to change processResults to success and put the following into that function:

for(i=0;1<data.length;++i){
   var currentObject = data[i];
   var id = currentObject.id;
   var text = currentObject.text;
   //do what you need to here (Put things in a div, etc)
}

And from there, you can do something like this:

document.getElementById("search").innerHTML = document.getElementById("search").innerHTML+"<br />"+id+text;

Noctambulous

Thanks! Just to clarify, the "search" should be replaced by the id of the select correct? – Nombril

yes, I'd say so. Or you could add a div inside of the select and place the contents in there. – Noctambulous

When i do that I get a "TypeError: document.getElementById(...) is null" , this is my altered jquery script with your suggestions codepaste.net/j5xjck . Thank you again for your help! – Nombril

document.getElementById("#regions").innerHTML = document.getElementById("#regions").innerHTML+"<br />"+id+text; needs to go where this is: //do what you need to here (Put things in a div, etc) and make sure you have a div that is named "regions", try that and tell me what you get – Noctambulous

Ok No more error, and i created a div named regions inside my select, but the results are still not showing. It only shows "searching" although the results have been returned. – Nombril

Can you put an alert for the ID for me and see if it's echoing out – Noctambulous

I created an alert, and no surprise, it is no echoing out. If i try to create a div named regions inside the select named "regions1" it doesn't show up, and creating the div outside does not help. Thank you again for helping a newbie out – Nombril

Sorry I couldn't help you more! If you follow the way I did it above, it should work. – Noctambulous