MYSQL Data -> PHP arrays -> Javascript (or Jquery) How can I pass the data with JSON?

Question

I am starting with a new site (it's my first one) and I am getting big troubles ! I wrote this code

<?php
    include("misc.inc");
    $cxn=mysqli_connect($host,$user,$password,$database) or die("couldn't connect to server");
    $query="SELECT DISTINCT country FROM stamps";
    $result=mysqli_query($cxn,$query) or die ("couldn't execute query");
    $numberOfRows=mysqli_num_rows($result);

    for ($i=0;$i<$numberOfRows;$i++){
        $row=mysqli_fetch_assoc($result);
        extract($row);
        $a=json_encode($row);
        $a=$a.",";
        echo $a;
    }
?>

and the output is as follows :

{"country":"liechtenstein"},{"country":"romania"},{"country":"jugoslavia"},{"country":"polonia"},

which should be a correct JSON outout ...

How can I get it now in Jquery ? I tried with

$.getJSON

but I am not able to fuse it properly. I don't want yet to pass the data to a DIV or something similar in HTML.

As an update, the code of Andres Descalzo works !

<?php
    include("misc.inc");
    $cxn=mysqli_connect($host,$user,$password,$database) or die("couldn't connect to server");
    $query="SELECT DISTINCT country FROM stamps";
    $result=mysqli_query($cxn,$query) or die ("couldn't execute query");
    $numberOfRows=mysqli_num_rows($result);

    echo "{data: [";
    for ($i=0; $i<$numberOfRows; $i++){
        $row=mysqli_fetch_assoc($result);
        extract($row);
        $a = (($i!=0)?",":"") . json_encode($row);
        echo $a;
    }
    echo "]}";
?>

The output is correct and is as follows :

{data: [{"country":"liechtenstein"},{"country":"romania"},{"country":"jugoslavia"},{"country":"polonia"}]}

How can I use $getJSON ?

It's ok that the syntax is

$.getJSON( url, [ data ], [ callback(data, textStatus) ] )

and that the url is the above mentioned PHP file but [data] and callback function?

Felix Kling · Accepted Answer · 2010-04-29 18:42:41Z

3

It is not correct JSON. Correct would be, if the elements were enclosed in square brackets (indicating an array) like so:

[{"country":"liechtenstein"},
 {"country":"romania"},
 {"country":"jugoslavia"},
 {"country":"polonia"}]

You can first fetch all elements from the DB in an array and then encode this array:

$elements = array()

for ($i=0;$i<$numberOfRows;$i++){
        $elements[]=mysqli_fetch_assoc($result);
}

echo json_encode($elements);

This should work (using $.getJSON() in jQuery).

Update: A .getJSON() example:

$.getJSON('/path/to/php_file', function(data) {
    // something with data which is of form
    // data = [{'country': '...'}, {...}, ...]
    //e.g.
    alert(data[0].country);
});

edited Apr 29, 2010 at 18:42

answered Apr 29, 2010 at 16:56

Felix Kling

820k181 gold badges1.1k silver badges1.2k bronze badges

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

Comments

Davide · Accepted Answer · 2010-04-29 17:04:49Z

1

Pay attention, the JSON string is not a valid JSON string! I suggest you to use json_encode once, just before producing the output. You'd probably do that:

$countries = array();
for ($i=0;$i<$numberOfRows;$i++){
    $row=mysqli_fetch_assoc($result);

    //Not needed, I guess
    //extract($row); 

    $countries[] = $row;

    //More probably, you want to get only the country name
    //$countries[] = $row['country'];
}

$result = json_encode( $countries );
echo $result;

Hope it's correct, I haven't tested it :)

answered Apr 29, 2010 at 17:04

Davide

2,3391 gold badge14 silver badges12 bronze badges

Comments

hendepher · Accepted Answer · 2010-04-29 16:56:15Z

0

I think you want to echo once - after the loop. Also, if you want to pass it as an array, surround it with brackets. Something like this:

for ($i=0;$i<$numberOfRows;$i++){
        $row=mysqli_fetch_assoc($result);
        extract($row);
        $a=json_encode($row);
        $a=$a.",";
    }
echo '['.$a.']';

The result you would be sending would be:

[{"country":"liechtenstein"},{"country":"romania"},{"country":"jugoslavia"},{"country":"polonia"},]

As to $.getJSON, how are you applying this? The syntax for getJSON is:

$.getJSON( url, [ data ], [ callback(data, textStatus) ] )

You need a callback function to make use of 'data'

answered Apr 29, 2010 at 16:56

hendepher

1435 bronze badges

Comments

andres descalzo · Accepted Answer · 2010-04-29 17:42:25Z

0

you can try this way:

PHP/HTML:

<div id="iddiv">
<?php
    echo "{data: [";
    for ($i=0; $i<$numberOfRows; $i++){
        $row=mysqli_fetch_assoc($result);
        extract($row);
        $a = (($i!=0)?",":"") . json_encode($row);
        echo $a;
    }
    echo "]}";
?>
</div>

Javascript:

$(function(){    
    var p = $.getJSON($("#iddiv").text());
    alert(p[0].country);
});

edited Apr 29, 2010 at 17:42

answered Apr 29, 2010 at 16:56

andres descalzo

15k14 gold badges66 silver badges119 bronze badges

2 Comments

Felix Kling Over a year ago

I don't think p[0].country is correct. The return value from PHP is an JSON object with attribute data so if any it should be p.data[0]. Second you are not generating valid JSON. In JSON, every string has to be inside quotes, so it should be {"data": [ ...

Felix Kling Over a year ago

-1 You cannot use getJSON this way. It is a function making use of Ajax. There is a lot wrong with your code.

Collectives™ on Stack Overflow

MYSQL Data -> PHP arrays -> Javascript (or Jquery) How can I pass the data with JSON?

4 Answers 4

Comments

Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related