codelearner codelearner - 1 year ago 77
jQuery Question

php/jQuery get primary key for selected value

I am trying to get key(primary key) for selected value in my form, so I can add key into joining table. I change my form to autocomplete from drop down list. but do not how to do map with jquery.

This is my php for autocomplete

if(isset($_POST['type']) && $_POST['type'] == 'faculty_id' ){

$type = $_POST['type'];
$name = $_POST['name_startsWith'];
$nameID = $_POST['nameID'];

$query = "SELECT FirstName, LastName, FacultyId FROM Test.Faculty where UPPER(FirstName) LIKE '".strtoupper($name)."%'";
$result = mysqli_query($con, $query);
$data = array();
while ($row = mysqli_fetch_assoc($result)) {
$name = $row['FirstName'].' '.$row['LastName'];
$nameID = $row['FacultyId'];
array_push($data, $name);

echo json_encode($data);exit;

this is form and jQuery page

<form action="Form.php" method="post">
<input type='text' id="faculty_id" placeholder="Instructor" name="faculty_id" value='' />

<input type="submit" value="submit" name="submit" />
<script type="text/javascript">

source: function( request, response ) {
url: 'Form.php',
dataType: "json",
method: 'post',
data: {
name_startsWith: request.term,
nameID: request.term,
type: 'faculty_id'
success: function( data ) {
response( $.map( data, function( item ) {
//var code = item.split("|");
return {
label: item,
value: item,
data : item
autoFocus: true,
minLength: 1,


and php insert query

$faculty_id = $_POST['faculty_id'];

$stat = $db->prepare("Insert into ATCTest.Schedule
(Faculty )
VALUE (':faculty_id' )");
$stat->bindParam(":faculty_id", $faculty_id);

if ($stat->execute()){
echo "<h5>Faculty-js: ".$faculty_id."</h5>";
} else {
echo "Problem!!!";

}catch(PDOException $e){
echo $e->getMessage();

Answer Source

(1) Stay consistent. Choose either PDO or MySQLi. Not both. Your autocomplete script uses MySQLi, but then in your insert script, you use PDO. Pick one and stick with it.

I'll use PDO as I find it much easier to use than MySQLi.

(2) Use the appropriate request methods. If you are getting something, use GET not POST. If you are adding or updating, use POST.

Let's rewrite your autocomplete script to use PDO:

if (isset($_GET['type']) && $_GET['type'] == 'faculty_id') {
    // this will hold your response that gets sent back
    $data = null;
    $name = trim($_GET['name']);

    try {
        // because you are passed untrusted data, use prepared statement 
        $sth = $db->prepare("
            SELECT FirstName, LastName, FacultyId 
            FROM Test.Faculty 
            WHERE UPPER(FirstName) LIKE UPPER(?)
        $sth->execute(array($name . '%'));
        // set the results (array of objects) as your JSON response
        $data['faculties'] = $sth->fetchAll(PDO::FETCH_OBJ);
    } catch(PDOException $e){
        echo $e->getMessage();
    // send the results i.e. response
    header('Content-Type: application/json');
    echo json_encode($data);

(2) I've never used the autocomplete plugin before but I'll take a crack at it based on other answers I've seen.

    source: function (request, response) {
        // short syntax for .ajax() using GET method that expects a JSON response
        $.getJSON('Form.php', { type: 'faculty_id', name: request.term }, function (data) {
            // data.faculties (your AJAX script's response) should now be an array of objects
            response($.map(data.faculties, function (faculty) {
                return {
                    label: faculty.FirstName + ' ' + faculty.LastName,
                    value: faculty.FacultyId
    autoFocus: true,
    minLength: 1,

(3) Lastly, when you insert

// check if form was POSTed
    $faculty_id = $_POST['faculty_id'];
    try {
        // VALUES not VALUE
        // don't wrap your placeholders with quotes in your prepared statement
        // simplified
        $sth = $db->prepare("INSERT INTO ATCTest.Schedule(Faculty) VALUES(?)");
        // simplified way to bind parameters
        // use rowCount() not execute() to determine if the operation was successful or not
        if ($sth->rowCount()){
            echo "<h5>Faculty-js: $faculty_id</h5>";
        } else {
            echo "Problem!!!";
    } catch (PDOException $e) {
        echo $e->getMessage();
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download