PHP PDO fetchAll then json_encode doesn't work

Question

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

What does the first code do? Which piece of code is that print_r from? Is it the same in both? — andrewsi
– andrewsi, Commented Feb 1, 2015 at 22:00
As you can see, fetch() and fetchAll() return different sets of data. It's hard to tell what you're asking. — Leo Galleguillos
– Leo Galleguillos, Commented Feb 1, 2015 at 22:31
I should have been clearer with my question. I understand that fetch returns the next row, this works perfectly when I use json_encode and return the data. I get the next row in json format. The problem is when I use fetchAll to get all the countries from the database. I can use print_r to see the results of the query and everything is as expected but when I use json_encode I get a blank screen. This makes no sense to me. — Ryan Smith
– Ryan Smith, Commented Feb 3, 2015 at 16:52

GuilhE · Accepted Answer · 2015-08-26 12:53:33Z

5

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")

edited Aug 26, 2015 at 12:53

GuilhE

11.9k17 gold badges82 silver badges122 bronze badges

answered Aug 26, 2015 at 12:44

Peter

591 silver badge3 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Pablo Ruan Over a year ago

i m using new PDO('firebird:dbname='.$dados_sql['ip'].':'.$dados_sql['alias'].';charset=utf8',$pwEuser['pw'], $pwEuser['user']); and FECH_ASSOC, not FETCH_OBJ

Your Common Sense · Accepted Answer · 2015-08-26 12:56:52Z

4

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();

answered Aug 26, 2015 at 12:56

Your Common Sense

158k42 gold badges226 silver badges373 bronze badges

Comments

Afrig Aminuddin · Accepted Answer · 2016-10-18 04:19:09Z

2

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;

answered Oct 18, 2016 at 4:19

Afrig Aminuddin

7821 gold badge9 silver badges22 bronze badges

Comments

Community · Accepted Answer · 2017-05-23 10:30:20Z

-1

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

edited May 23, 2017 at 10:30

CommunityBot

11 silver badge

answered Feb 11, 2015 at 18:45

huchister

271 bronze badge

Collectives™ on Stack Overflow

PHP PDO fetchAll then json_encode doesn't work

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related