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

Question

I am executing a simple ajax> php> mysql example posted here http://openenergymonitor.org/emon/node/107

I can only display information from the first line. My table is set up like this

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

Php code

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

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 var id = data[0]; , I will get the value 1. If I put var name = data[1]; I get Pat.

ROWS 2 n 3 undefined

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

My questions

• Why am I only getting values ​​from the first row?

• How can I get information for lines other than the first?

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

Answer 1

The old mysql extension is deprecated; better use mysqli or PDO !

mysql_fetch_row() returns only 1 row! You should put it in a loop, for example:

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

You also need 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 recommend you use mysql_fetch_assoc() , because it makes your code more flexible and clean.

Answer 2

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

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