Return array containing SQL query results to a webpage using JSON

Question

I have a PHP script that runs an SQL query. I'd like to put the results in an array, encode it as a JSON object, echo it back to the Javascript that called it, and use it as an array to write some HTML.

This is the JS which calls the PHP script using POST. I attempt to parse the echoed data using JSON.parse(data) (I also tried jQuery.parseJSON(data) - not sure if there's a difference), and I display the raw echoed data on the HTML page for testing.

var components = [];    // create empty array for component list
if ($('#area').val) {   // if area contains a value and isn't zero (i.e. an area has been selected)
    $.post("/basic/get_area_components.php"
    ,{area: $("#area").val()}
    ,function(data){ components = JSON.parse(data);
        $("#components_raw").html(data);
        $("#components").html(components);
    });
}

The PHP script (after the database connection has been set up) looks like this:

$result = $conn->query("SELECT Component FROM ConfigComponent WHERE Parent =" . $area);

while($row = $result->fetch_array()) {
    $rows[] = $row;
}

$i = 1;
foreach ($rows as $value) {
    $components[$i] = $value[0];
    $i++;
}

echo json_encode($components);

When I run the script, I get the raw output on the HTML page as follows:

{"1":"Handlebar","2":"Stem","3":"Headset","4":"Fork"}

which appears to be a correctly formatted JSON object, but nothing from the parsed components array, and no exceptions. I have tried scanning the array using forEach and printing or alerting the elements individually, to no avail.

How can I parse the JSON object correctly and use it in the Javascript? Is it a problem with the JS parsing, or the PHP encoding?

You are generating a JSON object, not an array. That is why `forEach` is not working. Try changing `$i = 1;` to `$i = 0` so the array index starts with zero. Perhaps `json_encode()` will then generate a JSON array. — John S, Nov 07 '17 at 20:18
That instance of `forEach` is working, unless I'm misunderstanding how it's working - it's where I rearrange the query result into an array of my own format. It's afterwards that I encode it as a JSON object to echo it to the JS. — Dave W, Nov 07 '17 at 21:23
You're right though, changing the index to start at 0 causes an array to be produced rather than an object (I deliberately started at 1 because of how I use the index later). I'm still trying to work this one out. — Dave W, Nov 07 '17 at 21:31

score 3 · Accepted Answer · answered Nov 07 '17 at 20:19

3

This behavior is normal. Javascript only support indexed arrays. Because your index in php starts with 1 instead of 0 it becomes an associative array instead of indexed.

$i = 0; // start with 0
foreach ($rows as $value) {
    $components[$i] = $value[0];
    $i++;
}

or

   foreach ($rows as $value) {
      // or don't use an index when adding a value to the array         
      $components[] = $value[0];
   }

This will result in a json array ["Handlebar","Stem","Headset","Fork"]

answered Nov 07 '17 at 20:19

ARN

679
7
15

I understand now. @John S identified this behaviour in a comment, but not the reasoning. I actually wanted to use an associative array, but since it causes problems with the JSON encoding, I can avoid it. – Dave W Nov 07 '17 at 21:34
If the keys are important you can set them as associative array in php en once converted to json use them as an object. You can handle that object as it is an array: yourObject['1'] had the value "Handlebar" – ARN Nov 08 '17 at 06:18

score 2 · Answer 2 · answered Nov 07 '17 at 20:09

2

Interesting problem. In Js the line:

{"1":"Handlebar","2":"Stem","3":"Headset","4":"Fork"}

Is no longer an array. It's an object. It seems jQuery actually disregards objects passed into the .html() method. It will render strings or execute functions.

So, you are already doing it right it's just not displaying to the page properly. I suggest using console.log instead.

answered Nov 07 '17 at 20:09

ktilcu

3,032
1
17
15

yes, .html() [calls](https://github.com/jquery/jquery/blob/master/src/manipulation.js#L435) append which [invokes](https://github.com/jquery/jquery/blob/master/src/manipulation.js#L345) native .appendChild() And latest does nothing when getting raw object – skyboyer Nov 07 '17 at 20:19
interesting thing, right? :) the only reason "true" arrays works in different way - because jQuery iterating them on its own. So list of primitives(strings or numbers) will be finally injected as text nodes. – skyboyer Nov 07 '17 at 20:22
I understand - I was actually surprised when the html() method printed out the whole array. You're right, console.log gives `object {1: "Frame", 2: "Seatpost", 3: "Seat clamp", 4: "Shock", 5: "Saddle"}` Thing is, I thought JSON.parse() was going to take an object and give me an array. How can I do that? Or should I just learn how to use the object as is? – Dave W Nov 07 '17 at 21:05
PHP has arrays and associative arrays. When you encode in php you want to make sure you just have a list of values instead of an associative array. Associative arrays encode to Objects in JSON. – ktilcu Nov 07 '17 at 21:28

score 0 · Answer 3 · answered Nov 07 '17 at 20:20

0

Try defining the type of data javascript should expect from the server. Try the following

$.post("/basic/get_area_components.php",
    {area: $("#area").val()},
    function(data){ 
        var components = JSON.parse(data);
        $("#components_raw").html(data);
        $("#components").html(components);
    },
    "json"); //define expected type here

Try it out and let me know if it works for you. More information on how to use $.post() can be found here

answered Nov 07 '17 at 20:20

natral

986
1
18
42

I see your thinking. I don't think it should make a difference, since as I understand it, jQuery is smart enough to detect the data type, but it makes sense to try. However it fails with: `Uncaught SyntaxError: Unexpected token o in JSON at position 1 at JSON.parse ()` which I can't explain – Dave W Nov 07 '17 at 21:16

score 0 · Answer 4 · answered Nov 07 '17 at 20:25

I suppose you're using jquery there so this maybe of help: How do I iterate over a JSON structure?

and yes for loop will also do:

var componentFormatted;
for (var k in obj){
    componentFormatted += obj[k];
}

then just apply any of your formatted output here

$("#components").html

Return array containing SQL query results to a webpage using JSON

4 Answers4