ansaurus

Question

How can I get all values of a mysql table in JSON from a php script?

Answer 1

A:

mysql_fetch_assoc will only return a single row from the database. You will need a loop to retrieve all rows:

$data = array();
while ($row = mysql_fetch_assoc($result)) {
    // add some or all of $row to the $data array
}
echo json_encode($data);

Richard Fearn 2010-10-19 19:49:39

it happens same with ur solution too as with Fosco matter with little difference. The ouput of HTML is same i.e. the alert box ouput 'undefined'. While urs json echo in php output last 'options' value of the column while Fosco's json echo output the 1st 'options' value of the options column.

XCeptable 2010-10-20 08:36:00

Answer 2

+3 A:

Your PHP needs to actually put all the rows together:

$query = "SELECT * FROM user_spec"; 
$result=mysql_query($query);    
$outArray = array();
if ($result) {
  while ($row = mysql_fetch_assoc($result)) $outArray[] = $row;
}
echo json_encode($outArray);

Your Javascript needs to look at each of the rows..

$.getJSON("/whatever.php", function(jsonData) { 
   for (var x = 0; x < jsonData.length; x++) {
      alert(jsonData[x].options);
   }
});

Fosco 2010-10-19 19:49:50

did u put for ech row in exact php syntax ?

XCeptable 2010-10-19 20:24:16

@babar The `for each` is in the javascript code, not the PHP side.

Fosco 2010-10-19 20:25:31

OH OK, thanX, I just made mistake......

XCeptable 2010-10-19 20:27:21

r u sure javascript has for each loop ?

XCeptable 2010-10-19 20:37:28

@babar did you try it?

Fosco 2010-10-19 21:03:57

XCeptable 2010-10-20 07:19:20

OK, its called a 'for in' loop in javascript } " But it opens an alert saying 'undefined' with ur code, not value is ouputted in HTML while php outputs [{"options":"smart_exp"}], 'smart_exp' is 1st value in 'options' column.

XCeptable 2010-10-20 09:34:47

@babar I realized an issue with the php code and corrected it... try again. I will also add a different JS method.

Fosco 2010-10-20 12:24:07

actually this does what I was expecting as I have done this earlier, it raises error, 'Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 24 bytes) in D:\xampp\htdocs\conn_mysql.php on line 12'.

XCeptable 2010-10-20 13:19:51

XCeptable 2010-10-20 13:20:22

@babar it has to be $data[] = $row, otherwise you're only getting the last row... you see why if it's just $data = $row, it's no longer an array, right?

Fosco 2010-10-20 13:29:58

$.getJSON with bew for loop is also not working, I tested it with while ($row = mysql_fetch_assoc($result)) loop in php that outputs all mysql values as I mentioned earlier.

XCeptable 2010-10-20 13:31:41

@babar can you update your question with new code both php and js?.. I've also updated the php portion of my answer.

Fosco 2010-10-20 13:34:06

I have updated the code, php is now showing all result in json echo but html is still not showing outpu. I am still working on it, may be some misplaced brace etc.

XCeptable 2010-10-20 13:58:06

@babar are the html page and the php page on the same address? change your getJSON call to href just conn_mysql.php without the full URL.

Fosco 2010-10-20 14:27:26

XCeptable 2010-10-20 14:41:38

XCeptable 2010-10-20 19:30:17

Answer 3

A:

Hi, try this:

<?php 
    $username = "user"; 
    $password = "********"; 
    $hostname = "localhost"; 
    $dbh = mysql_connect($hostname, $username, $password) or die("Unable to connect 
         to MySQL"); 
    $selected = mysql_select_db("spec",$dbh) or die("Could not select first_test"); 
    $query = "SELECT * FROM user_spec"; 
    $result=mysql_query($query);

    $_ResultSet = array();

    while ($row = mysql_fetch_assoc($result)) {
       $_ResultSet[] = $row;
    }
       echo json_encode($_ResultSet); 
?>

and your jQuery would looks like :

$.getJSON("/yourscript.php", function(data) {
    $.each(data, function(i, j) {
        // use: j.columnName
    });
});

Good luck

Cybrix 2010-10-19 19:54:55

I can't comment Fosco's answer but your example wont work if the query returns more then one row. Only the first one will be returned to getJSON.

Cybrix 2010-10-19 19:58:29

ansaurus

tags:

views:

answers:

How can I get all values of a mysql table in JSON from a php script?

related questions