Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

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!

share|improve this question

2 Answers

up vote 4 down vote accepted

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.

share|improve this answer
This saved the day. – Justin Amberson Nov 2 '11 at 20:28
up vote 3 down vote

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);

share|improve this answer
so would the correct answer just be to json_encode($result);, because I tried that and it just echoed "null". – Justin Amberson Nov 2 '11 at 18:43
1  
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. – Marc B Nov 2 '11 at 18:50
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]. – Justin Amberson Nov 2 '11 at 19:34
1  
+1 for being first and correct. – Code Magician Nov 2 '11 at 20:41
Awesome! how would you increase the index, starting with 0, 1, 2 etc... – Christopher Nov 15 '12 at 15:49

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.