How do I properly use PHP to encode MySQL object into JSON?

Question

I am trying to iterate through a MySQL object and use an ajax call on another page to append the data but I can't get the php to return valid JSON to the callback.

This one obviously doesn't work...

<?php

    $db_host = "localhost";
    $db_user = "blah";
    $db_pass = "blah";
    $db_name = "chat";
    $mysqli = new MySQLi($db_host, $db_user, $db_pass, $db_name);
    $myQuery = "SELECT * FROM users";
    $result = $mysqli->query($myQuery) or die($mysqli->error);
    $row = $result->fetch_assoc();
    echo json_encode($row);

?>

Or this one...

<?php

    $db_host = "localhost";
    $db_user = "blah";
    $db_pass = "blah";
    $db_name = "chat";
    $mysqli = new MySQLi($db_host, $db_user, $db_pass, $db_name);
    $myQuery = "SELECT * FROM users";
    $result = $mysqli->query($myQuery) or die($mysqli->error);
    while ( $row = $result->fetch_assoc() ){
        echo json_encode($row) . ", ";
    }

?>

tereško · Accepted Answer · 2012-08-09 22:12:05Z

14

$data = array();

while ( $row = $result->fetch_assoc() ){
    $data[] = json_encode($row);
}
echo json_encode( $data );

This should do it. Also, you can use http://jsonlint.com/ to see what are the problems with your JSON output.

Update: using fetch_all() might be a good idea too

$data = $result->fetch_all( MYSQLI_ASSOC );
echo json_encode( $data );

answered Aug 9, 2012 at 22:12

tereško

58.5k26 gold badges100 silver badges151 bronze badges

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

5 Comments

kapa Over a year ago

+1 Or maybe use fetch_all(). Note: I guess you don't need . ", ".

rib3ye Over a year ago

@tereško yeah! this is a good one, but now I don't know what to do with the callback... before I was using $("#diver").append("<p>" + data.id + "</p>"); but I just get an undefined variable.

tereško Over a year ago

@nipponese , try doing console.dir(data); in the callback (that's assuming that you have already parsed the JSON on JS side). Maybe the names of parameters have changed. Also, you are returning an array of objects instead of just a single object , that has been encode as JSON string. It would be something like data[0].id now.

rib3ye Over a year ago

Oh, yeah, duh, it's an array now. Thanks so much!

RicNjesh Over a year ago

Hey! Check that you don't encode twice! It will add back slashesin your json data. I think it should look like: $data[] = $row; NOT $data[] = json_encode($row);

zelda-franco · Accepted Answer · 2014-01-27 17:38:49Z

2

I use this:

    $json = array();

    if(mysqli_num_rows($result)){
        while($row=mysqli_fetch_assoc($result)){
                $json[]=$row;
        }
    }
    mysqli_close($mysqli);
    echo json_encode($json);

?>

and I get something like this

[ {"id":"2","usuario":"zeldafranco","password":"lol"},{"id":"3","usuario":"franco","password":"franco"},{"id":"4","usuario":"peteko","password":"sanpeteko"},{"id":"5","usuario":"prueba","password":"prueba"},{"id":"6","usuario":"test","password":"test"}, {"id":"7","usuario":"pibe","password":"hola"}, {"id":"8","usuario":"que ase","password":"que ase"},{"id":"9","usuario":"trt","password":"trt"}, {"id":"10","usuario":"tyt","password":"tyt"} ]

answered Jan 27, 2014 at 17:38

zelda-franco

211 bronze badge

1 Comment

Vrutin Rathod Over a year ago

Exactly what I was looking for, and its Valid JSON according to JSONLint.

user1588841 · Accepted Answer · 2012-08-09 22:15:42Z

-2

$arrUsers = array();

$fetch = mysql_query("SELECT * FROM users"); 

while ($row = mysql_fetch_array($fetch, MYSQL_ASSOC)) {



 $arrUsers['id'] = $row['name'];
    $arrUsers['col1'] = $row['col1'];
    $arrUsers['col2'] = $row['col2'];

    array_push($arrUsers,$row_array);

}

echo json_encode($arrUsers);

answered Aug 9, 2012 at 22:15

user1588841

12 bronze badges

Collectives™ on Stack Overflow

How do I properly use PHP to encode MySQL object into JSON?

3 Answers 3

5 Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related