json_encode returning null with Postgres database and php

Question

I am referring to this question here on stackoverflow, and I have the same problem, except that I use postgre database and can't seem to get this working.

This is my php function which is querying the database:

public function getCashData($id, $date)
{
    if ($this->openConnection()){
        $query = "SELECT * FROM cash_register (". $id .", '". $date ."');";

        $result = pg_query($query);
        if (!$result){
                return false;
        }

        return pg_fetch_all($result);
    }
}

I call this function like this:

$cashReport = getCashReport($id, $date);

$cashReport = array_map('utf8_encode' , $casaReport); //**note: please read below
echo json_encode($casaReport);

**This was working perfectly when I was returning only one row of result (and not all like now), but now when I'm returning an array of rows this array_map function (which I found in the above mentioned link) is not working as it expects an array and not an array of arrays.

Can you guys help me solve this problem?

Why do you have to run your array through `utf8_encode`? I think `json_encode` will handle that. — Shef, Jul 27 '11 at 07:36
@Shef: actually, it won't because of the reasons explain in the link I gave in the question. Just to give you an example, I will get output: "colin":null,"colout":"1500". And you see this null? Well, this is giving me problems later in the script. I need this to be "colin":"","colout":"1500". So "" instead of null. — Nikola, Jul 27 '11 at 07:39
You don't solve your problems with more problems, but with solving what is causing it in the first place. Change your database collation to `UTF8` if you know you will be getting `utf8` characters. This will solve all the problems from the root. — Shef, Jul 27 '11 at 07:43
@Shef: actually, I am not having any utf8 characters in any of my tables, and also, to be noted, I have my database set to this collation: en_US.UTF-8. I just used that solution from the (again) above mentioned link and that worked, that's why I was trying it this time also. — Nikola, Jul 27 '11 at 07:56

score 1 · Answer 1 · answered Jul 27 '11 at 07:42

1

Try this:

function encode_items(&$item, $key)
{
    $item = utf8_encode($item);
}

array_walk_recursive($cashReport, 'encode_items');

answered Jul 27 '11 at 07:42

lazyhammer

1,228
1
11
14

score 0 · Answer 2 · answered Jul 27 '11 at 08:19

0

It's also possible to "tell" the postgre server to send the data utf-8 encoded.
see http://docs.php.net/pg_set_client_encoding

answered Jul 27 '11 at 08:19

VolkerK

95,432
20
163
226

thank you for your answer but this unfortunately didn't help. – Nikola Jul 28 '11 at 08:21

score 0 · Accepted Answer · answered Jul 28 '11 at 08:24

I did this in the end as I was forced to "fix" it asap:

$json = json_encode($cashReport);
$jsonEscapedNulls = str_replace('null', '""', $json);
echo $jsonEscapedNulls;

Now, I understand that "fixes" aren't solutions but I had no other choice in a give period of time :(. So if someone gives an answer which will work not as a fix but as a general solution I will be grateful. Until then, this will have to do.

json_encode returning null with Postgres database and php

3 Answers3