PHP Json_encode - No json data on specific column MySQL

Question

I am trying to make a simple journalview app for iPhone using a mysql DB. I am using php to connect to the db and encode the data to json. I am using a very simple db with 3 columns:

id
subject
message

When I select the id and subject from the table, the JSON data is displayed correctly. When I select everything or I select the message alone, I only see a white screen. The message column is of datatype text and should be able to have strange signs (/,\,¨,$,%, spaces, lines,...) The only way I can get the message data to display is by doing the following in my connection file:

while($r = mysql_fetch_assoc($resultset))
        {

        //$r = preg_replace("!\r?\n!", " ",$r);
        $r= json_encode($r);

            $records[] = $r;

        }

        //Output the data as JSON

        echo json_encode($records);
    }


}

However, the data that is returned is full of backslashes, \r, \n and does not seem like valid json:

[{"id":"248","subject":"General","message":"Dear Diary\r\n\r\nThis is a test.\r\nDoes it work       or not?!\r\n\r\nGoodbye.\r\n\r\n\r\nD"},{"id":"249","subject":"General","message":"Hi\r\n\r\nThis is Test number 2.\r\nDoes it Work?\r\n\r\n\/\/ goodbye \\\\"}]

If i empty my message column and put in normal text without special signs it works fine, so I'm guessing that is the issue. I would be better however if a message could contain special characters and backslashes, is this possible or not?

Provide the exact data that causes issues. `json_encode` takes into account everything it needs. — zerkms, Dec 21 '14 at 22:09
Hi zerkms I have edited my post and added my json output, there are only 2 rows but both of them are causing issues. — Dresse, Dec 21 '14 at 22:32
"\r, \n and does not seem like valid json" --- what makes you thinking so? — zerkms, Dec 21 '14 at 23:04
@zerkms You are right, I validated it with a json validator and is correct. — Dresse, Dec 22 '14 at 22:18

score 0 · Answer 1 · edited May 23 '17 at 11:57

0

The \n and \r are valid and they are fine. They are hidden chartaters, they are stored this way in your database also so you have 2 options. Clean the database with the below function or clean the item before putting it into json with it.

function clean($string) {
   $string = str_replace(' ', '-', $string); // Replaces all spaces with hyphens.

   return preg_replace('/[^A-Za-z0-9\-]/', '', $string); // Removes special chars.
}

if you want to keep the special characters escape them Parsing JSON containing new line characters

edited May 23 '17 at 11:57

Community

1
1

answered Dec 21 '14 at 22:43

clonerworks

641
1
6
16

Thank you for the reply clonerworks that makes sense. – Dresse Dec 22 '14 at 22:06
How about characters like ë, ~, ä ? Whenever they are loaded from database the json_encode returns a whitepage. I tried setting mysql_set_charset('utf8'); right after mysql_connect – Dresse Dec 22 '14 at 22:13

PHP Json_encode - No json data on specific column MySQL

1 Answers1