Explore our sites

Stack Exchange
tour help careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

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);
?>
share|improve this question
 
Still can't fix it. –  Bob May 5 '12 at 19:51
 
I'm wondering if the word name or categories in your query would need to be escaped properly with `. Also, can you add a print_r($_GET); to the top of your PHP script and paste what the output is? –  Michael Taggart May 11 '12 at 20:42
 
@Michael Taggart I tried both. And print_r is empty. –  Bob May 15 '12 at 15:16
 
hrmmmmm. If print_r($_GET); doesn't return anything then that means the javascript is not posting the variables at all. Can you add this above the $.ajax line in popList(): alert('field='+escape(field)+'&table='+escape(table)); Make sure that you see the data in the alert window. –  Michael Taggart May 15 '12 at 21:01
 
also do you have a URL where I can take a look at what you're trying to do? –  Michael Taggart May 15 '12 at 21:01
show 1 more comment

1 Answer

up vote 4 down vote accepted

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!

share|improve this answer
 
I tried to implement your version of popList() but for some reason the field and table values do not get to the PHP script and I end up with an empty listbox. I've updated all three source codes in my question and added a detailed description of my current problem, so if you happen to know what is causing this feel free to check the source. –  Bob May 4 '12 at 23:31
1  
Hmmm, if you have "dataType: 'json'" then I believe the response variable is already json decoded for you. Meaning it gets turned into an array/object whatever behind the scenes. It looks like you are just passing the response straight through to the jqxListBox function in string form. Try changing dataType to html so that jQuery doesn't do any automatic parsing. –  Michael Taggart May 9 '12 at 18:27
 
@Bob it looks like your query would look like this: –  Michael Taggart May 11 '12 at 20:38
 
Okay. I'll try it. Thanks. –  Bob May 15 '12 at 15:18
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.