MySQL to JSON using PHP (accents i think)

Question

I have a problem with my PHP code that will look for an entire SQL table, and then display it in JSON format.

Here, the PHP code :

<?php
*CONFIG MYSQL*
    $lat=$_GET["lat"];
    $lng=$_GET["lng"];
      $dblink = new mysqli($dbhost, $dbuser, $dbpass, $dbname);

    $command = "SELECT *, 111.045 * DEGREES(ACOS(COS(RADIANS($lat))
     * COS(RADIANS(app_lieux.lat))
     * COS(RADIANS(app_lieux.lng) - RADIANS($lng))
     + SIN(RADIANS($lat))
     * SIN(RADIANS(app_lieux.lat))))
     AS distance_in_km
    FROM app_lieux
    ORDER BY distance_in_km ASC
    LIMIT 0,5";

      $result = $dblink->query($command);
      $dbdata = array();
      while ( $row = $result->fetch_assoc())  {
        $dbdata[]=$row;
      }
    echo json_encode($dbdata);
    ?>

When i try to go to the script, i have a blank page without error. http://notaire-gilles-france.be/charleroi2/lieux.php?lat=50.44348&lng=4.43840

What's the problem? Thank you very much for your help !

Is `*CONFIG MYSQL*` part of your code? Turn on error reporting. — WillardSolutions, Feb 13 '17 at 23:37
Difficult to say, if you get a blank page most probably you have PHP errors hidden. Check here on how to enable it http://stackoverflow.com/questions/1053424/how-do-i-get-php-errors-to-display — Henrique B., Feb 13 '17 at 23:47
I change the php.ini and the top of the script but showing nothing :( the code is working with different longitude [link](http://notaire-gilles-france.be/charleroi2/lieux.php?lat=50.44348&lng=4.10) — Alexis Giuseppe, Feb 13 '17 at 23:52
**WARNING**: When using `mysqli` you should be using [parameterized queries](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) and [`bind_param`](http://php.net/manual/en/mysqli-stmt.bind-param.php) to add user data to your query. **DO NOT** use string interpolation or concatenation to accomplish this because you have created a severe [SQL injection bug](http://bobby-tables.com/). **NEVER** put `$_POST` or `$_GET` data directly into a query, it can be very harmful if someone seeks to exploit your mistake. — tadman, Feb 14 '17 at 00:15
Also if you're using lat/long coordinates in MySQL try the [Spatial Extensions](https://dev.mysql.com/doc/refman/5.7/en/spatial-extensions.html) which make this a lot easier. Another thing to keep in mind is [PostGIS](http://postgis.net) for Postgres is exceptionally good by comparison and worth checking out. — tadman, Feb 14 '17 at 00:16
@tadman : Thank you :-) i will correct that later. How can i have the result ? — Alexis Giuseppe, Feb 14 '17 at 00:17
SQL Injection is not working anymore now :) but my script is not working ahahaha — Alexis Giuseppe, Feb 14 '17 at 00:31
"Not working" is a sign you need to do more digging. An error message, a description of the reults, anything is better. A good place to start is [turning on exceptions](http://stackoverflow.com/questions/23816633/mysqli-not-throwing-errors-why). — tadman, Feb 14 '17 at 00:51
Yes, i understand but i try to show errors (on 2 servers differents), this code hasn't error. i try 'php -l lieux.php' there is not syntax error. — Alexis Giuseppe, Feb 14 '17 at 00:59

score 1 · Answer 1 · answered Feb 14 '17 at 00:59

1

I don't see anything in the code that explains the behavior. (The comments about SQL Injection are valid; and about making sure error reporting is enabled, and all that.)

The prime suspect is the call to json_encode. My suspicion is that this is encountering an issue, returning FALSE.

For debugging...

I'd try commenting out the call to json_encode. And do a var_dump($dbdata) instead, to see if you get back an array of more than one element.

I'd also suggest inspecting the return from json_encode, before echoing it. Assign the return from json_encode to a variable. And test to see if it's FALSE, and var_dump the variable.

I'd do both.

I tested the link provided. With various values for lat and lng, I was able to get the browser (Chrome) View page source to show:

[{"id":"20","nom":"Monument ARIELLE CARLIER","lat":"50.413807","lng":"4.425867",...

And another time,

...,"nom":"Chariot Minier","lat":"50.406054","lng":"4.463691",...

But I only ever got back one row. Given the two different results, it seems like the table contains at least two entries, so I would expect to get back an at least two rows.

My guess (and it's just a guess) is that there's an encoding issue data being retrieved from the query, and json_encode is balking. I'm guessing that the two times I was able to get data back, json_encode managed to get through the first row in the array, before it upchucked.

answered Feb 14 '17 at 00:59

spencer7593

106,611
15
112
140

There is a LIMIT 1. It is normal. I'm trying to commenting json_encode. – Alexis Giuseppe Feb 14 '17 at 01:03
My observation was based on the code in the question showing a limit of five rows **`LIMIT 0,5`**. The great circle calculation expression seems valid, and I tested the query on MySQL 5.1, 5.6 and 5.7. – spencer7593 Feb 14 '17 at 01:03
Ok, var_dump($dbdata) is correctly working. So what's the problem with the json_encode? – Alexis Giuseppe Feb 14 '17 at 01:04
I think `json_encode` is expecting a valid UTF8 encoding. My guess is your data is is moji-baked. I suspect you've got latin1 encoding for some special characters, maybe "À Vélo". Somewhere along the line, an encoding has been misrepresented... – spencer7593 Feb 14 '17 at 01:06
What do you mean by "moji-baked"? haha sorry. – Alexis Giuseppe Feb 14 '17 at 01:09
All of my database is in UTF8. – Alexis Giuseppe Feb 14 '17 at 01:11
Google "mojibake". And see https://www.joelonsoftware.com/2003/10/08/the-absolute-minimum-every-software-developer-absolutely-positively-must-know-about-unicode-and-character-sets-no-excuses/ and http://kunststube.net/encoding/ – spencer7593 Feb 14 '17 at 01:11
All of your database is in UTF8. But did anyone insert an invalid UTF8 encoding into your database? Someone may have *lied* to MySQL and told MySQL "this data I'm inserting is UTF8", and MySQL obediently and efficiently stored the data. But it wasn't *really* UTF8. Now when it's being queried, what MySQL is returning, is NOT *valid* UTF8. (MySQL doesn't care. But `json_encode` cares. – spencer7593 Feb 14 '17 at 01:16
Ok thank you very much for your help @spencer7593 and others ! – Alexis Giuseppe Feb 14 '17 at 01:17

score 0 · Accepted Answer · answered Feb 15 '17 at 23:53

0

I just find the answer :

$utf8 = utf8_encode($result);
$json = json_encode($utf8);

It was very simple.

answered Feb 15 '17 at 23:53

Alexis Giuseppe

99
10

The `utf8_encode` function should be called only on data that is ISO-8859-1 encoded. A comment you left on my answer reported: **"All of my database is in UTF8"**, so this must not be true. Either your database connection is not utf8, or the data stored in your columns is not utf8. If your data is encoded latin1, and your database connection is latin1, then this an appropriate solution. If, however, the data in your database is utf8 encoded, and your database connection is utf8, then along with being very simple, this is also a very *wrong* solution. – spencer7593 Feb 17 '17 at 16:20
Future readers: https://www.joelonsoftware.com/articles/Unicode.html and http://kunststube.net/encoding/ – spencer7593 Feb 17 '17 at 16:21

MySQL to JSON using PHP (accents i think)

2 Answers2