PHP MySQL autocomplete

Question

I have an auto complete search field which as the user types a name, th results are shown in the dropdown.

This all works fine, and shows the data as it should.

I am waiting to make each result a link however, so when the results are shown the user can click on the correct name and it will take them to their profile.

See script below:

<input type='text' id=employees class='form-control' size="80" placeholder="Search Employees by first or last name">

search.php

$searchTerm = $_GET['term'];

    $sql = mysql_query ("SELECT name_first, employee_id, unique_id, name_last FROM hr_employees WHERE name_first LIKE '{$searchTerm}%' OR name_first LIKE '{$searchTerm}%' OR employee_id LIKE '{$searchTerm}%'");
    $array = array();
    while ($row = mysql_fetch_array($sql)) {
        $array[] = array (

            'value' => $row['name_first'].' '.$row['name_last'].' ('.$row['employee_id'].')',

        );
    }
    //RETURN JSON ARRAY
    echo json_encode ($array);

Upon selecting the correct user, I would like the user to de directed to page.php?id=$employee_id

Is this possible?

JavaScript

<script src="//code.jquery.com/jquery-1.10.2.js"></script>
<script src="//code.jquery.com/ui/1.11.4/jquery-ui.js"></script>

JavaScript

<script>
$(function() {
$( "#employees" ).autocomplete({
source: 'search.php'
});
});
</script>

Solution

As requested:

PHP:

$pdo        = new \PDO('mysql:host=localhost;dbname=test', $user, $pass);
$searchTerm = $_GET['term'];

$stmt = $pdo->prepare("SELECT name_first, employee_id, unique_id, name_last FROM hr_employees WHERE name_first LIKE :search OR name_first LIKE :search OR employee_id LIKE :search");
$stmt->execute([':search' => $searchTerm.'%']);

$array = [];
while (false !== ($row = $stmt->fetch())) {
    $array[] = [
        'value' => $row['name_first'].' '.$row['name_last'].' ('.$row['employee_id'].')',
        'id'    => $row['id'],
    ];
}

echo json_encode($array);

JavaScript:

<script>
    $( "#employees" ).autocomplete({
        source: 'search.php',
        select: function( event, ui ) {
          window.location.href = 'page.php?id='+ui.item.id;
        }
    });
</script>

Fiddle with console.log instead of location change: https://jsfiddle.net/dLe4a83x/

Comments

[Shane]

Thanks, I have added JavaScript to the question

[colburton]

Is that jQuery?

[Shane]

Sorry yes its JQuery. Updated Question

[Shane]

Thanks for the update, however it doesnt seem to be working, nothing is now showing in the drop down

[colburton]

Did you notice, that i've changed back to 'value', since that is what jQuery is expecting here.

[Shane]

Yes, this is now currently in my script and not working

[colburton]

Ok, I've added a fiddle to figure out the problem. Now it works.

[Admin]

Try to this code working fine Click hare