Simple Ajax Jquery script- How can I get information for each of the rows in the table?

Question

I'm following a simple ajax>php>mysql example posted here http://openenergymonitor.org/emon/node/107

I can only display information from the first row. My table is set up like so

--------------
|  id  | name|
--------------
| 1    | Pat |
| 2    | Joe |
| 3    | Rob |
--------------

The php code

 $result = mysql_query("SELECT * FROM $tableName");          //query
 $array = mysql_fetch_row($result);                          //fetch result  
 echo json_encode($array);

The script

$(function () 
  {
    $.ajax({                                      
      url: 'api.php', data: "", dataType: 'json',  success: function(data)        
      { 
        var id = data[0];              //get id
        var vname = data[1];           //get name
         $('#output').html("<b>id: </b>"+id+"<b> name: </b>"+vname); 
      } 
    });
  });

ROW 1

If I put the var id = data[0]; I get the value 1. If I put the var name = data[1]; I get Pat.

ROWS 2 n 3 are undefined

Example var id=data[2]; returns undefined etc

My Questions

Why do I only get the values from the first row?
How can I get information for rows other than the first one?

From other questions on Stackoverflow I see that I will probably have to use a while loop, but I'm not really sure why or how.

try using mysql_fetch_array instead of mysql_fetch_row. Regards Jonas — Jonas m, Nov 05 '11 at 10:14
Can you access the php page alone instead of doing by ajax request and tell us what the json echo prints on the screen? — Guilherme David da Costa, Nov 05 '11 at 10:14
@Jonas Thanks. I tried mysql_fetch_array but it still returned undefined for data[2],data[3],etc — TryHarder, Nov 05 '11 at 10:27
@ComFreek Thanks. That's very useful! Looks like I've got a bit of PHP study to do. — TryHarder, Nov 05 '11 at 11:07

ComFreek · Accepted Answer · 2013-04-26T15:18:59.543

21

The old MySQL extension mysql is outdated, better use mysqli or PDO!

mysql_fetch_row() returns only 1 row! You have to put it into a loop, for example:

$data = array();
while ( $row = mysql_fetch_row($result) )
{
  $data[] = $row;
}
echo json_encode( $data );

You also have to change the JavaScript:

$.ajax({                                      
  url: 'api.php', data: "", dataType: 'json',  success: function(rows)        
  {
    for (var i in rows)
    {
      var row = rows[i];          

      var id = row[0];
      var vname = row[1];
      $('#output').append("<b>id: </b>"+id+"<b> name: </b>"+vname)
                  .append("<hr />");
    } 
  } 
});

By the way I would recommend you to use mysql_fetch_assoc() because it makes your code more flexible and cleaner.

edited Apr 26 '13 at 15:18

answered Nov 05 '11 at 10:15

ComFreek

29,044
18
104
156

@ComFreek The PHP seems to be working, because I get [["1","Pat"],["2","Joe"],["3","Rob"]] when I run the PHP. However, no result was displayed in #output. I must have made a mistake somewhere. – TryHarder Nov 05 '11 at 10:47
is it possible to add auto refresh to this , to refresh the div, table , etc. whatever the object is at a defined interval, like 5 sec. ? – kayhan yüksel Mar 19 '13 at 00:26
1

@kayhanyüksel Yes, it's possible. There would be 2 techniques: **a)** *Pull* the data regularly from the server (PHP script). Use either 'normal' AJAX request and [window.setInterval()](https://developer.mozilla.org/en-US/docs/DOM/window.setInterval) or use [long polling](http://stackoverflow.com/questions/333664/simple-long-polling-example-code). **b)** *Push* the data, i.e. use WebSockets. There are many good resources on the internet, for example: http://www.websocket.org/ – ComFreek Mar 19 '13 at 12:23

score 0 · Answer 2 · edited Jan 05 '20 at 09:28

0

$result = mysql_query("SELECT * FROM $tableName");
$array = array(mysql_fetch_row($result));

// To store every row in the $array
while($row = mysql_fetch_row($result)) { 
    $array[] = $row; 
} 
echo json_encode($array);

edited Jan 05 '20 at 09:28

סטנלי גרונן

2,917
23
46
68

answered Nov 13 '17 at 13:52

Elfaks

1
1

3

Please add some bit of explanation instead of a code only answer. While this may work, the OP should also now WHY and HOW it works. – Manuel Mannhardt Nov 13 '17 at 13:54

Simple Ajax Jquery script- How can I get information for each of the rows in the table?

2 Answers2

Linked