开发者

Display MySQL "GROUP BY" results correctly

I cannot seem to get this query displayed properly. Here is the code:

$select_stats = "SELECT 
  c.ResponderID
  , COUNT(MsgID)
  , a.SubscriberID
  , CanReceiveHTML
  , EmailAddress 
FROM InfResp_msglogs AS a
  , InfResp_subscribers AS b
  , InfResp_responders AS c 
WHERE b.CanReceiveHTML = 1 
  AND c.owner='".$_SESSION['logged_user_id']."' 
  AND c.ResponderID = b.ResponderID 
  AND b.SubscriberID = a.SubscriberID 
GROUP BY c.ResponderID";

//echo $select_result;

while($row = mysql_fetch_array($select_result))
{
    $messages = $row['COUNT(MsgID)'];
    $campaign = $row['ResponderID'];
    $subscriber = $row['SubscriberID'];
    $subscriber_email = $row['EmailAddress'];

    echo "<br>Campaign = ". $campaign ."
          <br>Sent emails count = ". $messages ."
          <br>Subscriber Nr. = ". $subscriber ."
          <br>Subscriber Email = ". $subscriber_email ."<br>";
     }  

This is the output:

Campaign = 10
Sent emails count = 109
Su开发者_如何学运维bscriber Nr. = 95
Subscriber Email = timothy@gmail.com

Campaign = 11
Sent emails count = 16
Subscriber Nr. = 97
Subscriber Email = alan@yahoo.com

As there are three different subscribers under campaign no.10 and one under campaign no.11 in the db (each with its own email addresses of course), I would like the output to be:

Campaign = 10
Sent emails count = 109
Subscriber Nr. = 92
94
95
Subscriber Email = timothy@gmail.com
anthony@yahoo.com
josie@hotmail.com

Campaign = 11
Sent emails count = 16
Subscriber Nr. = 97
Subscriber Email = alan@yahoo.com

Thanks.


You need group_concat for this:

... SELECT c.ResponderID, COUNT(MsgID), a.SubscriberID, CanReceiveHTML, 
group_concat(EmailAddress ORDER BY EmailAddress DESC SEPARATOR ' ') ... 
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜