Doctrine prepare-execute

1

Hello, I'm doing this query with Doctrine

$retorno = $em->getConnection()->prepare("
        SELECT
        t.id_programa_fidelidade_diario,
        t.loja,
        t.nota,
        t.data,
        t.ecf,
        t.operador,
        o.nome,
        programa_fidelidade_calculo_status(t.id)
        AS calculo_status,
        programa_fidelidade_calculo_valor(t.loja, t.Nota, t.Ecf, t.Data)
        AS calculo_bonus_valor
        FROM sef s,
        totcupom t
        LEFT JOIN operador o
        ON (t.operador=o.codigo)
        WHERE (t.loja=s.id)
        AND (t.data BETWEEN '2016-01-01' AND '2016-12-30') LIMIT 1")->execute();

But run it returns only a bool, and I need the query result back. How can I do it?

Thank you

    
asked by anonymous 12.03.2018 / 21:50

1 answer

1

Since you are using prepare f of the PDO ( because Doctrine is made with PDO ), then make these changes to your code:

$stmt = $em->getConnection()->prepare("
        SELECT
        t.id_programa_fidelidade_diario,
        t.loja,
        t.nota,
        t.data,
        t.ecf,
        t.operador,
        o.nome,
        programa_fidelidade_calculo_status(t.id)
        AS calculo_status,
        programa_fidelidade_calculo_valor(t.loja, t.Nota, t.Ecf, t.Data)
        AS calculo_bonus_valor
        FROM sef s,
        totcupom t
        LEFT JOIN operador o
        ON (t.operador=o.codigo)
        WHERE (t.loja=s.id)
        AND (t.data BETWEEN '2016-01-01' AND '2016-12-30') LIMIT 1");
$stmt->execute();
$results = $stmt->fetchAll(); // ou ->fetch();

This would be what you need in your code, but since it has no parameter, do you need to prepare this SQL ?

Could simplify:

$results = $em->getConnection()->query("
        SELECT
        t.id_programa_fidelidade_diario,
        t.loja,
        t.nota,
        t.data,
        t.ecf,
        t.operador,
        o.nome,
        programa_fidelidade_calculo_status(t.id)
        AS calculo_status,
        programa_fidelidade_calculo_valor(t.loja, t.Nota, t.Ecf, t.Data)
        AS calculo_bonus_valor
        FROM sef s,
        totcupom t
        LEFT JOIN operador o
        ON (t.operador=o.codigo)
        WHERE (t.loja=s.id)
        AND (t.data BETWEEN '2016-01-01' AND '2016-12-30') LIMIT 1")
->fetchAll(); // ou ->fetch();

13.03.2018 / 02:41