CODEWITHSUNDEEP
phpmysqlpdo
Acoustic Jack
Acoustic Jack

Reputation: 117

PDO get id and name

I am using PDO to return a list of contact names for an autoselect input box. The PDO .....

$stmt = $db->prepare("SELECT ContactName, ContactID FROM `tblContacts` WHERE ContactName LIKE ? ORDER BY ContactName");

$keyword = '%' . $keyword . '%';
$stmt->bindParam(1, $keyword, PDO::PARAM_STR, 10);
$isQueryOk = $stmt->execute();
$results = array();

if ($isQueryOk) {
  $results = $stmt->fetchAll(PDO::FETCH_COLUMN);
} else {
  trigger_error('Error executing statement.', E_USER_ERROR);
}

The autoselect .....

$("#keyword").keyup(function() {
    var keyword = $("#keyword").val();
    if (keyword.length >= MIN_LENGTH) {

        $.get( "auto-complete.php", { keyword: keyword } )
        .done(function( data ) {
            $('#results').html('');
            var results = jQuery.parseJSON(data);
            $(results).each(function(key, value) {
                $('#results').append('<div class="item">' + value + '</div>');
            })

            $('.item').click(function() {
                var text = $(this).html();
                $('#keyword').val(text);
            })

        });
    } else {
        $('#results').html('');
    }
});

I need to get the ContactID when a name is selected. Looked on here and google but new to PDO and a bit confused

Upvotes: 1

Views: 960

Answers (1)

mjoschko
mjoschko

Reputation: 564

While fetching the result you are restricting it to the first column with the fetch style parameter PDO::FETCH_COLUMN.(see example 2)

Try change this to

$results = $stmt->fetchAll();

In you snippet you don't return the result as a JSON object.

Try to add

echo json_encode($results);

Afterwards you should be able to access the JSON response within your jQuery script. Try to change it this way

$('#results').append('<div class="item" data-id="' + value.ContactID + '">' + value.ContactName + '</div>');

Complete Example

HTML

<input type="text" name="keyword" id="keyword" />
<div id="results"></div>
<script>
    var min_length = 1;
    $("#keyword").keyup(function () {
        var keyword = $("#keyword").val();
        if (keyword.length >= min_length) {
            $.get("auto-complete.php", {keyword: keyword}).done(function (data) {
                        $('#results').html('');
                        var results = jQuery.parseJSON(data);
                        console.log(data);
                        $(results).each(function (key, value) {
                            $('#results').append('<div class="item" data-id="' + value.ContactID  + '">' + value.ContactName + '</div>');
                        })
                        $('.item').click(function () {
                            var text = $(this).html();
                            console.log($(this).attr("data-id"));
                            $('#keyword').val(text);
                        })
                    });
        } else {
            $('#results').html('');
        }
    });
</script>

PHP

// get the keyword
$keyword = filter_input(INPUT_GET, 'keyword', FILTER_SANITIZE_SPECIAL_CHARS);
// instantiate PDO with DNS, Username, Password
$db = new PDO();
$stmt = $db->prepare("SELECT ContactName, ContactID FROM `tblContacts` WHERE ContactName LIKE ? ORDER BY ContactName");
$keyword = '%' . $keyword . '%';
$stmt->bindParam(1, $keyword, PDO::PARAM_STR, 10);
$isQueryOk = $stmt->execute();
$results = array();

if ($isQueryOk) {
  $results = $stmt->fetchAll(PDO::FETCH_ASSOC);
} else {
  trigger_error('Error executing statement.', E_USER_ERROR);
}

echo json_encode($results);

Upvotes: 1

Related Questions