0

I am trying to return an array result from a query - but I'm only getting the first row back.

The count returns 5 (which is the number of the rows that matches the query) - what am I missing in order to display those?

    public function getFailType($email)
{
    $q = $this->getEntityManager()->getConnection()->createQueryBuilder();
    $q->select('dnc.comments, count(dnc.reason) as total')
        ->from(MAUTIC_TABLE_PREFIX.'lead_donotcontact', 'dnc')
        ->where('dnc.channel_id = :channelId')
        ->groupBy('dnc.comments')
        ->setParameter('channelId', $email);

    $results = $q->execute()->fetchAll();
            $count = count($results);
    $dnc     = isset($results) ? $results[0] : null;

    if ($dnc === null) {
        return false;
    }

    return [
        'count' => $count,
        'total'        => $dnc['total'],
        'comments'     => $dnc['comments'],
    ];
}

Returns the following:

$printout->getFailType($objectId)['count'] = 5
$printout->getFailType($objectId)['total'] = 20
$printout->getFailType($objectId)['comments'] = Contact unsubscribed via email
openport
  • 1
  • 1
  • `$dnc = isset($results) ? $results[0] : null;` - that doesn't look fishy to you, at all? It's screaming "only first record or nothing". – N.B. Nov 09 '16 at 20:35
  • Oh, good catch! I removed it and now the original record is simply tripled. The other 4 rows still aren't seen. – openport Nov 09 '16 at 20:38
  • Yep, and that's because of your query, you're grouping by a column. If you want to select *different* 5 records and retrieve the count at the same time, do the count part in a subquery / `JOIN`. – N.B. Nov 09 '16 at 20:41
  • The comments column has 5 different rows, so in theory, it should display them all, no? (Outside of this platform, this query works: `select count(lead_id) as total, reason, comments from ".$parameters['db_table_prefix']."lead_donotcontact GROUP BY comments ORDER BY total desc` --not sure how to get the same to work within). – openport Nov 09 '16 at 20:44
  • Try to run your query in MySQL terminal and see what happens. Usually that's the best way to approach these types of problems - see what MySQL returns using its terminal and then if the same doesn't come through in PHP - you identified where the problem is. However, if Doctrine and MySQL yield the same thing - then it's the query. Good luck! – N.B. Nov 09 '16 at 21:05
  • Running via MySQL yielded success: Contact unsubscribed via email 20 There is a DNS loop 1 Unknown user or server 4 User unsubscribed. 1 Domain is Unknown 1 I think this is totally operator error. I'm missing something or made a typo, or perhaps even my foreach() is broken? (Is foreach() needed? New to Symfony) `foreach($printout->getFailType($objectId) as $fail): $html .= ' '.$printout->getFailType($objectId)['total'].' '.$printout->getFailType($objectId)['comments'].' '; endforeach;` – openport Nov 09 '16 at 21:12

0 Answers0