Doctrine prepare->execute

Question

Hi, I’m doing this consultation 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();

However the execute returns me only one bool, and I need to return the result of the query. How can I do it?

Thank you

Answer 1

How are you using the prepare of the PDO (because Doctrine is made with PDO), then make these modifications 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, as there is no parameter, will 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();

_Reference:

• Replacing the PDO with Doctrine DBAL
• Simplifying database interactions with Doctrine DBAL