php/jQuery get primary key for selected value

Question

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

<?php
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);
    }
    mysqli_close($con);


    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" />
</form>
<script type="text/javascript">

    $('#faculty_id').autocomplete({
        source: function (request, response) {
            $.ajax({
                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) {
                        console.log(item);
                        //var code = item.split("|");
                        return {
                            label: item,
                            value: item,
                            data: item
                        }
                    }));
                }
            });
        },
        autoFocus: true,
        minLength: 1,

    });
</script>

and php insert query

<?php
if (isset($_POST)) {
    $faculty_id = $_POST['faculty_id'];
    try {
        $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 1

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);
        exit;
    }

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

   $('#faculty_id').autocomplete({
       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
               console.log(data.faculties);
               response($.map(data.faculties, function (faculty) {
                   console.log(faculty);
                   return {
                       label: faculty.FirstName + ' ' + faculty.LastName,
                       value: faculty.FacultyId
                   }
               }));
           });
       },
       autoFocus: true,
       minLength: 1,
   });
   

3. Lastly, when you insert

   // check if form was POSTed
   if ($_SERVER['REQUEST_METHOD'] === 'POST') {
       $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
           $sth->execute(array($faculty_id));
           // 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();
       }
   }