1

I am having a users table with Collation as utf8_general_ci. Having fields like name, age, phone and email.

It seems some of the rows of name field is having some trailing spaces. If echo-ed echo "selectedUser " . $selectedUser->name; It is printing that name with spaces and I just copied the name from DB field and pasted it on a php file, and echo-ed that text, it was printing like

selectedUser "Rohan Harish Reddy\u00a0\u00a0"

name field contains only trailing white-spaces on the field, I am not sure

why it is printed like this ? Why it is not working for json_encode ? How can I convert this user object with json_encode ?

Code:

$users = $stmt->fetchAll(PDO::FETCH_OBJ);

foreach ($users as $user) { 

    $selectedUser = $user;

    echo "selectedUser " . json_encode($selectedUser);

}

If add below code above the fetch all statement, It is working fine. 

$this-> adminConn ->query("SET NAMES utf8");
$stmt = $this-> adminConn ->query($sql);

Do I need to set "SET NAMES utf8" for each query ? Is there a common way to achieve it ?

Muthu
  • 1,550
  • 10
  • 36
  • 62

1 Answers1

1

Because you don't have spaces (Unicode 0x0020), you have non-breaking spaces (Unicode 0x00A0). JSON standard (RFC 7159) declares that all Unicode characters may be escaped, and json_encode is using this to escape the non-breaking spaces (to distinguish them to a human reader from normal spaces, I would assume). The solution is probably to figure out why you have trailing non-breaking spaces in your data.

Community
  • 1
  • 1
Amadan
  • 191,408
  • 23
  • 240
  • 301
  • When we upload csv files, It is happening. Unicode 0x00A0 is inserted. Any way to avoid it while parsing the uploaded file and avoiding it on json_encode ?? – Muthu Feb 02 '16 at 01:29
  • I don't know what your CSV looks like, or how you are parsing it, or how you sanitise (or not) your values. Avoiding it on `json_encode` is simple, just replace all nbsps with regular spaces, or delete them outright, using `preg_replace`, before `json_encode`. – Amadan Feb 02 '16 at 01:31
  • If name is having any non-breaking spaces, that particular object is not printed with json_encode($selectedUser) due to this non-breaking spaces. – Muthu Feb 02 '16 at 01:31
  • I do not want to replace that non-breaking spaces, Whatever content that is available on the DB fields need to be returned. If add below code above the fetch all statement, It is working fine. $this-> adminConn ->query("SET NAMES utf8"); $stmt = $this-> adminConn ->query($sql); Do I need to set "SET NAMES utf8" for each query ? Is there a common way to achieve it ? – Muthu Feb 02 '16 at 01:33
  • If you need a JSON representation of the exact content in the database, then you have the correct value - `"Rohan Harish Reddy\u00a0\u00a0"` is a valid JSON for a string with two trailing nbsps. I suppose I do not understand your question. (You might also read through [answers here](http://stackoverflow.com/questions/1650591/whether-to-use-set-names) to see arguments why `SET NAMES utf8` is generally not a good idea.) – Amadan Feb 02 '16 at 01:41