JSON data in HTML page in a table

Question

I struggling print all my data from DB to webpage using JSON. But I not understand logical how it should work. My JSON script:

<script> 
$("document").ready(function() {
    $.getJSON("test1.php", function(data) {

        $("#div-my-table").text("<table>");

        $.each(data, function(i, item) {
            $("#div-my-table").append("<tr><td>" + item.code +"</td><td>" + item.name + "</td></tr>");
        });

        $("#div-my-table").append("</table>");

    });
});     
</script>

And test1.php file

<?php
require_once 'connection.php';

$sql = $conn -> prepare("SELECT * FROM DB_NAME");

$sql -> execute();

  while ($row = $sql -> fetch(PDO::FETCH_ASSOC)) 
  {
      $values = array('code'=>$row['code'],
                    'line'=>$row['line']);                  
}
echo json_encode($values);
?>

and part of HTML:

<body>

<table id="div-my-table">
</table>

</body>

And system return back only:

<table>
undefined undefined 
undefined undefined

remove the line $("#div-my-table").text("<table>"); and $("#div-my-table").append("<table>"); — Sudharsan S
– Sudharsan S, Commented Dec 4, 2014 at 10:43

Prashant M Bhavsar · Accepted Answer · 2014-12-04 11:03:15Z

2

First make below correction in your code

$values[] = array('code'=>$row['code'],'line'=>$row['line']);

Above change will append all database value to $value variable and will show all records instead of last record of db

Also Please check with your table name in below query

$sql = $conn -> prepare("SELECT * FROM DB_NAME");

It seems that you are taking db name constant here instead of table name as mentioned below.

$sql = $conn -> prepare("SELECT * FROM TABLE_NAME");

edited Dec 4, 2014 at 11:03

answered Dec 4, 2014 at 10:45

Prashant M Bhavsar

1,16410 silver badges13 bronze badges

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

Comments

Kevin · Accepted Answer · 2016-09-13 22:50:56Z

If you're expecting multiple rows, you need to gather the results properly. The $values gets overwritten every iteration.

while ($row = $sql->fetch(PDO::FETCH_ASSOC)) {
    // add another dimension
    $values[] = array(
        'code'=>$row['code'],
        'line'=>$row['line']
    );                  
}

echo json_encode($values);

Or for just one line:

echo json_encode($sql->fetchAll(PDO::FETCH_ASSOC));

So that they are properly nested.

Then on your JS:

<script type="text/javascript"> 
$(document).ready(function() {

    $.getJSON("test1.php", function(data) {

        var table_rows = '';

        $.each(data, function(i, item) {
            table_rows += "<tr><td>" + item.code +"</td><td>" + item.name + "</td></tr>");
        });

        $("#div-my-table").html(table_rows);

    });

});     
</script>

Barmar · Accepted Answer · 2014-12-04 10:46:33Z

You're overwriting the same $values variable each time through the loop. At the end, it will just contain a single row, not an array of all the rows. You need to add each row to $values, not replace it. It should be:

$values = array();
while ($row = $sql -> fetch(PDO::FETCH_ASSOC)) 
{
    $values[] = $row;
}
echo json_encode($values);

Also, your jQuery is wrong. You shouldn't use .text() when you're creating HTML, you should use .html(). And you don't need to append </table> -- these functions operate on the DOM, not the HTML, and the DOM always has complete elements.

$("document").ready(function() {
    $.getJSON("test1.php", function(data) {

        var table = $("<table>");
        $("#div-my-table").empty().append(table);

        $.each(data, function(i, item) {
            table.append("<tr><td>" + item.code +"</td><td>" + item.name + "</td></tr>");
        });

    });
});

White Marcus · Accepted Answer · 2014-12-04 10:45:13Z

0

Use last.after();

Instead of append();

answered Dec 4, 2014 at 10:45

White Marcus

3052 gold badges4 silver badges12 bronze badges

Collectives™ on Stack Overflow

JSON data in HTML page in a table

4 Answers 4

Comments

Comments

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Related