jQuery ui autocomplete没有搜索结果

Question

I am getting no search results from my mysql database using php. I've tried using mysqli, and PDO style from here.

html

<div id="tags_wrapper">
    <p>Tags</p>
    <input type="text" class="txtTag" placeholder="Start entering tag..">      </input>
</div>

jQuery: located in php page at root/Blog/panel.php, 'source' located in root/Blog/_class/tag_filler.php.

<script type="text/javascript">
    $(document).ready(function(){
        $('.txtTag').autocomplete(
        {
            source:'/_class/tag_filler.php',
            minLength: 2
        });
    });
</script>

php source [mysqli]: Brisktilities.php creates an instance of mysqli that I use below.

include_once 'BriskUtilities.php';

$util = new BriskUtilities();
$mysqli = $util->getMysqli();
if(isset($_GET['term'])) {
    $search = $_GET['term'];
    if($queryTags = $mysqli->query("SELECT * FROM Tag_T WHERE tValue LIKE %".$search."% ORDER BY tValue ASC")) {
        while($row = mysqli_fetch_array($queryTags)) {
            $results[] = array('id' => $row['tID'], 'label' => $row['tValue']);
        }
        echo json_encode($results);
    }
$mysqli->close();
}

php source [PDO]: Still no search results with PDO. My DB is brisk, my table is tag_t, and my connection is working properly. Any suggestions?

try {
  $conn = new PDO("mysql:host=$dbhost;dbname=$dbname", $dbuser, $dbpass) or die ("<p class='error'>Sorry, we were unable to connect to the database server.</p>");
}
catch(PDOException $e) {
    echo $e->getMessage();
}
$return_arr = array();

if ($conn)
{
    $ac_term = "%".$_GET['term']."%";
    $query = "SELECT * FROM tag_t where tValue like :term";
    $result = $conn->prepare($query);
    $result->bindValue(":term",$ac_term);
    $result->execute();

    /* Retrieve and store in array the results of the query.*/
    while ($row = $result->fetch(PDO::FETCH_ASSOC)) {
        $row_array['id'] = $row['id'];
        $row_array['value'] = $row['tValue'];

        array_push($return_arr,$row_array);
    }
}
$conn = null; 
echo json_encode($return_arr);

Update GET 'term' not found (error goes away when changing source to Blog/_class/tag_filler.php, but still no search results returned. I am using the database on the same page and it's fetching results for another table fine):

Failed to load resource: the server responded with a status of 404 (Not Found) [http] site.local/_class/tag_filler.php?term=an
Failed to load resource: the server responded with a status of 404 (Not Found) site.local/_class/tag_filler.php?term=and
GET site.local/_class/tag_filler.php?term=my 404 (Not Found) jquery-1.9.1.js:8526

Answer 1

When using like you need to enclose the value within quotes. e.g. tValue like '%my_value%'.

Note the use of single quotes in the following (I'm only showing the lines that need to be updated):

In your mysqli:

$mysqli->query("SELECT * FROM Tag_T WHERE tValue LIKE '%".$search."%' ORDER BY tValue ASC")

and in your PDO:

$ac_term = "'%".$_GET['term']."%'";

Update:

If you've already tried this then please add some error handling so that you can narrow down the source of the problem.

For mysqli (http://php.net/manual/en/mysqli.error.php):

$queryTags = $mysqli->query("SELECT * FROM Tag_T WHERE tValue LIKE %".$search."% ORDER BY tValue ASC");
if (!$queryTags) {
    printf("Error: %s", mysqli_error($mysqli));
}

while($row = mysqli_fetch_array($queryTags)) {
    $results[] = array('id' => $row['tID'], 'label' => $row['tValue']);
}
echo json_encode($results);

For PDO ( http://php.net/manual/en/pdo.error-handling.php):

try {
  $conn = new PDO("mysql:host=$dbhost;dbname=$dbname", $dbuser, $dbpass);
  $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
}
catch(PDOException $e) {
    echo $e->getMessage();
    die();
}