PHP and encapsulating MySQL rows into a JSON array

Question

I'm playing with a guestbook/chatroom idea and have a MySQL table setup with three columns:

1 - id - Primary Key auto increment

2 - name - String

3 - comment - string

I have very little experience with PHP but this is what I've put together for this operation:

$result = mysql_query("SELECT * FROM guestbook");
$i = 0;
while($row = mysql_fetch_array($result))
  {
      //add the row to the $chat array at specific index of $i
      $chat[$i] = $row;
      $i += 1;
  }

$encode = json_encode($chat);
echo "$encode";

However, output from this looks pretty awful:

[{"0":"1","id":"1","1":"Justin ","name":"Justin ","2":"Comment 1","comment":"Comment 1"},
{"0":"2","id":"2","1":"Justin ","name":"Justin ","2":"Another comment","comment":"Another comment"},
{"0":"3","id":"3","1":"Justin ","name":"Justin ","2":"Look at this comment!","comment":"Look at this comment!"},
{"0":"4","id":"4","1":"Justin ","name":"Justin ","2":"Ok I'm done talking","comment":"Ok I'm done talking"}]

I was hoping to get three fields: id, name, and comment, but it looks like things doubled. Can anyone help?

Thanks!

Code Magician · Accepted Answer · 2011-11-02 20:04:03Z

6

To paraphrase Marc, Just replace this line:

while($row = mysql_fetch_array($result))

With this:

while($row = mysql_fetch_array($result, MYSQL_ASSOC))

To get cleaner JSON. By default mysql_fetch_array() will return both an integer index and an associative index, you only want the associative index.

answered Nov 2, 2011 at 20:04

Code Magician

24.1k7 gold badges63 silver badges77 bronze badges

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

Comments

Marc B · Accepted Answer · 2011-11-02 18:24:25Z

3

mysql_fetch_array returns a compound array: indexed AND keyed. To fetch just one or the other, use mysql_fetch_row() (indexed only), mysql_fetch_assoc() (keyed only), or use the extra argument on fetch_array to specify which you want: mysql_fetch_array($result, MYSQL_BOTH);

answered Nov 2, 2011 at 18:24

Marc B

362k44 gold badges433 silver badges508 bronze badges

3 Comments

Daddy Over a year ago

so would the correct answer just be to json_encode($result);, because I tried that and it just echoed "null".

Marc B Over a year ago

No, the correct answer would be to use mysql_fetch_assoc() in your while() loop, then json_encode $chat as usual. That'll produce JUST the named id/name/comment key:value pairs in the JSON data.

Daddy Over a year ago

I don't understand, can you copy/paste my code and insert the syntax you're describing? I'm concerned with what the put inside the parentheses in mysql_fetch_assoc(), and if I should still be adding to $chat[$i].

Collectives™ on Stack Overflow

PHP and encapsulating MySQL rows into a JSON array

2 Answers 2

Comments

3 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Linked

Related