Mysql - CASE WHEN, the third condition does not return the THEN

Asked

Viewed 34 times

1

I made the QUERY a CASE to display in 'Data' output if current, expired or pending, but for pending has no output when printa.

$sql_check_plano = "SELECT 
    *,
    (
    SELECT 
        `ps_nome` 
    FROM 
        `plano_pack` 
    WHERE 
        `ps_token` = `pu_ps_token` 
    LIMIT 
        1
    ) AS `plano_nome`,
    (
    SELECT
        CASE 
            WHEN 
                TIMEDIFF(`pu_data_expira`,NOW()) < 0 THEN 'Expirado' 
            WHEN 
                TIMEDIFF(`pu_data_expira`,NOW()) > 0 THEN 'Vigente' 
            WHEN 
                `pu_data_expira` = '0000-00-00 00:00:00' THEN 'Pendente'
        END
    ) AS `Data`
FROM 
    `plano_usuario` 
WHERE 
    `pu_user_token` = '" . $_SESSION['usuario']['user_token'] . "' 
ORDER BY 
    `pu_data_cad` 
        DESC 
;";
php mysql

  • Have you checked if NO_ZERO_DATE mode is enabled in your settings? If you are testing with IS NULL.

  • in the migration coming to the production environment do not know how will be the settings, so I am avoiding using resources that depend on custom configuration, but worked as you suggested, this no_zero_date was unaware.

1 answer

0


I entered on condition that the date is also different from "000-00-00 00:00:00" and if it is not < or > then it is an undefined date, then I entered the ELSE 'Pending', I do not know if it is the best solution, but returned as expected.

$sql_check_plano = "SELECT 
    *,
    TIMEDIFF(`pu_data_expira`,NOW()) AS xxx,
    (
    SELECT 
        `ps_nome` 
    FROM 
        `plano_pack` 
    WHERE 
        `ps_token` = `pu_ps_token` 
    LIMIT 
        1
    ) AS `plano_nome`,
    (
    SELECT
        CASE 
            WHEN 
                TIMEDIFF(`pu_data_expira`,NOW()) < 0 AND `pu_data_expira` != '0000-0000-00 00:00:00' THEN 'Expirado' 
            WHEN 
                TIMEDIFF(`pu_data_expira`,NOW()) > 0 AND `pu_data_expira` != '0000-0000-00 00:00:00' THEN 'Vigente' 
            ELSE 'Pendente'
        END
    ) AS `Data`
FROM 
    `plano_usuario` 
WHERE 
    `pu_user_token` = '" . $_SESSION['usuario']['user_token'] . "' 
ORDER BY 
    `pu_data_cad` 
        DESC 
;";

Browser other questions tagged php mysql

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