Search code examples
javascriptjsonajaxgetjson

Why can't show data passed by getJSON?


The test-json.php read the database , and prepare it in JSON format.

<?php
$conn = new mysqli("localhost", "root", "xxxx", "guestbook"); 
$result=$conn->query("select * From lyb limit 2"); 
echo '[';
$i=0;
while($row=$result->fetch_assoc()){  ?>
 {title:"<?= $row['title'] ?>",
        content:"<?= $row['content'] ?>",
        author:"<?= $row['author'] ?>",
        email:"<?= $row['email'] ?>",
        ip:"<?= $row['ip'] ?>"}
<?php 
if(
$result->num_rows!=++$i) echo ',';   
}
echo ']'    
?>

For my database,select * From lib limit 2 get the records.

title    | content   | author   | email            |ip
welcome1 | welcome1  | welcome1 | [email protected] |59.51.24.37
welcome2 | welcome2  | welcome2 | [email protected] |59.51.24.38

php -f /var/www/html/test-json.php

[ {title:"welcome1",
         content:"welcome1",
        author:"welcome1",
         email:"[email protected]",
        ip:"59.51.24.37"},
{title:"welcome2",
         content:"welcome2",
        author:"welcome2",
         email:"[email protected]",
        ip:"59.51.24.38"}]

test-json.php get some data in JSON format.

Now to callback the data and show it in the table.

<script src="http://127.0.0.1/jquery-3.3.1.min.js"></script>
<h2 align="center">Ajax show data in table</h2>
<table>
    <tbody id="disp">
        <th>title</th>
        <th>content</th>
        <th>author</th>
        <th>email</th>
        <th>ip</th>
    </tbody>
</table>

<script> 
$(function(){
    $.getJSON("test-json.php", function(data) {
        $.each(data,function(i,item){
            var tr = "<tr><td>" + item.title + "</td>"    +
                        "<td>"  + item.content  + "</td>" +
                        "<td>"  + item.author  + "</td>"  +
                        "<td>"  + item.email  + "</td>"   +
                        "<td>"  + item.ip  + "</td></tr>"
            $("#disp").append(tr);
        });
    });
});
</script>

Type 127.0.0.1/test-json.html, why no data created by test-json.php on a webpage?

What i get is as below:

Ajax show data in table
title   content author  email   ip

What i expect is as below:

Ajax show data in table
title   content author  email   ip
welcome1  welcome1  welcome1  [email protected]  59.51.24.37
welcome2  welcome2  welcome2  [email protected]  59.51.24.38

Solution

  • The problem is the response from your PHP script is not valid JSON.

    In JSON, object keys must be quoted.

    Rather than try and roll-your-own JSON response, use json_encode() to do it for you. For example

    <?php
    $conn = new mysqli("localhost", "root", "xxxx", "guestbook"); 
    $stmt = $conn->prepare('SELECT title, content, author, email, ip FROM lyb limit 2');
    $stmt->execute();
    $stmt->bind_result($title, $content, $author, $email, $ip);
    $result = [];
    while ($stmt->fetch()) {
        $result[] = [
            'title'   => $title,
            'content' => $content,
            'author'  => $author,
            'email'   => $email,
            'ip'      => $ip
        ];
    }
    header('Content-type: application/json; charset=utf-8');
    echo json_encode($result);
    exit;
    

    You don't have to use prepare() and bind_result(), that's just my preference when working with MySQLi.

    This will produce something like

    [
      {
        "title": "welcome1",
        "content": "welcome1",
        "author": "welcome1",
        "email": "[email protected]",
        "ip": "59.51.24.37"
      },
      {
        "title": "welcome2",
        "content": "welcome2",
        "author": "welcome2",
        "email": "[email protected]",
        "ip": "59.51.24.38"
      }
    ]