Problem returning data with PDO from MySQL

Question

Problem returning data with PDO from MySQL

Navigation

#1 by (0 votes)

1

I have a query that returns the permissions of a certain group of users on 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'm testing:

1 - Access the system: 1
2 - Issue permissions: 0

If I run the command in the database, everything works perfectly. When I run my PHP application, I'm 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 "Suspend" column is not returning the correct value? I tried everything and nothing worked.

Thanks in advance for your attention.

php mysql pdo

asked by anonymous 12.08.2017 / 23:55

1 answer

How to display listing of the same data? Leave the video occupying 100% of the screen with fixed height

score 0 · Answer 1

I removed CASE and OUTER , thus:

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