Query string parameters from Javascript do not make it to processing PHP script

Question

I want to populate a jQWidgets listbox control on my webpage(when page finished loading and rendering) with values from an actual MySQL database table.

PARTIAL SOLUTION: Here

NEW PROBLEM: I've updated the source code and if I hardcode the SQL string - the listbox gets populated. But I want to make a small JS function - popList(field, table) - which can be called when you want to generate a jQWidgets listbox with values from a MySQL database on a page.

Problem is - for some reason the $field and $table are empty when the PHP script is being executed, and I receive You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM' at line 1 error. What gives?

The page:

            <div id="ListBox">
                <script type="text/javascript">
                popList("name", "categories");
            </script>
            </div>

popList(field, value):

    function popList(field, table) {
    $.ajax({
        type: "GET",
        url: 'getListOfValues.php',
        data: 'field='+escape(field)+'&table='+escape(table),
        dataType: 'json',
        success: function(response) {
            var source = $.parseJSON(response);
            $("#ListBox").jqxListBox({ source: source, checkboxes: true, width: '400px', height: '150px', theme: 'summer'});
        },
        error: function() {
            alert('sources unavailable');
        }
    });
}

getListOfValues.php:

    <?php
    require "dbinfo.php";

    // Opens a connection to a MySQL server
    $connection=mysql_connect($host, $username, $password);
    if (!$connection) {
        die('Not connected : ' . mysql_error());
    }

    // Set the active MySQL database
    $db_selected = mysql_select_db($database, $connection);
    if (!$db_selected) {
        die ('Can\'t use db : ' . mysql_error());
    }

    $field = $_GET["field"];
    $table = $_GET["table"];

    $field = mysql_real_escape_string($field);
    $table = mysql_real_escape_string($table);

    $qryString = "SELECT " . $field . " FROM " . $table;

    $qryResult = mysql_query($qryString) or die(mysql_error());

    $source = array();

    while ($row = mysql_fetch_array($qryResult)){
        array_push($source, $row[$field]);
    }

    mysql_close($connection);

    echo json_encode($source);
?>

Answer 1

Ok, you have a few things here. First off you need a callback function when you do the ajaxRequest. (I'll explain why in a bit.) So add the following line BEFORE your ajaxReqest.send(null);

ajaxRequest.onreadystatechange = processAjaxResponse;

Then you need to add the processAjaxResponse function which will be called.

function processAjaxResponse() {
    if (ajaxRequest.readySTate == 4) {
        var response = ajaxRequest.responseText;
        //do something with the response
        //if you want to decode the JSON returned from PHP use this line
        var arr = eval(response);
    }
}

Ok, now the problem on your PHP side is you are using the return method. Instead you want PHP to print or echo output. Think about it this way. Each ajax call you do is like an invisible browser. Your PHP script needs to print something to the screen for the invisible browser to grab and work with.

In this specific case you are trying to pass an array from PHP back to JS so json_encode is your friend. Change your return line to the following:

print json_encode($listOfReturnedValues);

Let me know if you have any questions or need any help beyond this point. As an aside, I would really recommend using something like jQuery to do the ajax call and parse the response. Not only will it make sure the ajax call is compliant in every browser, it can automatically parse the JSON response into an array/object/whatever for you. Here's what your popList function would look like in jQuery (NOTE: you wouldn't need the processAjaxResponse function above)

function popList(field,table) {
    $.ajax({
            type: "GET",
            url: 'getListofValues.php',
            data: 'field='+escape(field)+'&table='+escape(table),
            dataType: "json",
            success: function(response) {
                //the response variable here would have your array automatically decoded
            }
        });
}

It's just a lot cleaner and easier to maintain. I had to go back to some old code to remember how I did it before ;)

Good luck!