SELECT with CASE runs on phpMyAdmin but does not pass in PHP

Asked

Viewed 473 times

2

I have a problem with an SQL that runs perfectly in phpMyAdmin, but does not run the query to the database by PHP.

Query copied from phpMyAdmin’s SQL field:

SELECT `idforma_pagamento`, `habilitado`, `descricao`,
CASE forma_pagamento.tipo
WHEN 0 
THEN  'A PRASO'
WHEN 1 
THEN  'A VISTA'
END AS tipo,

CASE forma_pagamento.entrada
WHEN 0 
THEN  'SIM'
WHEN 1 
THEN  'NÃO'
END AS entrada
FROM forma_pagamento
WHERE 1

Same query passed to variable $query in PHP:

"SELECT `idforma_pagamento`, `habilitado`, `descricao`,"
            ."CASE forma_pagamento.tipo"
            ."WHEN 0" 
            ."THEN  'A PRASO'"
            ."WHEN 1" 
            ."THEN  'A VISTA'"
            ."END AS tipo,"

            ."CASE forma_pagamento.entrada"
            ."WHEN 0" 
            ."THEN  'SIM'"
            ."WHEN 1" 
            ."THEN  'NÃO'"
            ."END AS entrada"
            ."FROM forma_pagamento"
            ."WHERE 1";

Someone there can help me

mysql

  • You’re missing spaces for the other lines, otherwise it’s all stuck together CASE forma_pagamento.tipoWHEN 0 just as it is.

  • 1

    Term detail instead of PRASO ;)

2 answers

1

The problem is you’re missing spaces from one line to another, just like $query stays:

SELECT `idforma_pagamento`, `habilitado`, `descricao`,CASE forma_pagamento.tipoWHEN 0...

Adding spaces to each line already works:

"SELECT `idforma_pagamento`, `habilitado`, `descricao`, "
        ."CASE forma_pagamento.tipo "
        ."WHEN 0 " 
        ."THEN  'A PRASO' "
        ."WHEN 1 " 
        ."THEN  'A VISTA' "
        ."END AS tipo, "

        ."CASE forma_pagamento.entrada "
        ."WHEN 0 " 
        ."THEN  'SIM' "
        ."WHEN 1 " 
        ."THEN  'NÃO' "
        ."END AS entrada "
        ."FROM forma_pagamento "
        ."WHERE 1";

  • Thank you guys. The two alternatives worked

  • 1

    @Reinaldoanício if the two answers helped you, choose what you think is right and mark as right with the visa next to the answer.

1

Spaces are missing, string concatenation is generating things like tipoWHEN. believe that you could use a syntax less prone to errors, for example:

<?php   
$query = "
SELECT `idforma_pagamento`,
       `habilitado`,
       `descricao`,
       CASE forma_pagamento.tipo
       WHEN 0 THEN 'A PRASO'
       WHEN 1 THEN 'A VISTA'
       END AS tipo,
       CASE forma_pagamento.entrada
       WHEN 0 THEN 'SIM'
       WHEN 1 THEN 'NÃO'
       END AS entrada
FROM forma_pagamento
WHERE 1";

Or

$query = <<<SQL
SELECT `idforma_pagamento`,
       `habilitado`,
       `descricao`,
       CASE forma_pagamento.tipo
       WHEN 0 THEN 'A PRASO'
       WHEN 1 THEN 'A VISTA'
       END AS tipo,
       CASE forma_pagamento.entrada
       WHEN 0 THEN 'SIM'
       WHEN 1 THEN 'NÃO'
       END AS entrada
FROM forma_pagamento
WHERE 1
SQL;

Browser other questions tagged mysql

You are not signed in. Login or sign up in order to post.