CODEWITHSUNDEEP
javascriptphpjqueryjquery-ui-autocomplete
Carlos Santiago
Carlos Santiago

Reputation: 73

Autocomplete outputting two fields from DB

I have the following code for an autocomplete input where the user can input the nome of the employee instead of the ID:

if (isset($_GET['term'])){
    $return_arr = array();

    try {
        $conn = new PDO("mysql:host=".DB_SERVER.";dbname=".DB_NAME, DB_USER, DB_PASSWORD);
        $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

        $stmt = $conn->prepare('SELECT * FROM colaboradores WHERE nome LIKE :term');
        $stmt->execute(array('term' => '%'.$_GET['term'].'%'));

        while($row = $stmt->fetch()) {
            $return_arr[] =  $row['nome'];
        }



    } catch(PDOException $e) {
        echo 'ERROR: ' . $e->getMessage();
    }


/* Toss back results as json encoded array. */
echo json_encode($return_arr);

The output now it's like this:
enter image description here

And I need to output the name with the ID but only send the nome information to the DB

UPDATE Here's the scripts:

<script type="text/javascript" src="http://code.jquery.com/jquery-1.9.1.min.js"></script>
<script type="text/javascript" src="http://code.jquery.com/ui/1.10.1/jquery-ui.min.js"></script>  
<link rel="stylesheet" href="css/autocomplete.css" />       
<script type="text/javascript">
    $(function() {  
        //autocomplete
        $(".auto").autocomplete({
            source: "search.php",
            minLength: 1,
            messages: {
                noResults: '',
                results: function() {}
        }
    });                
});
</script>

Upvotes: 2

Views: 49

Answers (1)

Peter
Peter

Reputation: 1725

I believe you can return a label and a value to the autocomplete:

In your PHP:

while($row = $stmt->fetch()) {
    // add the ID inside () chars
    $return_arr[] =  [
        'label' => $row['nome'],
        'value' => $row['id']
    ]; 
}

In your JS:

$(function() {
    $(".auto").autocomplete({
        source: "search.php",
        focus: function(event, ui) {
            event.preventDefault();
            // manually update the textbox
            $(this).val(ui.item.label);
        },
        select: function(event, ui) {
            event.preventDefault();
            // manually update the textbox and readonly field
            $(this).val(ui.item.label);
            $("#other-input").val(ui.item.value);
        }
    });
});

OR: You could try putting the ID in some delimiters and then strip the ID out before sending to the database. Something like this:

if (isset($_GET['term'])){

    // strip out the (ID) before searching in DB
    $nomeOnly = preg_replace('/ \(.*\)/', '', $_GET['term']);

    $return_arr = array();

    try {
        $conn = new PDO("mysql:host=".DB_SERVER.";dbname=".DB_NAME, DB_USER, DB_PASSWORD);
        $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

        $stmt = $conn->prepare('SELECT * FROM colaboradores WHERE nome LIKE :term');
        $stmt->execute(array('term' => '%'.$nomeOnly.'%'));

        while($row = $stmt->fetch()) {
            // add the ID inside () chars
            $return_arr[] =  $row['nome'] . ' (' . $row['id'] . ')'; 
        }

    } catch(PDOException $e) {
        echo 'ERROR: ' . $e->getMessage();
    }

    /* Toss back results as json encoded array. */
    echo json_encode($return_arr);
}

Upvotes: 1

Related Questions