Tuesday, 28 August 2018

Autocomplete jQuery / PHP / MySQL does not return results

I'm using jQuery autocomplete for the first time. I've created a simple data base that contains several user names and a simple search box to practice the autocomplete function that I've just read about. I've verified that my mysql queries are working within mysql. I also verified that I'm connected to the database. I still can not get it to auto suggest terms. I think (though not 100% sure) that I've isolated my problem to the way that i'm using the $_REQUEST['term'] command. So two questions: Am I using this command correctly, and is there something wrong with how I'm doing auto complete? Note: I'm aware that I haven't sanitized the input. I'm just practicing the autocomplete stuff

Additional info " If I take out the $_request and just query all the users, it will return an autocomplete with all of database users. This happens regardless of what key I type. This would lead me to believe that it's receiving the ajax request.
here's what my console shows
here is my actest.php
<!doctype html>
<html lang="en">
<head>
  <meta charset="utf-8">
  <title>AutoComplete Test</title>
  <link rel="stylesheet" href="//code.jquery.com/ui/1.10.4/themes/smoothness/jquery-ui.css">
  <script src="//code.jquery.com/jquery-1.10.2.js"></script>
  <script src="//code.jquery.com/ui/1.10.4/jquery-ui.js"></script>
  <link rel="stylesheet" href="/resources/demos/style.css">

  <script> jQuery(document).ready(function($){
    $('#username').autocomplete(
        {source:'suggest_username.php',
        minLength:2
        });
    });
</script>

</form>
</head>
<body>

<form action="actest.php" method="POST">
    Enter Username:
    <input type="text" id="username" />

    <br />
    <input type="submit" value="Search" />

</body>
</html>
Here is my suggest_username.php
<?php

// here we set the database information
$SERVER = 'localhost';
$USER = 'user';
$PASS = 'password';
$DATABASE = 'database';

// we are instantiating the mysqli database object
$db = new mysqli($SERVER, $USER, $PASS, $DATABASE);

// error check
if($db->connect_errno > 0){

      // report an error
die('Unable to connect to database [' . $db->connect_error . ']');
}

$rs = 'SELECT * FROM users WHERE user LIKE "($_REQUEST['term']) .'%'"';

$result = $db->query($rs);

$data = array();

    while ($row = $result->fetch_assoc())
    {
        $data[] = array(
            'label' => $row['user'] ,
            'value' => $row['user']
        );
    }

// jQuery wants JSON data
echo json_encode($data);
flush();
I would suggest this line has a syntax error on it:
$rs = 'SELECT * FROM users WHERE user LIKE "($_REQUEST['term']) .'%'"';
looks like it should be something like this:
$rs = 'SELECT * FROM users WHERE user LIKE "(' . $_REQUEST['term'] . ')%"';
Not sure if you need the brackets either and you may want to make sure you make it safe so you can't get and sql injection attack

0 comments:

Post a Comment

Subscribe to: Post Comments (Atom)