...

View Full Version : Resolved Group by and display



grumpy
04-12-2011, 01:06 PM
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
Subscriber 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.

grumpy
04-12-2011, 04:19 PM
Had to figure it out myself. The code is:


$len = 8192;
$query = "SET group_concat_max_len=$len";
$resulty = mysql_query($query);
if (!$resulty)
{
die("error! $query<br>".mysql_error());
}
$select_stats = "SELECT c.ResponderID, COUNT(MsgID), CanReceiveHTML, GROUP_CONCAT(a.SubscriberID) as subIDs, GROUP_CONCAT(EmailAddress) as Eaddrs 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";

$select_result = mysql_query($select_stats) or die("Invalid query: " . mysql_error());
//echo $select_result;

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

$subscriber = explode(',', $row['subIDs']);
$subscriber = array_unique($subscriber);
$subscriber = implode(', ', $subscriber);

$subscriber_email = explode(',', $row['Eaddrs']);
$subscriber_email = array_unique($subscriber_email);
$subscriber_email = implode(', ', $subscriber_email);

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

}

Will be asking questions on stackoverflow.com from now.

Time's up!

Old Pedant
04-12-2011, 07:38 PM
He asks the question at 5AM (well, 5AM my time...but that's 8AM even East Coast time) and is upset he doesn't get an answer within three hours.

Maybe stackoverflow.com is populated mostly by European posters whose time zone matches his more.

Sheesh.

Anyway, the query has a major bug in it and he's making PHP do more work than it needs to, so maybe stackoverflow.com doesn't always have the best answers.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum