SELECT with CASE runs in phpMyAdmin but not passing in PHP

Question

SELECT with CASE runs in phpMyAdmin but not passing in PHP

Navigation

#1 by (1 votes)
#2 by (1 votes)

2

I have a problem with a SQL that runs perfectly in phpMyAdmin, but does not rotate passing 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 $query variable 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

asked by anonymous 14.04.2015 / 16:24

2 answers

My program has a performance problem Send email with attachment on Lua

score 1 · Answer 1

The problem is that you are missing spaces from one line to the other, just as is $query gets:

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

Adding the 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";

score 1 · Answer 2

Space is missing, string concatenation is generating things like tipoWHEN . I believe you could use a less error-prone syntax, 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;