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

Question

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?

Your problem exists of two halves. You call sql.php on the server, which I assume is a php server that retrieves the data from the database? To check if this half works, you can simply make the call in the browser and check the results. If that works, the second bit is to make that request from javascript. — flup, Aug 30 '14 at 15:33
The sql.php file works just fine, and if I use a global (fixed) variable, everything works as expected. But thanks, I will clarify that above. — Jens, Aug 30 '14 at 15:38

score 1 · Accepted Answer · answered Aug 30 '14 at 16:08

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.

Great, this works both fine for me. I did not know about the result option. I was under the impression that all function arguments are passed byref. — Jens, Aug 30 '14 at 16:33
Apparently it's called 'call-by-sharing'. See http://stackoverflow.com/questions/518000/is-javascript-a-pass-by-reference-or-pass-by-value-language . If you like this answer, you can accept it :) — flup, Aug 30 '14 at 16:42

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

1 Answers1