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
– John S, Commented Nov 7, 2017 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
– Dave W, Commented Nov 7, 2017 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
– Dave W, Commented Nov 7, 2017 at 21:31

ARN · Accepted Answer · 2017-11-07 20:19:04Z

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 7, 2017 at 20:19

ARN

7298 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Dave W Over a year ago

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.

ARN Over a year ago

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"

ktilcu · Accepted Answer · 2017-11-07 20:09:22Z

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 7, 2017 at 20:09

ktilcu

3,1381 gold badge20 silver badges16 bronze badges

4 Comments

skyboyer Over a year ago

yes, .html() calls append which invokes native .appendChild() And latest does nothing when getting raw object

skyboyer Over a year ago

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.

Dave W Over a year ago

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?

ktilcu Over a year ago

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.

natral · Accepted Answer · 2017-11-07 20:20:05Z

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 7, 2017 at 20:20

natral

1,1081 gold badge19 silver badges46 bronze badges

1 Comment

Dave W Over a year ago

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 (<anonymous>) which I can't explain

EverestOnline Marketing · Accepted Answer · 2017-11-07 20:25:12Z

0

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

answered Nov 7, 2017 at 20:25

EverestOnline Marketing

293 bronze badges

Collectives™ on Stack Overflow

Return array containing SQL query results to a webpage using JSON

4 Answers 4

2 Comments

4 Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

4 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related