json_encode passes null values

Question

Code:

 <?php
$datoBuscar = $_GET["term"];

$conexion = mysql_connect("localhost", "root", "");
mysql_select_db("ap_contable");

$ssql = "SELECT provincia_id, nombre, codigopostal FROM ciudades WHERE codigopostal LIKE '%" . $datoBuscar . "%' LIMIT 50";
$rs = mysql_query($ssql, $conexion);

$arrayElementos = array();

while ($fila = mysql_fetch_array($rs)){

     echo $fila[provincia_id];

     $queryp = "SELECT nombre FROM provincias WHERE id = '$fila[provincia_id]' LIMIT 1";
     $resultp = mysql_query($queryp, $conexion);
     $foundp = mysql_fetch_array($resultp);

     echo $foundp['nombre'];

     $row['prov'] = $foundp['nombre'];
     $row_set[] = $row;

}
echo json_encode($row_set);
?>

Example: http://localhost/Contable/autocompletecod.php?term=3269

The output of this is as follows

6Entre Ríos6Entre Ríos6Entre Ríos6Entre Ríos6Entre Ríos6Entre Ríos6Entre Ríos6Entre Ríos6Entre Ríos6Entre Ríos6Entre Ríos6Entre Ríos6Entre Ríos6Entre Ríos6Entre Ríos6Entre Ríos6Entre Ríos[{"prov":null},{"prov":null},{"prov":null},{"prov": null},{"prov":null},{"prov":null},{"prov":null},{" prov":null},{"prov":null},{"prov":null},{"prov":nu ll},{"prov":null},{"prov":null},{"prov":null},{"pr ov":null},{"prov":null},{"prov":null}]

I put the ECHO intentionally to see if it is taking the data from the MySQL database and if it gets it. The problem is that when I assign it to the array $row['prov'] = $foundp['nombre']; Where is the problem? Where it says NULL should say Entre Rios

**WARNING**: If you're just learning PHP, please, do not use the [`mysql_query`](http://php.net/manual/en/function.mysql-query.php) interface. It’s so awful and dangerous that it was removed in PHP 7. A replacement like [PDO is not hard to learn](http://net.tutsplus.com/tutorials/php/why-you-should-be-using-phps-pdo-for-database-access/) and a guide like [PHP The Right Way](http://www.phptherightway.com/) explains best practices. Your user data is **not** [properly escaped](http://bobby-tables.com/php.html) and there are [SQL injection bugs](http://bobby-tables.com/) and can be exploited. — tadman, Aug 29 '17 at 17:34
If you use PDO and placeholder values that will fix your string interpolation bug on your second query. — tadman, Aug 29 '17 at 17:57

ishegg · Answer 1 · 2017-08-29T17:47:07.473

1

Your strings need to be UTF-8 encoded (that's a json_encode() requirement). Try this:

$row['prov'] = utf8_encode($foundp['nombre']);

Alternatively, you can set the CHARACTER SET to utf8mb4 in the MySQL connection. Ideally, you should have it all on UTF-8. Read this.

edited Aug 29 '17 at 17:47

answered Aug 29 '17 at 17:36

ishegg

9,685
3
16
31

Glad I could help. Don't forget to mark the question as resolved by accepting the answer that helped you most. – ishegg Aug 29 '17 at 17:50
This?? – Fernando Rossi Aug 29 '17 at 18:04
It's okay like this Excuse my English, I'm from Argentina and I translate with google. – Fernando Rossi Aug 29 '17 at 18:12

score 0 · Answer 2 · answered Aug 29 '17 at 17:49

Uh, your code is just wrong for so many reasons:

mysql_* is deprecated http://php.net/manual/en/function.mysql-connect.php use PDO instead
Unprotected $datoBuscar variable passed to SQL https://en.wikipedia.org/wiki/SQL_injection
Unused $arrayElementos
You should JOIN those two tables like: SELECT p.nombre FROM provincias AS p JOIN ciudades AS c ON p.id = c.provincia_id WHERE c.codigopostal LIKE '%" . $datoBuscar . "%' https://dev.mysql.com/doc/refman/5.7/en/join.html
$row_set is not defined
$row is not defined
ending ?> should NOT be used in pure PHP files Why would one omit the close tag?

So, it should look like this (untested code):

$datoBuscar = $_GET["term"];
$db = new PDO('mysql:host=localhost;dbname=ap_contable', 'root', 'PASSWORD', [PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES 'utf8'"]);
$query = $db->prepare('SELECT p.nombre FROM provincias AS p JOIN ciudades AS c ON p.id = c.provincia_id WHERE c.codigopostal LIKE \'%?%\'');
$query->execute([$datoBuscar]);
$results = $query->fetchAll();
$json = [];
foreach($results AS $result)
{
   $row = [];
   $row['prov'] = $result['nombre'];
   $json[] = $row;
}
echo json_encode($json);

PS: If you want to write good PHP code, enable notice and warning error reporting, you will be amazed what you missed and "How this code worked in first place?!"

score 0 · Answer 3 · answered Aug 29 '17 at 17:50

To assign the $row array into $row_set array use this

$row_set = $row;

this way you will get everything of your $row array in $row_set. And whenever you are running a loog and you are setting the value of $row_set at the same index then you will only get the last loop execution value in your array. If you want all the results then you can use array_push(); description from php manual int array_push ( array &$array , mixed $value1 [, mixed $... ] ). In your case you can use

array_push($row_set, $row);

Instead of

$row_set[] = $row;

json_encode passes null values

3 Answers3