Select2 Ajax不匹配结果

Question

My Select2 form is using ajax to load results from the database, but on the front end the the select2 search is returning all results and not the results specific to the search term. After doing some research I realized that the matcher has to be on the server side, but I can't seem to get it working. Here is my code

Code to query the database and return json data

<?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);

My jquery

<script>
 jQuery(function($) {
 $(document).ready(function() {
     function matchStart (term, text) {
  if (text.toUpperCase().indexOf(term.toUpperCase()) == 0) {
    return true;
  }

  return false;
}
$( "#regions1" ).select2({        
    ajax: {
        url: "/ajax/connect.php",
        dataType: 'json',
        delay: 250,
        data: function (params) {
            return {
                q: params.term // search term
            };
        },
        processResults: function (data) {
    return {
        results: $.map(data, function(obj) {
            return { id: obj.id, text: obj.text };

            })

        };

        }


},
  minimumInputLength: 3,
  width: "300px",
  placeholder: "Please Select Up To Four Additional Cities"

 });

    });
 });
</script>

and here is an example of my json results

[{"id":"Abanda, AL","text":"Abanda, AL"},{"id":"Abbeville, AL","text":"Abbeville, AL"},{"id":"Adak, AK","text":"Adak, AK"},

Answer 1

you have not mentioned the like condition in your sql statement.

your sql should be something like this

$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 AND search_column LIKE ?    <==== this line
    ) as T"
     );

$query->execute(array("%".$search."%"));