Skip to content
Advertisement

How to store a SQL result in a JavaScript array, using AJAX and JSON

I want to retrieve a table from an SQL database dynamically into an array using JavaScript.

The code below works fine if I use a global JavaScript variable for the array. But I would like to pass the array the data is to be stored in dynamically as well. That is where the problem begins.

My source:

var getSQL = function (query, array, callback) {
    var req = new XMLHttpRequest();
    req.onreadystatechange = function () {
        if (req.readyState == 4 && req.status == 200) {
            callback(req.responseText, array);
        }
    };
    var param = "?q=" + encodeURIComponent(query);
    req.open("GET", "sql.php" + param, true);
    req.send();
}
function mycallback (json_expr, t_array) {
    t_array = JSON.parse(json_expr);
}

The custom array in which I want to stor ethe result of that request is called my_data:

var my_data = [];
getSQL('SELECT * FROM users ORDER BY id', my_data, mycallback);

// wait some time

// then this code gets called via an onclick event
alert(my_data[0]['name']);

The result is undefined. Why?

Advertisement

Answer

This does not work because changing the value of t_array in mycallback only changes the value of t_array in the scope of the function, it does not change the value that was passed to it.

If you want to use this construction, you should modify the t_array instead, so:

t_array.result = JSON.parse(json_expr);

Then you can read

alert(my_data.result[0]['name']);

But it’s more common to provide a callback function instead of an array, like this:

var getSQL = function (query, callback) {
    var req = new XMLHttpRequest();
    req.onreadystatechange = function () {
        if (req.readyState == 4 && req.status == 200) {
            callback(req.responseText);
        }
    };
    var param = "?q=" + encodeURIComponent(query);
    req.open("GET", "sql.php" + param, true);
    req.send();
}

And then call it as follows:

var my_data = [];
getSQL('SELECT * FROM users ORDER BY id', function(json_expr){
    my_data = JSON.parse(json_expr);
});

...

alert(my_data[0]['name']);

The callback function will be able to change the value of the my_data var because of how javascript function closure works.

User contributions licensed under: CC BY-SA
1 People found this is helpful
Advertisement