Problem returning data with Mysql PDO

Question

I have a query that returns the permissions of a certain group of users of my system. I use Mysql with PHP. My SQL command is as follows:

select
    p.Codigo, p.Descricao,
    (case when gp.CodGrupoUsuario is null then 0 else 1 end) as TemPermissao
from permissao p
left outer join grupopermissao gp
  on gp.CodPermissao = p.Codigo
 and gp.ativo = 1
 and gp.CodGrupoUsuario = :pCodGrupo

In my mysql database (5.6.21) I have two permissions for a group I am testing:

• 1 - Access the system: 1
• 2 - Register permissions: 0

If I execute the command in the database, everything works perfectly. When I run my PHP application, I am using PDO (mysqlnd 5.0.11-dev - 20120503) and return my data using "fetchAll(PDO::FETCH_OBJ)". In PHP, the data is returned, but the permission indicator (1 or 0) is always returning 0, as shown below:

Array (
  [0] => stdClass Object (
    [Codigo] => 1
    [Descricao] => Acessar o sistema
    [TemPermissao] => 0 )
  [1] => stdClass Object (
    [Codigo] => 2
    [Descricao] => Cadastrar permissões
    [TemPermissao] => 0 ) )

When instantiating my connection to the database I am using:

• PDO::ATTR_EMULATE_PREPARES = false
• PDO::ATTR_STRINGIFY_FETCHES = false

Can anyone tell me why my "Tempermissao" column is not returning the correct value? I’ve tried everything and nothing worked.

Thanks for your attention.

Answer 1

I removed the CASE and the OUTER, being like this:

SELECT
    p.Codigo, p.Descricao,
    if(gp.CodGrupoUsuario is null, 0, 1) as TemPermissao
FROM permissao p
LEFT JOIN grupopermissao gp  ON 
    gp.CodPermissao = p.Codigo 
    and gp.ativo = 1 
    and gp.CodGrupoUsuario = :pCodGrupo