I try to get the data from a query and print it to the screen but it doesn't show anything.
$pdo=new PDO("mysql:dbname=usuario;host=localhost","root","root");
$statement=$pdo->prepare("SELECT * FROM usuario WHERE estado = 1 ORDER BY idusuario desc;");
$statement->execute();
if (!$statement){
echo 'Error al ejecutar la consulta';
}{
echo 'intentando obtener registros <br>';
while ($results = $statement->fetchAll(PDO::FETCH_ASSOC)){
$arreglo['data'][]= $results;
}
echo json_encode($arreglo);
}
Investigating I found that it may be due to the character encoding so supposedly adding array_map $arreglo['data'][] = array_map("utf8_encode", $results);
would solve the problem, after adding array_map at least it shows something .. it shows null values this appears:
{"data":[[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null]]}
I'll show how to do it both in PDO and MySQLi.
1. Using PDO
If you use the method
fetchAll
in PDO. You no longer need to read the data inside a loop, since this method:So the code could look like this:
As the Manual says, the code will store in the variable
$results
all the results when usingfetchAll
. In addition, it will store them in an associative array that, in the json, will correspond to a key/value data set.2. Using MySQLi
MySQLi has a method
fetch_all
, but it is only available if you have the driver installedmysqlnd
. Since we are not sure about this, and since we do not know if your code could in the future be executed on a computer that does not have that driver, we will better use a code that is valid for any system.We will retrieve the data with the method
fetch_assoc
, then we will read it inside a loop to create the array with all the rows.3. About the character set
Sometimes working with JSON gives errors because the data is coming in badly encoded because an appropriate character set was not established when creating the connection.
To set the character set in PDO
If you want to do it after you've created the connection object, you can do this:
If you want to do it in the constructor, you pass it in an array, at the end of the constructor (see link at the end of this section).
To set the character set in MySQLi
You can also do it after the object is created:
Or indicate it in the constructor.
For more details about the encoding problem, you can consult the answer to this question: Why does the Checker Character (�) appear in some data obtained from the Database?
The error is because json_encode() can't process certain characters, try running the following query before querying your data:
Your code would be like this,