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

Question

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.

Answer 1

When preparing a statement in MySQLi, you need to use bind_result() to bind the resulting columns into variables, which you'll use instead of the $row array you'd use if you weren't preparing the statement (like you'd use mysqli_fetch_*() functions).

Its important that the number of variables you bind match the number of selected columns. This is why I have modified your query, to match that.

Then you need to fetch() the results. With those changes implemented, your query should look something like this

$stmt = $conn->prepare("SELECT contractID, 
                               loanpurpose, 
                               applicant1_firstname, 
                               applicant1_middlename, 
                               applicant1_surname, 
                               applicant1_dateofbirth 
                        FROM contract 
                        WHERE contractID=? 
                          AND applicant1_firstname=? 
                          AND applicant1_surname=?");
$stmt->bind_param("iss", $contractno, $firstname, $lastname);
$stmt->execute();
$stmt->bind_result($contractID, $loanpurpose, $applicant1_firstname, $applicant1_middlename, $applicant1_surname, $applicant1_dateofbirth);
if ($stmt->errno) {
    die("Query failed to execute: " . $stmt->error);
}
if ($stmt->fetch()) {
    echo json_encode(array("contractID" => $contractID, 
                           "loanpurpose" => $loanpurpose,  
                           "applicant1_firstname" => $applicant1_firstname,  
                           "applicant1_middlename" => $applicant1_middlename,  
                           "applicant1_surname" => $applicant1_surname,  
                           "applicant1_dateofbirth" => $applicant1_dateofbirth));
} else {
    echo "No matching rows returned.";
}
$stmt->close();

And no, you should not use mysqli_real_escape_string() when using a prepared statement - that'd ruin the data, by escaping quotes which does not need to be escaped (IF I use mysqli prepared statements do i need to escape).

Instead of using $stmt->num_rows == 1, you can check if $stmt->fetch() returned true - because you only expect one row, you don't need to check the amount - just that something was fetched. As per the manual, $stmt->fetch() returns true for successful queries, null if no rows was returned.
The reason why this approach might be better, is because you avoid having to call $stmt->store_result(); before you can use $stmt->num_rows;. This is more efficient in terms of resources, but also saves you some typing ;-)

Also note that echo "Updated {$stmt->affected_rows} rows"; wouldn't make sense to use here, as you're running a SELECT query, not an UPDATE one. If you want to see the number of rows returned, you can use $stmt->num_rows; instead.

• http://php.net/mysqli-stmt.bind-result
• http://php.net/mysqli-stmt.fetch
• http://php.net/mysqli-stmt.num-rows