CODEWITHSUNDEEP
phpmysqljson
Fernando Rossi
Fernando Rossi

Reputation: 39

json_encode passes null values

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

Upvotes: 0

Views: 801

Answers (3)

MNI Noyon
MNI Noyon

Reputation: 98

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;

Upvotes: 0

Salamek
Salamek

Reputation: 73

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

  1. mysql_* is deprecated http://php.net/manual/en/function.mysql-connect.php use PDO instead
  2. Unprotected $datoBuscar variable passed to SQL https://en.wikipedia.org/wiki/SQL_injection

  3. Unused $arrayElementos

  4. 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

  5. $row_set is not defined

  6. $row is not defined

  7. 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?!"

Upvotes: 0

ishegg
ishegg

Reputation: 9937

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.

Upvotes: 1

Related Questions