No JSON response from remote web server to HTML web form

Question

I'm an amateur programmer so please be nice:-)

I've been trying (for hours and hours!) to develop a web form which on submission of one form item (eg employee number) it retrieves data from a remote SQL web server and populates the other fields in the web page (eg name, address, telephone number etc). All pretty usual stuff and I've been attempting to adopt and adapt numerous examples from StackOverflow.

The approach is HTML Form to collect EmployeeID, javascript getJSON to request the data via a remote server php script, which connects to the SQL database, queries the database creates the return array, and json_encodes it back to the web form.

The consistent problem seems to be that data is not getting passed back to the Web Form, and therefore doesn't populate the other fields.

So I searched and found a tutorial which had a downloadable "working" example code (acknowledging https://www.electrictoolbox.com/json-data-jquery-php-mysql/). This example populates the Variety select options when a fruit is selected and is very simple in its coding approach. I am using this to get the basic functionality working, but not even this simple code wants to play nicely:-) I'm sure it's something simple but I just can't seem to find the issue.

I did change the connection to the SQL database from PDO to Mysqli as I couldn't get PDO to work at all with the SQL Server.

What I'm expecting is that the form will display the items under the Variety dropdown box extracted from the database to match the type of fruit.

Here's the fruit.html file with the Javascript:

<!doctype html>
<html>

<head>
<title>Untitled 1</title>
</head>

<script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/1.12.4/jquery.min.js"></script>

<script type="text/javascript">

function populateFruitVariety() {

    $.getJSON('https://mysite.com.au/Secure/fruit-varieties.php', {fruitName:$('#fruitName').val()}, function(data) {
        var select = $('#fruitVariety');
        var options = select.prop('options');
        $('option', select).remove();

        $.each(data, function(index, array) {
            options[options.length] = new Option(array['variety']);
        });

    });
}

$(document).ready(function() {
    populateFruitVariety();
    $('#fruitName').change(function() {
        populateFruitVariety();
    });
});

</script>

<body>

    <form name="testfruit" onsubmit="populateFruitVariety();">

    Fruit: <select id="fruitName" name="name">
    <option>Apple</option>
    <option>Banana</option>
    <option>Orange</option>
    <option>Pear</option>
    </select> Variety: <select id="fruitVariety" name="variety"></select>
    <input type="submit" name="submit" value="Search">
</form>


</body>

</html>

and here's the fruit-varieties.php which runs on the Remote SQL Server:

<?php


    // Connect using SQL Server Authentication.   
    $con = mysqli_connect($serverName, $username, $password); 
        if (!$con) { 
            echo 'Unable to connect to SQL server: ' . mysqli_error();
            die('Unable to connect to SQL server: ' . mysqli_error()); 
        } 

    // Select database
    mysqli_select_db($con,$databaseName);
        if (!$con) { 
            echo 'Unable to open database: ' . mysqli_error(); 
            die('Unable to open database: ' . mysqli_error()); 
        } 

    $fruitName = $_GET['fruitName'];
    echo $fruitName.'|';
    $rows = array();

    if(isset($_GET['fruitName'])) {
        $sql= "SELECT variety FROM fruit WHERE name = '$fruitName' ORDER BY variety";

        $rows = mysqli_fetch_assoc(mysqli_query($con, $sql));
        if (!$con) { 
            echo 'Query failed: ' . mysqli_error(); 
            die('Query failed: ' . mysqli_error()); 
        } 
    }
    echo json_encode($rows);

?>

and finally, here is the SQL database construct and sample data:

SQL to create the table and populate with example data

CREATE TABLE IF NOT EXISTS `fruit` (
  `fruit_id` int(10) unsigned NOT NULL auto_increment,
  `name` varchar(50) NOT NULL,
  `variety` varchar(50) NOT NULL,
  PRIMARY KEY  (`fruit_id`)
);

INSERT INTO `fruit` (`fruit_id`, `name`, `variety`) VALUES
(1, 'Apple', 'Red Delicious'),
(2, 'Pear', 'Comice'),
(3, 'Orange', 'Navel'),
(4, 'Pear', 'Bartlett'),
(5, 'Orange', 'Blood'),
(6, 'Apple', 'Cox''s Orange Pippin'),
(7, 'Apple', 'Granny Smith'),
(8, 'Pear', 'Anjou'),
(9, 'Orange', 'Valencia'),
(10, 'Banana', 'Plantain'),
(11, 'Banana', 'Burro'),
(12, 'Banana', 'Cavendish');

Thank you everyone - really appreciate any help you can give me!

Cheers ...Steve

@Dharman - Thanks - I did say it was simple just to get the the thing working! I'll fix that once I've got the mechanism running. Thanks for picking it up:-) — SteveParry, Jul 03 '19 at 11:29

neoto · Accepted Answer · 2019-07-04T10:41:52.863

CLEAN EDIT: I completely missed the jQuery part where you're sending a custom body. Anyway, your issue is the echo $fruitName.'|'; part. Once you echo out something, that gets sent as the response which means that it never gets to the database querying part.

Here's working code with prepared statements the mysqli way:

<?php

$host = HOST;
$username = USERNAME;
$password = PASSWORD;
$databaseName = DB;

// Connect using SQL Server Authentication.   
$con = new mysqli($host, $username, $password, $databaseName);
if ($con->connect_errno) {
    die('Unable to connect to SQL server: ' . $con->connect_error);
}

$fruitName = $_GET['fruitName'];
$rows = [];

if (!empty($fruitName)) {
    $sql = "SELECT variety FROM fruit WHERE name = ? ORDER BY variety";
    $stmt = $con->prepare($sql);

    if (!$stmt) {
        die("Failed to prepare statement: {$con->error}");
    }

    // since you're passing a string, the first argument is "s"
    $stmt->bind_param("s", $fruitName);

    if (!$stmt->execute()) {
        die("Failed to execute statement: {$stmt->errorInfo()}");
    }

    // bind the result to $result which equals a new row each fetch
    $stmt->bind_result($result);
    while ($stmt->fetch()) {
        $rows[] = $result;
    }

    echo json_encode($rows);
}

$con->close();

The PDO way:

<?php

$host = HOST;
$username = USERNAME;
$password = PASSWORD;
$databaseName = DB;

$options = [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION];
$con = new PDO("mysql:host=$host;dbname=$databaseName", $username, $password, $options);

$fruitName = $_GET['fruitName'];
$rows = [];

if (!empty($fruitName)) {
    $sql = "SELECT variety FROM fruit WHERE name = :name ORDER BY variety";
    $stmt = $con->prepare($sql);

    // bind $fruitName directly in the execute
    // note that you can, but don't have to, use : in here
    try { $stmt->execute(["name" => $fruitName]); }
    catch(PDOException $e) {
        die("Failed to execute statement: {$e->getMessage()}");
    }

    while (($row = $stmt->fetch(PDO::FETCH_COLUMN))) {
        $rows[] = $row;
    }

    echo json_encode($rows);
}

$con = null;

Note that in production, you never spit out the error directly to the user - especially when it comes to database related stuff. For stuff that throws exceptions, you need to catch them, send a 50x status code and log the error. You would also need to configure your server to send users to the appropriate page after doing so. An example for PDO exceptions:

try {
    $stmt->execute(["name" => $fruitName]);
} catch (PDOException $e) {
    http_response_code(500);
    error_log($e->getMessage());
    die();
}

For stuff that doesn't throw exceptions, you make a custom exception handler that does something similar to the above and throw it instead.

Lastly, change the $.each() part to:

$.each(data, function(index, item) {
    options[options.length] = new Option(item);
});

and everything should work.

One more thing: make sure that you have enabled the extension that you're going to use in the php.ini file, by removing the semicolon in front of it. You also have to restart PHP, PHP-FPM, Apache or whatever you have after doing so.

Thanks @krimaeus - just getting an error in the PHP: 03-Jul-2019 21:41:12 Australia/Brisbane] PHP Fatal error: Uncaught Error: Call to undefined method mysqli_result::fetch_all() in /home/theva/wSSN/Secure/fruit-varieties.php:23 Stack trace: #0 {main} thrown in /home/theva/wSSN/Secure/fruit-varieties.php on line 23 Line 23 is $rows = $result->fetch_all(); Don't understand that one - how did it expand out to that error? — SteveParry, Jul 03 '19 at 11:49
How do I stop the comments stripping out all my newlines? - makes it unreadable! — SteveParry, Jul 03 '19 at 11:50
@SteveP What is your PHP version, distro, and do you have mysqlnd installed? Regarding the newlines, you can't create them in comments. — neoto, Jul 03 '19 at 12:45
@Dharman Alright, I'll edit my answer shortly with prepared statements - both the mysqli and the PDO way. — neoto, Jul 03 '19 at 12:46
@Dharman Thank you for recoding with prepared statements and for all your help. This code functions perfectly - good work! — SteveParry, Jul 04 '19 at 00:57
@krimaeus One more thing. Your code should not be catching the exceptions and displaying them to the user. If this code goes into production it could leak sensitive information. `die` is a very bad practice. See this post for more explanation: [mysqli or die, does it have to die?](https://stackoverflow.com/a/15320411/1839439) — Dharman, Jul 04 '19 at 08:50
@Dharman I'm pretty sure both of us know that. I wrote it that way so that they can quickly see what the error was. But alright, I guess I'll make it the way you actually would in production. — neoto, Jul 04 '19 at 09:12

No JSON response from remote web server to HTML web form

1 Answers1