How do I get the row data from a Prepared statement and then use it with AJAX() function

Viewed 1365

With Mysqli my code is working fine, but when I try to use a prepared statement, for more security, I can't get the values back to my AJAX() function.

this is my js code

function validateCoSigner(){
var contractNo = $('#cosigner_reference_number').val();
var firstName = $('#cosigner_firstname').val();
var lastName = $('#cosigner_lastname').val();
$.ajax({
    type: 'POST',
    url: 'php/retrieve-applicant1-details.php',
    // dataType: 'json',
    data: {
        'contractNo': contractNo,
        'firstName' : firstName,
        'lastName'  : lastName
    }, 
    success: function(json) {
        showCoSigner();
        console.log(json);
        $('#contractID').val(json.contractID);
        $('#loanpurpose').val(json.loanpurpose);
        $('#applicant1_firstname').val(json.applicant1_firstname);
        $('#applicant1_middlename').val(json.applicant1_middlename);        
        $('#applicant1_surname').val(json.applicant1_surname);
        $('#applicant1_dateofbirth').val(json.applicant1_dateofbirth);
    }, 
    error: function() {
        alert ('error2');

    }
});

}

This is the retrieve-applicant1-details.php file with the mysqli functions that works:

<?php
header('Content-Type: application/json');
$conn = mysqli_connect("localhost", "root","","xxx");

$contractno = $_POST['contractNo'];
$firstname = $_POST['firstName'];
$lastname = $_POST['lastName'];

$sql = "SELECT * FROM contract WHERE contractID='$contractno' AND applicant1_firstname='$firstname' AND applicant1_surname='$lastname'";
$query = mysqli_query($conn,$sql);
$num_row = mysqli_num_rows($query);
$row=mysqli_fetch_assoc($query);
if( $num_row == 1 ) {
    $obj = [
        'contractID'                =>($row['contractID']), 
        'loanpurpose'               =>($row['loanpurpose']), 
        'applicant1_firstname'      =>($row['applicant1_firstname']), 
        'applicant1_middlename'     =>($row['applicant1_middlename']), 
        'applicant1_surname'        =>($row['applicant1_surname']), 
        'applicant1_dateofbirth'    =>($row['applicant1_dateofbirth']), 
    ];
    }
else {
    echo 'The reference number, firstname and lastname does not match';
    exit;
}

echo json_encode($obj);
?>

If I use prepared statement as below, I can't figure out how to properly fetch the data so that I can get the values back to my Ajax function so that it can be displayed in my form.

<?php
header('Content-Type: application/json');
$conn = mysqli_connect("localhost", "root","","xxx");

$contractno = $_POST['contractNo'];  //not sure if I still need to use mysqli_real_escape_string here.
$firstname = $_POST['firstName'];
$lastname = $_POST['lastName'];

// prepare statement        
$stmt = $conn->prepare("SELECT * FROM contract WHERE contractID=? AND applicant1_firstname=? AND applicant1_surname=?");
// bind
$stmt->bind_param("iss", $contractno, $firstname, $lastname);
// execute
$stmt->execute();
if ($stmt->errno) {
    echo "FAILURE!!! " . $stmt->error;
}
else echo "Updated {$stmt->affected_rows} rows";

$row = $stmt->fetchObject();
$obj = (
    'contractID'                =>($row['contractID']), 
    'loanpurpose'               =>($row['loanpurpose']), 
    'applicant1_firstname'      =>($row['applicant1_firstname']), 
    'applicant1_middlename'     =>($row['applicant1_middlename']), 
    'applicant1_surname'        =>($row['applicant1_surname']), 
    'applicant1_dateofbirth'    =>($row['applicant1_dateofbirth']), 
);

echo json_encode($obj);
?>

I'm a Newbie. Any recommendations would be much appreciated.

1 Answers
Related