CODEWITHSUNDEEP
phpjson
Ryan Smith
Ryan Smith

Reputation: 509

PHP PDO fetchAll then json_encode doesn't work

I have a strange issue. I have a simple PHP script that uses PDO to get all countries from a database then returns the result as json. When I use the fetch function instead of fetchAll everything works as expected. When I print_r the data is there.

Doesn't work:

 $sql = "SELECT * FROM countries";
if($stmt = $_db->prepare($sql))
{
    $stmt->execute();
    $data = $stmt->fetchAll(PDO::FETCH_ASSOC);
    $stmt->closeCursor();

    header("content-type:application/json");

    echo json_encode($data);
    exit();

}

Works: 

 $sql = "SELECT * FROM countries";
if($stmt = $_db->prepare($sql))
{
    $stmt->execute();
    $data = $stmt->fetch(PDO::FETCH_ASSOC);
    $stmt->closeCursor();

    header("content-type:application/json");

    echo json_encode($data);
    exit();

}

Results of print_r:

Array
(
[0] => Array
    (
        [id] => 1
        [name] => Afghanistan
    )

[1] => Array
    (
        [id] => 2
        [name] => Åland Islands
    )

[2] => Array
    (
        [id] => 3
        [name] => Albania
    )
....
[248] => Array
    (
        [id] => 249
        [name] => Zimbabwe
    )

)
1

Upvotes: 5

Views: 14439

Answers (4)

Afrig Aminuddin
Afrig Aminuddin

Reputation: 782

Try to setup the charset at PDO initialization like this

$_db = new PDO("mysql:host=$host;dbname=$dbname;charset=UTF8;", $dbuser, $dbpass);

Note the end of the first parameter above charset=UTF8;

Upvotes: 2

Your Common Sense
Your Common Sense

Reputation: 157839

When you have a problem with JSON, there is a function json_error().

After learning that there is a problem with encoding, tell your database to return data in utf8:

$_db->query("SET NAMES utf8");
$data = $_db->query("SELECT * FROM countries")->fetchAll(PDO::FETCH_ASSOC);

header("content-type:application/json");
echo json_encode($data);
exit();

Upvotes: 4

Peter
Peter

Reputation: 59

try

$dbh = new PDO("mysql:host=$dbhost;dbname=$dbname", $dbuser, $dbpass, array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));

With:

array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8")

Upvotes: 5

huchister
huchister

Reputation: 27

fetchall documentation says retrieves an array of all fetch data simutaneously. In this case, It seems your fetchall function assigned result array into arraylist.

Here is the reference link below,

PDO fetch / fetchAll

Upvotes: -1

Related Questions