error using json_encode on a string returned from a mySQL query

Question

I am trying to pass an array which is the result of a mySQL PDO query from php to javascript using json_encode.

However if one of the fields contains spaces, or, if I try to use a database function on the string (to select only the first non-space part), the object seems to be nonexistent, and my code does not compile.

Following is the executed code:

<?php
  $sql = "SELECT id, substring_index(firstNames, ' ', 1) as firstName, lastName, gender, idPersonFather, idPersonMother
  FROM a_person";

  $result = $db->query($sql);
  while ($row = $result->fetch(PDO::FETCH_ASSOC)){ 

  $personArray[$row['id']] = Array(     'firstName' => $row['firstName']
                                    , 'lastName' => $row['lastName']
                                    , 'gender' => $row['gender']
                                    , 'idFather' => $row['idPersonFather']
                                    , 'idMother' => $row['idPersonMother']);
}
?>

<html>
...
<script type="text/javascript" src="js/includes/grid_classes.js">

  var personArray=<?php echo json_encode($personArray); ?>//get php aray
  var someNextStuff
  ...etc

This will result in the error Uncaught SyntaxError: Unexpected token var because the line var someNextStuff comes unexpected, apparently the object is translated into an empty string?

Also, the following select causes the same problem:

$sql = "SELECT id, firstNames, lastName, gender, idPersonFather, idPersonMother
FROM a_person";

with firstNames containing values like 'Maria Anna Cecilia', and NULLs.

The SQL queries work on the database directly however (in phpmyadmin).

However, if I leave out the firstNames field from the select and the array, the code is working correctly.

What could be causing this?

thanks for your help..

Edit:

I now found that

SELECT id, substring_index(coalesce('Maria anna blah','?'), ' ', 1) as firstName, <rest of fields>

works, but

SELECT id, substring_index(coalesce(firstNames,'?'), ' ', 1) as firstName, <rest of fields>

does not.

So it has to be something with that field, I guess..

It looks like you're mixing PHP and JavaScript without appropriately separating them with <?php and ?> tags, which is what's causing that syntax error... — Hayden Schiff
– Hayden Schiff, Commented Sep 14, 2016 at 3:31
Sorry, I left out the separators in the example - now added.. as mentioned, the code does compile and work properly as long as i leave out the field containing spaces. — Rusty75
– Rusty75, Commented Sep 14, 2016 at 3:39
Oh, okay, well then it looks like you need a semicolon right after <?php echo json_encode($personArray); ?> in the JavaScript. — Hayden Schiff
– Hayden Schiff, Commented Sep 14, 2016 at 3:40
I use as a coding standard no semicolons in javascript... As mentioned, the code does compile and work except when I add that field that contains spaces :) — Rusty75
– Rusty75, Commented Sep 14, 2016 at 3:44
I'd assume that something with your query might be wrong, when no rows are produced the json_encode is naturally empty. You should probably check $result === false and in that case output the error message in $db->errorInfo() — Jakumi
– Jakumi, Commented Sep 14, 2016 at 8:18

Moshe Katz · Accepted Answer · 2016-09-15 00:05:03Z

1

If you look at the output of json_last_error_msg (or just json_last_error if you are using an old PHP version, in which case you will only get an integer that you need to match to a constant), you will get one of these errors:

Control character error, possibly incorrectly encoded

or

Malformed UTF-8 characters, possibly incorrectly encoded

The two weird characters you found (Ã and unprintable) are what happens when you try to store UTF-8 data in a database that is not using UTF-8 encoding.

You can try to work around this by calling utf8_decode on each piece of data that might have unicode characters.

However, the best way to fix it is to make sure that your database is using UTF-8.

edited Sep 15, 2016 at 0:05

answered Sep 14, 2016 at 23:57

Moshe Katz

16.5k7 gold badges71 silver badges127 bronze badges

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

1 Comment

Rusty75 Over a year ago

Hi, thanks for the helpful comments. Collation of the database is utf8_general_ci, however I do not think the HEX code C289 is anything in any character set that fits within UTF8. It is data that I exported from a family tree software program in GEDCOM format and I wrote a package to process a GEDCOM file and populate the mysql database. It may be that I overlooked setting UTF8 somewhere but I suspect that the data landed in the file incorrectly already. In all cases, I would only expect common characters so it is most likely dirty data...

Rusty75 · Accepted Answer · 2016-09-14 23:39:16Z

0

OK, I seems that it was indeed a false value in one of the rows in that field! (thanks Jakumi)

Patiently added more rows to the array from an ordered select until it failed, and that happens when adding the record with firstnames Ãline. Copy Pasting I just found out that there is an unreadable character after The Ã...0089 whatever that is... HEX c289 However just removing the value after the Ã character was not enough, it seems that my character set is maybe too limited. However I suspect this is dirty data, so it will go back to the source instead of me expanding the character set..

answered Sep 14, 2016 at 23:39

Rusty75

5257 silver badges25 bronze badges

Collectives™ on Stack Overflow

error using json_encode on a string returned from a mySQL query

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related